Get Database Backup And Restore The Backup File In SQL Server

In this article, we are going to learn how to get backup from SQL Server and how to restore the backup files in SQL Server.

First, we will create the sample SQL database with the sample table.

Here, I have created the database as SqlBackUpTest and created sample table named as Log.

Get SQL database backup

Step 1

Once the database has been created, we will take the backup.

To take SQL database as a backup file, select your database in SQL Server. Right click and select Tasks -> select BackUp.

Step 2

Now, you can see the dialog Window, once you click Backup option.

In the dialog Window, the default destination path has been selected. It will create the backup file in your SQL installed directory with the name of your database ex : "SqlBackUpTest.bak".

Note

The backup file with be created with the extension of .bak.

If you want to create the backup file in any other directory, click Remove button in the dialog Window and click Add button.

Step 4

Now, you can see another new dialog Window, which will have an option to select our custom directory to create the backup file.

Step 5

Select your Custom directory and enter the backup file name in File Name option. Please create your file name same as your database name. In future, you don't need to remember your DB name and it is a better way.

Note

While entering the file name, it should have to mention your extension name also. ex "SqlBackUpTest.bak".

Step 6

Once you are done with all the steps, finally click OK. Now, you can see the message box to display backup of your database filename completed successfully.

Step 7

Now, you can see the created backup file in the selected directory. Here, I have selected my backup file directory as F:\\.

Restore the created backup file

Step 1

Before restoring the database, we can delete the existing database or we can overwrite the existing database. Here, I am going to remove the database and restore it as a new database.

To restore the database, select Databases -> Right click and select Restore Database.

Step 2

Now, you are able to see the new dialog Window to restore your backup file.

Note

In "To Database" field, kindly mention your database name correctly. It is very important.

Step 3

Once you filled the database name, select the database backup file from the database or from the device. Here, we have a file on our device. Kindly select from the device option. Now, you can see the one new dialog Window, which will have an option to add your backup file from device to SQL Server.

Step 4

Once you click Add, a new dialog Window will appear and will ask you to select your directory and the backup file.

Once you are done with all the steps given above, now you can see, you added backup file in a box and with check box option. Here, you have to check the selected backup file.

Finally, click OK. Your database will be restored successfully..

Points to keep in Mind while deploying and restoring database

The file name should have an extension with .bak while assigning the name.
Create Backup file name as database file name and it willl be easy to understand in future.
While creating back up file, mention the current date with the time because in a single day, you can may create more than 2 backup files for same db. Ex "Test_24 april 9 :30PM.bak"
While restoring the database, you can delete the existing database or overwrite the exsiting database.
While restoring the database, kindly mention the database name correctly, else you will get an error.

Thanks for reading my article. Feel free to add your comments, if you have any queries or suggesstions.