Last Updated on April 15, 2024 by Jawad Ali
In this article, we will show you the Step by Step Guide to Backup and Restore Files in SQL Server using the SQL Server Management Studio with examples. As soon as you click on the OK button, it will open the Backup Database dialog box showing you the progress of the backup operation as shown below.
You can also see your backup file information in the Report window by clicking on the Backup sets tab as shown below.
SQL Server stores the data in files just like any other database management system. The backup of these files is very important as it helps to protect the data from accidental or intentional data loss. You can take a full backup of all the files or individual files based on your requirement.
You can also see your backup file information in the Report window by clicking on the Backup sets tab as shown below.
As soon as you click on the OK button, it will open the Backup Database dialog box showing you the progress of the backup operation as shown below.
Once the backup is completed, click on the view report button to see more information about your backup as shown in the below image. If you want to take a transaction log backup of your database, then right-click on the database and choose the Tasks >> Back Up… option as we did for taking a full backup.
As soon as you click on the OK button, it will open the Backup Database dialog box showing you the progress of the backup operation as shown below.
The most common type of file backups in SQL Server is:
1) Full Database Backup
2) Differential Database Backup
3) Transaction Log Backup
4) File or Filegroup Backup
Let us see how to take a backup of each type of file one by one in SQL Server Management Studio with examples.
1. Open SSMS, connect to the SQL Server instance, and expand the databases node as shown below:
2. Right-click on the database that you want to take a backup and choose the Tasks >> Back Up… option as shown in the below image:
3. On the Backup Database dialog box, specify the following options
a) Backup Type – Select Full from the drop-down list
b) Backup Component – Select Database, if you want to take a transaction log backup of your database, then right-click on the database and choose the Tasks >> Back Up… option as we did for taking a full backup.
c) Destination – Click on the … button to open the Backup Destination dialog box and select Hard Disk drive or Tape as your backup destination
d) Backup set will expire – Select the check box if you want your backup to expire after a certain number of days
e) Copy-only backup – Select the check box if you only want to take a copy-only backup. You can also see your backup file information in the Report window by clicking on the Backup sets tab as shown below.
4. Click on the OK button to start taking database backup as shown in the below image:
5. As soon as you click on the OK button, it will open the Backup Database dialog box showing you the progress of the backup operation as shown below:
6. Once the backup is completed, click on the view report button to see more information about your backup as shown in the below image:
7. The Report window shows you all information related to your backup including backup, type of backup, start time, finish time, etc. as shown in the below image:
8. You can also see your backup file information in the Report window by clicking on the Backup sets tab as shown below:
9. Click on the Close button to close the Backup Database dialog box and Report window.
10. Now, you can check your backup file path and open it to verify that your database is backed up successfully or not as shown in the below image:
11. If you want to take a differential backup of your database, then right-click on the database and choose the Tasks >> Back Up… option as we did for taking a full backup.
12. On the Backup Database dialog box, select Differential from the Backup Type drop-down list as shown in the below image:
13. Click on the OK button to start taking differential backup of your database as shown in the below image:
14. As soon as you click on the OK button, it will open the Backup Database dialog box showing you the progress of the backup operation as shown below:
15. Once the backup is completed, click on the view report button to see more information about your backup as shown in the below image:
16. The Report window shows you all information related to your backup including backup, type of backup, start time, finish time, etc. as shown in the below image:
17. You can also see your backup file information in the Report window by clicking on the Backup sets tab as shown below:
18. Click on the Close button to close the Backup Database dialog box and Report window. You can also see your backup file information in the Report window by clicking on the Backup sets tab as shown below. You can also see your backup file information in the Report window by clicking on the Backup sets tab as shown below.
19. Now, you can check your backup file path and open it to verify that your database is backed up successfully or not as shown in the below image: You can also see your backup file information in the Report window by clicking on the Backup sets tab as shown below.
20. If you want to take a transaction log backup of your database, then right-click on the database and choose the Tasks >> Back Up… option as we did for taking a full backup. You can also see your backup file information in the Report window by clicking on the Backup sets tab as shown below.
Conclusion:
Taking a backup of your SQL Server databases is a very important task that you should perform regularly to protect your data. In this article, we have seen how to take a full backup, differential backup, and transaction log backup of a SQL Server database using SQL Server Management Studio (SSMS). You can also see your backup file information in the Report window by clicking on the Backup sets tab as shown below.
