In SQL Server, you can take different types of database backups. Below, we will explain 4 common SQL database backups’ types. <\/p>
1. Full Backup<\/strong> <\/h3>It is a complete copy of your SQL database. It stores all the data and objects, including tables, functions, procedures, views, indexes, etc. A full backup allows you to easily restore the database in exactly the same form as it was at the time creating the backup. It serves as a foundation of all other types of backups. However, creating a full backup of large databases requires considerable storage space and time. Now, let’s see how to take a full backup of SQL Server database.<\/p>
You can create a full back up using the SQL Server Management Studio (SSMS). Here are the steps:<\/strong><\/p>\n- Open SSMS. In the Object Explorer, right-click the database, and select Tasks > Back Up<\/strong>.<\/li>\n<\/ul>
![\"click](\"https:\/\/www.stellarinfo.com\/blog\/wp-content\/uploads\/2024\/08\/click-task-and-select-backup.jpg\")
<\/figure>\n- In the Back-Up Database<\/strong> window, select the Backup<\/strong> type <\/strong>as Full<\/strong>.<\/li>\n<\/ul>
![\"click](\"https:\/\/www.stellarinfo.com\/blog\/wp-content\/uploads\/2024\/08\/click-backup-type-as-full-in-backup-database-window.jpg\")
<\/figure>\n- Select Database <\/strong>under Backup <\/strong>component, choose a destination to store the backup, and click Add.<\/strong><\/li>\n\n\n\n
- Click OK. <\/strong>Wait till the backup is completed.<\/li>\n\n\n\n
- Then, click OK<\/strong> to close the dialog box.<\/li>\n<\/ul>
Alternatively, you can also create a full backup using the T-SQL command. Here’s the syntax:<\/strong><\/p>BACKUP DATABASE [AdventureWorks2019] TO DISK = N’C:\\backups\\AdventureWorks2019.bak’ WITH NOFORMAT, NOINIT, NAME = N’AdventureWorks2019-Full Database Backup’, SKIP, NOREWIND, NOUNLOAD, STATS = 10<\/code><\/pre>GO<\/code><\/pre>2. Differential Backup<\/strong><\/h3>Differential Backup stores only the modifications since the last full backup was created. This helps save storage space and time. It requires less information because it stores only the changes after the last full database backup. However, you need to restore the full backup before restoring the differential backup.<\/p>
Here are the steps to create differential backup using the SSMS:<\/p>
Note:<\/strong> Make sure you have a full backup of the database before proceeding.<\/p>\n- Open SSMS. In Object Explorer, right-click the database and select Tasks > Back Up<\/strong>.<\/li>\n<\/ul>
![\"click](\"https:\/\/www.stellarinfo.com\/blog\/wp-content\/uploads\/2024\/08\/click-tasks-and-then-click-backup-to-select-filegroups.jpg\")
<\/figure>\n- In the Backup type <\/strong>dropdown, select Differential backup<\/strong>.<\/li>\n<\/ul>
![\"click](\"https:\/\/www.stellarinfo.com\/blog\/wp-content\/uploads\/2024\/08\/click-backup-type-drop-down-to-select-differential-backup.jpg\")
<\/figure>\n- Select the Database<\/strong> option under Database component, select a destination to store the backup, click Add<\/strong> and then click OK.<\/strong><\/li>\n<\/ul>
Alternatively, you can use the T-SQL commands to create a differential backup. Here’s the syntax:<\/strong><\/p>BACKUP DATABASE [AdventureWorks2019] TO DISK = N’C:\\backups\\AdventureWorks2019.bak’ WITH DIFFERENTIAL, NOFORMAT, NOINIT, NAME = N’AdventureWorks2019-Full Database Backup’, SKIP, NOREWIND, NOUNLOAD, STATS = 10<\/code><\/pre>GO<\/code><\/pre>3. Transaction Log Backup<\/strong><\/h3>A Transaction Log Backup is a type of incremental backup that stores all the transactions logs. It contains only the changes made to the database since the last transaction log backup. This backup helps in restoring the database to a particular point in time and minimizing the data loss. To create the transaction log backup<\/a>, your backup type should be full or bulk-logged recovery.<\/p>To create the Transaction Log Backup, you can use the following command.<\/strong><\/p> BACKUP LOG database-name<\/code><\/pre>To Disk = ‘E:\\BackupDrive\\database_name_log.TRN’<\/code><\/pre>4. Files and <\/strong>Filegroups Backup<\/strong><\/h3>Files and Filegroups backup stores a copy of selected files or filegroups within the database. This backup type is suitable if you only need to back up a specific part of the data regularly. This helps save significant storage space and time.<\/p>
Here are steps to create a files or filegroups backup using SSMS:<\/strong><\/p>\n- In SSMS, go to Object Explorer, right-click the database, and select Tasks > Back Up.<\/strong><\/li>\n<\/ul>
![\"click](\"https:\/\/www.stellarinfo.com\/blog\/wp-content\/uploads\/2024\/08\/click-database-tasks-and-then-backup.jpg\")
<\/figure>\n- Select the Files and filegroups option.<\/li>\n<\/ul>
![\"click](\"https:\/\/www.stellarinfo.com\/blog\/wp-content\/uploads\/2024\/08\/click-file-and-filegroups-click-ok.jpg\")
<\/figure>\n- Select the files and filegroups that you want to back up, select the database name, specify the backup set, and choose a backup destination.<\/li>\n<\/ul>
![\"specify](\"https:\/\/www.stellarinfo.com\/blog\/wp-content\/uploads\/2024\/08\/specify-the-backup-and-then-click-ok.jpg\")
<\/figure>\n- Click OK.<\/strong><\/li>\n<\/ul>
Alternatively, you can use the following T-SQL command to create files or filegroups backup.<\/p>
BACKUP DATABASE [Northwind] FILEGROUP = N’PRIMARY’ TO DISK = N’C:\\Program Files\\Microsoft SQL Server\\MSSQL15.MSSQLSERVER\\MSSQL\\Backup\\Northwind.bak’ WITH NOFORMAT, NOINIT, NAME = N’Northwind-Full Database Backup’, SKIP, NOREWIND, NOUNLOAD, STATS = 10<\/code><\/pre>GO<\/code><\/pre>How to Restore SQL Server Database Backup?<\/h2>
If you have successfully taken the backup of your SQL Server database, you can easily restore the backup as and when the situation arises. Let’s see how to restore a full backup of SQL Server database using the T-SQL commands:<\/p>
\n- Open SSMS and connect to your SQL Server instance.<\/li>\n\n\n\n
- Click the New Query<\/strong> option.<\/li>\n\n\n\n
- In the Query Editor<\/strong> window, run the following command to restore the entire database from the .bak file:<\/li>\n<\/ul>
USE[master];<\/code><\/pre>GO<\/code><\/pre>BACKUP DATABASE [test1]<\/code><\/pre>TO DISK = N'C:\\Program Files\\Microsoft SQL Server\\MSSQL14.MSSQLSERVER\\MSSQL\\Backup\\test1.bak'<\/code><\/pre>WITH NOFORMAT, NOINIT,<\/code><\/pre>NAME = N'test1-Full Database Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10;<\/code><\/pre>GO<\/code><\/pre>Alternately, you can restore the SQL Server database backup<\/a> using the SSMS or Windows PowerShell.<\/strong><\/p>Common Issues and Errors while Restoring SQL Database Backup<\/strong><\/h2>Sometimes, while restoring the SQL Server database backup (.BAK) file, you encounter various issues and errors, preventing you from restoring the backup. Let’s take a look at some common error messages that you may encounter while restoring the SQL database backup:<\/p>
Error 3183: Corrupt SQL Backup File<\/h3>
This error indicates corruption or integrity issues in the backup (.BAK) file. You can try to restore the database using the CONTINUE_AFTER_ERROR option. This will ignore the error and restore the backup. If the backup file is corrupted, you can repair the backup file to resolve the error 3183.<\/a><\/p>SQL Database Restore Failed, Database in Use<\/strong><\/h3>This error indicates that the backup file you want restore is already in use or has some issues. You can check and disconnect all existing connections and restart the services. If the backup file is corrupted, you can repair it to resolve the SQL database restore failed error<\/a>.<\/p>Database cannot be opened: It is in the middle of a restore<\/strong><\/h3>This error indicates that the database is already in restore mode. So, wait until the database is restored. Corruption in the backup file can also cause this error. You can repair the backup file to resolve the Database cannot be opened: It is in the middle of a restore<\/a> error.<\/p>SQL Database Restore Error 5243<\/strong><\/h3>This error occurs due to inconsistencies in the backup file or lack of space on the disk storing the database file. You can check the storage space on the disk or repair the backup (.bak) file to resolve the error 5243.<\/a><\/p>The database cannot be recovered because the log was not restored<\/strong><\/h3>This error occurs when your SQL Server database is stuck in the RESTORING state. This might happen due to corruption in the SQL database or the backup file. You can repair the database or backup file to resolve the error ‘The database cannot be recovered because the log was not restored<\/a>.’<\/p>SQL Database Error 3241: Restore Headeronly<\/strong><\/h3>This error can occur if you try to restore the backup file, created in a higher version of SQL Server, in an earlier version of SQL Server or corruption in backup file. You can check the version of backup file to resolve the SQL database error 3241: Restore Headeronly<\/a> error.<\/p>SQL Backup Restore Error 3013<\/strong><\/h3>You can encounter this error due to issues with backup file, file permission issues, and other reasons. You can check the file permissions or repair the backup file to resolve the SQL Error 3013.<\/a><\/p>Conclusion<\/strong><\/h2>In this guide, we have discussed how to create different types of database backups in SQL Server. To save time and efforts, you can use the Maintenance Plan Wizard and SQL Server Agent Job to create automated backups. The wizard allows you to set a schedule to automatically backup the SQL database<\/a>.<\/p>We have also discussed the steps to restore the backups. However, sometimes, when restoring the backup, you encounter errors that can prevent you from restoring the backup. Such errors usually occur due to inconsistencies or corruption in the backup files. If you face errors while restoring the SQL backup, run the ‘RESTORE VERIFYONLY<\/strong>’ command to identify if the SQL backup file is corrupt.<\/a> If the backup file is corrupted, you can use an advanced SQL repair software,<\/a> like Stellar Repair for MS SQL Technician. The software can easily repair corrupt SQL Server database backup (.BAK) files and restore all the data. It can help restore the data from full, differential, and transaction log backups. It can even recover deleted records from the damaged .BAK file. The tool supports both Linux and Windows systems. It is compatible with SQL Server 2022, 2019, and lower versions.<\/p>\n","protected":false},"excerpt":{"rendered":"
- \n
- Open SSMS. In the Object Explorer, right-click the database, and select Tasks > Back Up<\/strong>.<\/li>\n<\/ul><\/figure>
- \n
- In the Back-Up Database<\/strong> window, select the Backup<\/strong> type <\/strong>as Full<\/strong>.<\/li>\n<\/ul><\/figure>
- \n
- Select Database <\/strong>under Backup <\/strong>component, choose a destination to store the backup, and click Add.<\/strong><\/li>\n\n\n\n
- Click OK. <\/strong>Wait till the backup is completed.<\/li>\n\n\n\n
- Then, click OK<\/strong> to close the dialog box.<\/li>\n<\/ul>
Alternatively, you can also create a full backup using the T-SQL command. Here’s the syntax:<\/strong><\/p>
BACKUP DATABASE [AdventureWorks2019] TO DISK = N’C:\\backups\\AdventureWorks2019.bak’ WITH NOFORMAT, NOINIT, NAME = N’AdventureWorks2019-Full Database Backup’, SKIP, NOREWIND, NOUNLOAD, STATS = 10<\/code><\/pre>GO<\/code><\/pre>2. Differential Backup<\/strong><\/h3>
Differential Backup stores only the modifications since the last full backup was created. This helps save storage space and time. It requires less information because it stores only the changes after the last full database backup. However, you need to restore the full backup before restoring the differential backup.<\/p>
Here are the steps to create differential backup using the SSMS:<\/p>
Note:<\/strong> Make sure you have a full backup of the database before proceeding.<\/p>
- \n
- Open SSMS. In Object Explorer, right-click the database and select Tasks > Back Up<\/strong>.<\/li>\n<\/ul><\/figure>
- \n
- In the Backup type <\/strong>dropdown, select Differential backup<\/strong>.<\/li>\n<\/ul><\/figure>
- \n
- Select the Database<\/strong> option under Database component, select a destination to store the backup, click Add<\/strong> and then click OK.<\/strong><\/li>\n<\/ul>
Alternatively, you can use the T-SQL commands to create a differential backup. Here’s the syntax:<\/strong><\/p>
BACKUP DATABASE [AdventureWorks2019] TO DISK = N’C:\\backups\\AdventureWorks2019.bak’ WITH DIFFERENTIAL, NOFORMAT, NOINIT, NAME = N’AdventureWorks2019-Full Database Backup’, SKIP, NOREWIND, NOUNLOAD, STATS = 10<\/code><\/pre>GO<\/code><\/pre>3. Transaction Log Backup<\/strong><\/h3>
A Transaction Log Backup is a type of incremental backup that stores all the transactions logs. It contains only the changes made to the database since the last transaction log backup. This backup helps in restoring the database to a particular point in time and minimizing the data loss. To create the transaction log backup<\/a>, your backup type should be full or bulk-logged recovery.<\/p>
To create the Transaction Log Backup, you can use the following command.<\/strong><\/p>
BACKUP LOG database-name<\/code><\/pre>To Disk = ‘E:\\BackupDrive\\database_name_log.TRN’<\/code><\/pre>4. Files and <\/strong>Filegroups Backup<\/strong><\/h3>
Files and Filegroups backup stores a copy of selected files or filegroups within the database. This backup type is suitable if you only need to back up a specific part of the data regularly. This helps save significant storage space and time.<\/p>
Here are steps to create a files or filegroups backup using SSMS:<\/strong><\/p>
- \n
- In SSMS, go to Object Explorer, right-click the database, and select Tasks > Back Up.<\/strong><\/li>\n<\/ul><\/figure>
- \n
- Select the Files and filegroups option.<\/li>\n<\/ul><\/figure>
- \n
- Select the files and filegroups that you want to back up, select the database name, specify the backup set, and choose a backup destination.<\/li>\n<\/ul><\/figure>
- \n
- Click OK.<\/strong><\/li>\n<\/ul>
Alternatively, you can use the following T-SQL command to create files or filegroups backup.<\/p>
BACKUP DATABASE [Northwind] FILEGROUP = N’PRIMARY’ TO DISK = N’C:\\Program Files\\Microsoft SQL Server\\MSSQL15.MSSQLSERVER\\MSSQL\\Backup\\Northwind.bak’ WITH NOFORMAT, NOINIT, NAME = N’Northwind-Full Database Backup’, SKIP, NOREWIND, NOUNLOAD, STATS = 10<\/code><\/pre>GO<\/code><\/pre>How to Restore SQL Server Database Backup?<\/h2>
If you have successfully taken the backup of your SQL Server database, you can easily restore the backup as and when the situation arises. Let’s see how to restore a full backup of SQL Server database using the T-SQL commands:<\/p>
- \n
- Open SSMS and connect to your SQL Server instance.<\/li>\n\n\n\n
- Click the New Query<\/strong> option.<\/li>\n\n\n\n
- In the Query Editor<\/strong> window, run the following command to restore the entire database from the .bak file:<\/li>\n<\/ul>
USE[master];<\/code><\/pre>GO<\/code><\/pre>BACKUP DATABASE [test1]<\/code><\/pre>TO DISK = N'C:\\Program Files\\Microsoft SQL Server\\MSSQL14.MSSQLSERVER\\MSSQL\\Backup\\test1.bak'<\/code><\/pre>WITH NOFORMAT, NOINIT,<\/code><\/pre>NAME = N'test1-Full Database Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10;<\/code><\/pre>GO<\/code><\/pre>Alternately, you can restore the SQL Server database backup<\/a> using the SSMS or Windows PowerShell.<\/strong><\/p>
Common Issues and Errors while Restoring SQL Database Backup<\/strong><\/h2>
Sometimes, while restoring the SQL Server database backup (.BAK) file, you encounter various issues and errors, preventing you from restoring the backup. Let’s take a look at some common error messages that you may encounter while restoring the SQL database backup:<\/p>
Error 3183: Corrupt SQL Backup File<\/h3>
This error indicates corruption or integrity issues in the backup (.BAK) file. You can try to restore the database using the CONTINUE_AFTER_ERROR option. This will ignore the error and restore the backup. If the backup file is corrupted, you can repair the backup file to resolve the error 3183.<\/a><\/p>
SQL Database Restore Failed, Database in Use<\/strong><\/h3>
This error indicates that the backup file you want restore is already in use or has some issues. You can check and disconnect all existing connections and restart the services. If the backup file is corrupted, you can repair it to resolve the SQL database restore failed error<\/a>.<\/p>
Database cannot be opened: It is in the middle of a restore<\/strong><\/h3>
This error indicates that the database is already in restore mode. So, wait until the database is restored. Corruption in the backup file can also cause this error. You can repair the backup file to resolve the Database cannot be opened: It is in the middle of a restore<\/a> error.<\/p>
SQL Database Restore Error 5243<\/strong><\/h3>
This error occurs due to inconsistencies in the backup file or lack of space on the disk storing the database file. You can check the storage space on the disk or repair the backup (.bak) file to resolve the error 5243.<\/a><\/p>
The database cannot be recovered because the log was not restored<\/strong><\/h3>
This error occurs when your SQL Server database is stuck in the RESTORING state. This might happen due to corruption in the SQL database or the backup file. You can repair the database or backup file to resolve the error ‘The database cannot be recovered because the log was not restored<\/a>.’<\/p>
SQL Database Error 3241: Restore Headeronly<\/strong><\/h3>
This error can occur if you try to restore the backup file, created in a higher version of SQL Server, in an earlier version of SQL Server or corruption in backup file. You can check the version of backup file to resolve the SQL database error 3241: Restore Headeronly<\/a> error.<\/p>
SQL Backup Restore Error 3013<\/strong><\/h3>
You can encounter this error due to issues with backup file, file permission issues, and other reasons. You can check the file permissions or repair the backup file to resolve the SQL Error 3013.<\/a><\/p>
Conclusion<\/strong><\/h2>
In this guide, we have discussed how to create different types of database backups in SQL Server. To save time and efforts, you can use the Maintenance Plan Wizard and SQL Server Agent Job to create automated backups. The wizard allows you to set a schedule to automatically backup the SQL database<\/a>.<\/p>
We have also discussed the steps to restore the backups. However, sometimes, when restoring the backup, you encounter errors that can prevent you from restoring the backup. Such errors usually occur due to inconsistencies or corruption in the backup files. If you face errors while restoring the SQL backup, run the ‘RESTORE VERIFYONLY<\/strong>’ command to identify if the SQL backup file is corrupt.<\/a> If the backup file is corrupted, you can use an advanced SQL repair software,<\/a> like Stellar Repair for MS SQL Technician. The software can easily repair corrupt SQL Server database backup (.BAK) files and restore all the data. It can help restore the data from full, differential, and transaction log backups. It can even recover deleted records from the damaged .BAK file. The tool supports both Linux and Windows systems. It is compatible with SQL Server 2022, 2019, and lower versions.<\/p>\n","protected":false},"excerpt":{"rendered":"
- In the Query Editor<\/strong> window, run the following command to restore the entire database from the .bak file:<\/li>\n<\/ul>
- Click OK.<\/strong><\/li>\n<\/ul>
- Select the files and filegroups that you want to back up, select the database name, specify the backup set, and choose a backup destination.<\/li>\n<\/ul>
- Select the Files and filegroups option.<\/li>\n<\/ul>
- In SSMS, go to Object Explorer, right-click the database, and select Tasks > Back Up.<\/strong><\/li>\n<\/ul>
- Select the Database<\/strong> option under Database component, select a destination to store the backup, click Add<\/strong> and then click OK.<\/strong><\/li>\n<\/ul>
- In the Backup type <\/strong>dropdown, select Differential backup<\/strong>.<\/li>\n<\/ul>
- Click OK. <\/strong>Wait till the backup is completed.<\/li>\n\n\n\n
- Select Database <\/strong>under Backup <\/strong>component, choose a destination to store the backup, and click Add.<\/strong><\/li>\n\n\n\n
- In the Back-Up Database<\/strong> window, select the Backup<\/strong> type <\/strong>as Full<\/strong>.<\/li>\n<\/ul>