How to Restore Database Backup With T-SQL

Let’s learn how to restore a SQL Server database backup for Microsoft SQL Server. Restoring is a method of copying data from a backup and applying logged transactions to the data. Restore is basically taking a database backup and turning it back into a database. There are different procedures of restoring a database backup which include using T-SQL code, SQL Server Management Studio, or third-party applications. This article will not dive into how backups are taken but you should at least be aware that backups are taken purposely to be restored when the database becomes corrupt or crashes, migrating the database, making a copy of the database, and other business requirements. In this crash course, we will be focusing mainly on how to restore using the T-SQL code.

Prerequisites

There is the assumption that the backup for the database is readily available and the file location is known. We also have permission to access the file/directory as long as there are no corruption or disk issues with the backup file. Also, during the restore process of the database, you will need exclusive access to the database, which means no other user connections can connect to the database.

Finally, the version of the database cannot be greater than the version of the SQL Server that backup needs to be restored on. For example, you cannot restore a database with version 130 (SQL Server 2016) on a SQL Server 2012 or version (110). Learn more here. 

Steps to Restore Database Backup Using T-SQL

RESTORE DATABASE is a very common and universal T-SQL command to restore SQL Server backups since the language works in almost any environment or tool that understands it. Therefore, you can execute them in SQL Server Management Studio, SQLCMD utility, or any other third-party tool. The RESTORE DATABASE command is mainly used you to restore either a full, differential, file, or filegroup backup. We are going to look at the methods to restore different backups and other options that can be applied to the restore command process.

1. Restore Full SQL Server Database Backup

First, let’s start with the most common restore of a full backup. These backups contain all information needed to restore your database to the point in time when the backup process had finished. The restore process of the backup could potentially overwrite your existing database or create a new one depending on how you write the code. Let’s look at the basic restore your full backup which is stored at a folder location C:\MSSQL\Backup\MyDB_full.bak and you want to restore it to MyDB database. You need to execute the following commands:

RESTORE DATABASE FROM DISK = 'C:\MSSQL\Backup\MyDB_full.bak'

p>Note that the above T-SQL command will restore the full backup completely and bring the database online. If you had additional differentials or transaction logs, you would not be able to restore that on top of the database. In order to continue with restoring differential or transaction log backups after that, you need to add to use the NORECOVERY option. This sets the database in a restoring state and allows you to restore extra differential or transaction log backups to it.

xxxxxxxxxx

RESTORE DATABASE MyDB FROM DISK = 'C:\MSSQL\Backup\MyDB_full.bak' WITH NORECOVERY

2. Restore Differential SQL Server Database Backup

Differential backup is basically the changes that occurred in the database since the last full backup was taken. The differential backup of a full backup amasses all the changes so all previous differential backups are not needed to restore a database, just the last one of the point-in-time you need to restore to. Prior to restoring the differential backup, you will need to restore the last full backup first with the NORECOVERY option and then the last differential backup with the RECOVERY option:

xxxxxxxxxx

RESTORE DATABASE MyDB FROM DISK = 'C:\MSSQL\Backup\MyDB_full.bak' WITH NORECOVERY

GO

RESTORE DATABASE MyDB FROM DISK = 'C:\MSSQL\Backup\MyDB_diff.bak' WITH RECOVERY

GO

3. Restore Transaction Log SQL Server Database Backup

Transaction log backups cover all transactions that happened between the first full backup or the last taken transaction log backup and the time when the backup process had finished. In restoring transaction logs, you have to restore all transaction log backups sequentially after the last differential backup. Also, keep in mind that all your sequential log restorations should be done in NORECOVERY until the last log to be restored. The last one should be restored with RECOVERY to bring the database online. Finally, log backups are the final set of backups to be restored after full and differential backups have completed in a NORECOVERY state.

xxxxxxxxxx

RESTORE DATABASE MyDB FROM DISK = 'C:\MSSQL\Backup\MyDB_full.bak' WITH NORECOVERY

GO

RESTORE DATABASE MyDB FROM DISK = 'C:\MSSQL\Backup\MyDB_diff.bak' WITH NORECOVERY

GO

RESTORE LOG MyDB FROM DISK = 'C:\MSSQL\Backup\MyDB_log1.trn' WITH NORECOVERY

GO

RESTORE LOG MyDB FROM DISK = 'C:\MSSQL\Backup\MyDB_log2.trn' WITH RECOVERY

GO

4. Restore Using a Backup File That Has Multiple Backups

These backup files set up as a single file but contain multiple backups and positions. For example, you can write all your backups both full and different to one single file at C:\MSSQL\Backup\MyDB_full.bak. The RESTORE HEADERONLY command can be used to preview the contents of the file as in the backups and the positions. Once we know where backups and positions are in the file, you can perform a restore as below. In this example, position 1 is the full back up and position 2 is the differential backup.

xxxxxxxxxx

RESTORE DATABASE MyDB FROM DISK = 'C:\MSSQL\Backup\MyDB_full.bak' WITH NORECOVERY, FILE = 1

GO

RESTORE DATABASE MyDB FROM DISK = 'C:\MSSQL\Backup\MyDB_full.bak'  WITH FILE = 2

GO

5. Restore Full Backup WITH MOVE

Backups do not only backup the data in the database but also meta-data and configurations like the physical path where the database files are stored. During a restore, if the WITH MOVE is not utilized then SQL Server would attempt to restore the database files to exactly where they existed on the source database. You may encounter an error if this path does not exist, hence the WITH MOVE can be utilized to specify where you will like to save the restored database files. The T-SQL code below demonstrates such scenario.

xxxxxxxxxx

RESTORE DATABASE MyDB FROM DISK = 'C:\MSSQL\Backup\MyDB_full.bak' 

WITH MOVE 'MyDB' TO 'D:\Data\MyDB.mdf',

MOVE 'MyDB_Log' TO 'D:\Data\MyDB_Log.ldf'

6. Restore Full backup Using WITH REPLACE

In a case of restore over an existing database in SQL Server, the WITH REPLACE option is utilized. There are times where a restore will throw an error of “The tail of the log for the database [xxxx] has not been backed up." If you do not care about getting a tail log then using the WITH REPLACE option permits you to overwrite an existing database without first backing up the tail of the transaction log. This command basically indicates that ignore any active transactions in the transaction log and move forward with the restore. The T-SQL code below will restore the database and disregard any active entries in the current transaction log.

xxxxxxxxxx

RESTORE DATABASE MyDB FROM DISK = 'C:\MSSQL\Backup\MyDB_full.bak' 

WITH REPLACE

Conclusion

This demonstration is a seamless setup where you are not facing any corruption, access, lost database files, and virus affected databases. In this case, you can easily restore a database by writing a simple T-SQL script as shown above to achieve a full database restore. 

Did you find yourself in the worst scenario where you cannot complete a restore due to the possible corruption in the database or even the backups? Good news, I would like to introduce to you a robust SQL Database Repair Tool  that will not only fix your database or backups with the least minimal data loss but also potentially help you restore deleted records from the database.