SQL Server Database Backup and Restore

In this article we will learn how to take a full backup of the SQL Server Database and how to restore a backup of a database.

There are two methods to take a backup:

1. Using SQL Server Management Studio (wizard)
2. Using TSQL scripts 

Step 1:  

Open your SQL Server Object Explorer; right click on the Database name you want to take a backup; go to Tasks -> Backup as shown in below screen.

 

 

 

Step 2:

In Back Up Database Dialog box, Select the database name. In Next step select the Full Backup as Backup type. Also there is an option to copy only, if you want to copy only then tick that box. 

 

 

Step 3: 

Now click on Add and select the file or backup device for the backup destination as shown in the below Screen and click OK.

 

 

 

 

Step 4:

Go to Option Tab and Select Overwrite Media Option as "Back up to the existing  media set or overwrite to the existing backup set as per your requirement."

In the Reliability section we can select the checkbox "Verify backup when finished" so we can make sure that the database backup is Ok.

 

 

 

Step 5:

Once you complete the above step, click on Ok. Once completed it will display a success message as per the below screen.

 

 

 

Now you can go and check the destination path, there is a 'databasename.bak' file (.bak extension).

 

If you click on Script  "Script Action to New Query Window" than it will generate Script as follows.

 

  

BACKUP DATABASE [EMP] TO  DISK = N'C:\Ankur\Emp.bak'
WITH NOFORMAT, NOINIT,  NAME = N'EMP-Full Database Backup', SKIP, NOREWIND, NOUNLOAD,  STATS = 10
GO

Now, we are going to Restore a Full Database Backup Using SQL Server Management Studio. For that follow the steps. 

 

Step 1:

Select the Database and Right click, Go to Tasks -> Restore -> Database.

(You can also restore it from file or file groups) .

 

 

 

Step 2:

In Next Dialog, Select the Source, here we want to store from Device so choose Device.

 

 

 

Step 3: Select Backup Device, Click on Add button.

 

 

 

Step 4: Select the .bak file you want to Restore, here I am selecting Emp.bak file.

 

 

 

Step 5: From the next Dialog, make sure the Restore is ticked.

 

 

 

Step 6: Go to Option Tab and select Overwrite the existing database (WITH REPLACE) .

 

 

 

Step 7:

Finally, Click on Ok and on Success it will display a success message dialog as per below screen.

Now check your database for the new data. 

 

 

 

Hope you like the steps to backup and restore the SQL Database.