Get Database Backup And Restore The Backup File In SQL Server

In this article, we are going to learn how to get back up 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 a sample table named Log.

 

Get SQL database backup

 

Step 1

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

 

To take the 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 the 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 the 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 the File Name option. Please create your file name the same as your database name. In the 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 mentioned 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 the 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 to 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 the "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 the 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 the backup file in a box and with a 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 a database

1. The file name should have an extension with .bak while assigning the name.
2. Create a Backup file name as a database file name and it will be easy to understand in the future.
3. While creating a backup file, mention the current date with the time because in a single day, you can may create more than 2 backup files for the same DB. Ex  "Test_24 April 9:30PM.bak"
4. While restoring the database, you can delete the existing database or overwrite the existing database.
5. 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 suggestions.