SQL Server – Understanding Various Types of Restores in SQL Server – Volume 1

Performing a Database Backup is a part of DBA’s life. Backups are taken to ensure that in case of any emergencies Recovery is possible. When we talk about Recovery it means that we are referring to Restore. Understanding the various types of Restores is important because depending upon the situation the DBA needs to take the call. In the part 1 of this article,Satnam Singh a Senior SQL Server DBA from Larsen&Toubro Infotech,Mumbai,India discusses the following 4 types of Restores which are available in SQL Server.

• Restoring a Full Backup of Database.
• Restoring a Full as well as the Differential Backup.
• Restoring a Database using the RESTART option.
• Restoring using the VERIFYONLY option

Let’s discuss each one of them in detail.

• Restoring a Full Backup of Database

 Consider we have a Full Backup of the database named “Satnam” which needs to be restored on the server. Its Full Backup is present under the following path on the server,

D:\Satnam

Before starting the Database Restore, we first need to find the logical name of the data and log files of the database named Satnam using the backup which is present under the following path,

D:\Satnam

In order to perform the task, we will need to execute the below T-SQL query against the master database.

RESTORE FILELISTONLY FROM DISK='D:\Satnam\satnam_backup.bak'

Once this command is executed it gives us the logical name of the data and log files of the database named satnam, please refer the screen capture below:

The next step would be to perform the Restore. This can be accomplished by executing the below T-SQL query against the master database on the server.

RESTORE DATABASE satnam
FROM DISK='D:\Satnam\satnam_backup.bak'
WITH
MOVE 'satnam' TO 'D:\SQL\satnam_data.mdf',
MOVE 'satnam_log' TO 'D:\SQL\satnam_log.mdf',
RECOVERY,
STATS=10

The output of the above query is as shown in the screen capture below:

As you can see from the above screen capture, the database backup was obtained from the location “D:\satnam” and then the logical files named “satnam” and “satnam_log” were moved onto the location named  “D:\SQL” i.e. the location where the data and log files of the database needs to be stored. RECOVERY means that once the database gets RESTORED completely and is operational i.e. there will be no other backups which will be Restored over it.

STATS = 10 means that once the RESTORE procedure starts, the user will come to know How much portion of the database is Restored. Its unit is percentage. I have specified the parameter as 10 which means that the user will be able to view the Restoration success message on every 10% of the successful restore. You can modify the parameter as per your own convenience.

• Restoring Full and Differential Database Backups

As we all know that the Differential backup contains all the changes that have happened since Last Full Backup. There might be many occasions wherein something can go wrong with the database and a differential backup is also required to ensure minimal data loss. If you have the differential backup as well along with you then it ensures faster recovery of data.

Let us understand How the Differential Backup can be Restored over a Full Backup. Consider we have Full and Differential Backup of the database named “satnam” which needs to be Restored. The Full Backup is named satnam.bak and differential backup is named satnam_differential.bak and are present under the path named D:\Satnam on the server. In order to Restore the Full Backup, we will execute the below T-SQL query:

RESTORE DATABASE satnam
FROM DISK='D:\Satnam\satnam_backup.bak'
WITH
MOVE 'satnam' TO 'D:\SQL\satnam_data.mdf',
MOVE 'satnam_log' TO 'D:\SQL\satnam_log.mdf',
NORECOVERY,
STATS=10

As you can see from the above query we are restoring the Full Backup of the database named “satnam” using the Full Backup which is present under the following location “D:\satnam”. NORECOVERY specifies that additional backups can be restored over it and the database is currently not operational, please refer the screen capture below:

The next step was to restore the differential backup over the existing database which is in Restoring state as shown above and bring it ONLINE. This can be accomplished by executing the below T-SQL query against the master database.

RESTORE DATABASE satnam
FROM DISK='D:\Satnam\satnam_differential.bak'
WITH
MOVE 'satnam' TO 'D:\SQL\satnam_data.mdf',
MOVE 'satnam_log' TO 'D:\SQL\satnam_log.mdf',
RECOVERY,
STATS=10

As seen from the above query we are restoring the differential backup of the database named “satnam” whose named is satnam_differential.bak which is present under the following path on the server “D:\Satnam”

Once the differential backup is restored we need to bring it ONLINE so that it can be Operational. This was achieved by using the keyword named RECOVERY.

Please refer the screen capture below:

As you can see from the above screen capture that the database named “satnam”  is now ONLINE and ready for use.

• Restoring the Database Using the RESTART option

 There can be various scenarios wherein you have started Restoring the Full Backup of the database and suddenly the system SHUT Down to a sudden loss of Power Supply or perhaps due to some other reason. Microsoft has introduced Restore with Restart option which starts the Restoration from the actual point where it halted.

Consider a case we are restoring the Full Backup of the database named “satnam” using the below T-SQL query and the system SHUT’s down unexpectedly.

RESTORE DATABASE satnam
FROM DISK='D:\Satnam\satnam.bak'
WITH
MOVE 'satnam' TO 'D:\SQL\satnam_data.mdf',
MOVE 'satnam_log' TO 'D:\SQL\satnam_log.mdf',
RECOVERY,
STATS=10

During the time the RESTORE was happening the system suddenly ran out of Power Supply and the RESTORE operation halted. In order to resume the Restoration from where the Power went off, we will use something named Restore with Restart, the same is mentioned in the below query.

RESTORE DATABASE satnam
FROM DISK='D:\Satnam\satnam.bak'
WITH
MOVE 'satnam' TO 'D:\SQL\satnam_data.mdf',
MOVE 'satnam_log' TO 'D:\SQL\satnam_log.mdf',
RECOVERY,
STATS=10
WITH RESTART

From the above T-SQL query we can conclude with the RESTART option the system resumes the Restore from the point where the Restore operation actually halted.

• Verifying whether the Backup Copy is valid or invalid: RESTORE with VERIFYONLY

Many times we want to ensure that the backup copy which we have with us is a valid one or not. In order to ensure it we need to perform a Restore with VERIFYONLY. Consider a case wherein we need to identify whether the Full Backup of the database named “satnam” is a valid copy or not. In order to do so, I execute the below RESTORE statement against the master database on the server.

RESTORE VERIFYONLY FROM DISK='D:\Satnam\satnam_backup.bak'

Please refer the screen capture below:

The output message saying “The backup set on file 1 is valid”  confirms that the backup is a valid copy.

These were some of the basic RESTORE commands which we normally use in our day to day life. I hope you all enjoyed reading this blog post.

Regards

Satnam Singh

Like us on FaceBook  |  Follow us on Twitter

Follow me on FaceBook| Join the fastest growing SQL Server group on FaceBook