Database | Technology

this time I will show how to backup database, first I have one database like this :

I want to backup database test, just right click on database test choose Task >> Backup.

you can choose another Destination folder by Clicking Remove and Add new Destination folder, the highlight text is destination folder of backup file.

Note : if you add another destination backup folder, don’t forget to put .bak at the end of filename like this :

after that Klik OK :

wait a few second and you will see information message like this :

RESTORE DATABASE

now I will delete database tes and try to restore it using backup file, first delete database tes and then create new empty database tes.

as you can see, the database is empty. just right click on database tes and then choose Tasks > Restore > Database :

First Choose radio button From device, and point it to your backup destination folder, after that check Restore, and then choose Options properties.

then check Overwrite the existing database (WITH REPLACE), after that click OK

you will see this information message :

USE GENERATE SCRIPTS TO BACKUP DATABASE

this is the second alternative, you can use Generate script to backup database. I will Script database tes, just right click on database then choose Tasks > Generate Scripts, like this :

Klik Next :

You can Script entire database and all the objects or Select specific database object, in this case I choose Script entire database and all database object, then click Next :

You can choose the destination folder of Scripts by changing the File Name, after that click Advanced :

in this section, you can change Type of data to script, and I change it to Schema and data. after that click OK, then click Next until Finish

RESTORE DATABASE USING SCRIPTS

if you want to restore database using Scripts, just open Script in SQL Server and then execute it by pressing F5

success…

this time i will explain how to create store procedure in SQL Server. i used Microsoft SQL Server 2008 R2 as database. first i have created one database and table like this :

and this is design of table dbo.tb_profile :

now lets create store procedure to insert data into table dbo.tb_profile, right click folde Stored Procedures and choose New Stored Procedure :

after that i write and execute this query:

USE [db_test]
GO
/****** Object:  StoredProcedure [dbo].[sp_InsertData]    Script Date: 02/11/2013 16:28:30 ******/
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER ON
GO
  
CREATE PROCEDURE [dbo].[sp_InsertData]
  
    /*Parameter*/
    @id varchar(10),
    @name varchar(25),
    @contact varchar(20)
    
AS
BEGIN
    /*Insert data into Table*/
    insert into tb_profile (id, name, contact)
    values (@id, @name, @contact)
END

after that change CREATE into ALTER like this :

USE [db_test]
GO
/****** Object:  StoredProcedure [dbo].[sp_InsertData]    Script Date: 02/11/2013 16:28:30 ******/
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER ON
GO
  
ALTER PROCEDURE [dbo].[sp_InsertData]
  
    /*Parameter*/
    @id varchar(10),
    @name varchar(25),
    @contact varchar(20)
    
AS
BEGIN
    /*Insert data into Table*/
    insert into tb_profile (id, name, contact)
    values (@id, @name, @contact)
END

now lets try our store procedure, open New Query :

then i write and execute this Query :

   1: sp_InsertData 'zxc','OKA','080989999'

the result is :

success…

Technology

Category Database

Backup and Restore Database in SQL Server

Create Store Procedure Microsoft SQL Server