How to Backup and Restore SQL Server Database? – A Comprehensive Guide

Monika Dadool | Updated on August 30, 2024 SQL Database Repair

Summary: It is important to create backup of SQL Server database to prevent data loss in case of any untoward incident. In this guide, we will explain how to create different types of backups in SQL Server and how to restore these SQL database backups. We will also mention an advanced SQL repair tool that can come in handy in case the backup file gets corrupt or damaged.

One of the major responsibilities of SQL Server Database Administrators (DBAs) is to ensure continuous availability of data and prevent data loss in case of any issue or disaster. To ensure this, it is important to take regular backups of databases and store the backup copies at a safe, off-site location. A healthy, valid backup prevents potential data loss and helps in recovery of data within minimal downtime.

Backups can help recover the data in case of hardware failure or issues, server failure, human errors (such as mistakenly dropping a table), natural disasters, database corruption, etc. Additionally, backups are useful for migrating databases from one server to another.

In this detailed guide, we’ll explain the process of taking different types of database backups in SQL Server and also see how to restore the SQL Server database backups.

How to Take Different Types of SQL Server Database Backups?

In SQL Server, you can take different types of database backups. Below, we will explain 4 common SQL database backups’ types.

1. Full Backup

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.

You can create a full back up using the SQL Server Management Studio (SSMS). Here are the steps:

Open SSMS. In the Object Explorer, right-click the database, and select Tasks > Back Up.

In the Back-Up Database window, select the Backup type as Full.

Select Database under Backup component, choose a destination to store the backup, and click Add.
Click OK. Wait till the backup is completed.
Then, click OK to close the dialog box.

Alternatively, you can also create a full backup using the T-SQL command. Here’s the syntax:

BACKUP DATABASE [AdventureWorks2019] TO DISK = N’C:\backups\AdventureWorks2019.bak’ WITH NOFORMAT, NOINIT, NAME = N’AdventureWorks2019-Full Database Backup’, SKIP, NOREWIND, NOUNLOAD, STATS = 10

GO

2. Differential Backup

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.

Here are the steps to create differential backup using the SSMS:

Note: Make sure you have a full backup of the database before proceeding.

Open SSMS. In Object Explorer, right-click the database and select Tasks > Back Up.

In the Backup type dropdown, select Differential backup.

Select the Database option under Database component, select a destination to store the backup, click Add and then click OK.

Alternatively, you can use the T-SQL commands to create a differential backup. Here’s the syntax:

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

GO

3. Transaction Log Backup

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, your backup type should be full or bulk-logged recovery.

To create the Transaction Log Backup, you can use the following command.

 BACKUP LOG database-name

To Disk = ‘E:\BackupDrive\database_name_log.TRN’

4. Files and Filegroups Backup

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.

Here are steps to create a files or filegroups backup using SSMS:

In SSMS, go to Object Explorer, right-click the database, and select Tasks > Back Up.

Select the Files and filegroups option.

Select the files and filegroups that you want to back up, select the database name, specify the backup set, and choose a backup destination.

Click OK.

Alternatively, you can use the following T-SQL command to create files or filegroups backup.

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

GO

How to Restore SQL Server Database Backup?

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:

Open SSMS and connect to your SQL Server instance.
Click the New Query option.
In the Query Editor window, run the following command to restore the entire database from the .bak file:

USE[master];

GO

BACKUP DATABASE [test1]

TO DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\Backup\test1.bak'

WITH NOFORMAT, NOINIT,

NAME = N'test1-Full Database Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10;

GO

Alternately, you can restore the SQL Server database backup using the SSMS or Windows PowerShell.

Common Issues and Errors while Restoring SQL Database Backup

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:

Error 3183: Corrupt SQL Backup File

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.

SQL Database Restore Failed, Database in Use

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.

Database cannot be opened: It is in the middle of a restore

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 error.

SQL Database Restore Error 5243

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.

The database cannot be recovered because the log was not restored

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.’

SQL Database Error 3241: Restore Headeronly

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 error.

SQL Backup Restore Error 3013

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.

Conclusion

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.

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’ command to identify if the SQL backup file is corrupt. If the backup file is corrupted, you can use an advanced SQL repair software, 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.