SQL SERVER RESTORE

25 Apr 2021 14610 views 0 minutes to read Contributors

Restore in SQL Server

Introduction

 

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 SQL Server DBA needs to take the call.

Restoring the database depends on the situation and backup type available at the time. Sometimes we need to take the backup as to create a copy of the backup for testing purpose from prod to test environment and sometimes we might want to restore the database in case of any failure or corruption. Also, the availability of the backups is also important and how we have formed the backups strategies in case of any database corruption or server failure.

In this Blog we will try to cover up below points:-

1)Restoring a database from a Full Backup

2)Restoring Full and then differential backup.

3)Restoring a log backup after Full and Differential.

4)Other restore options

 

1)Restoring a database From a Full Backup

Let’s take an example of Full database backup of our Test database SQLTreeoDEMO. We will then try to restore it.

 

 

Full backup of SQLTreeoDEMO database.

Now we will check the file information by using the Filelistonly option available.

 

Here we can see the logical file and physical file information available through the filelistonly option available. This information can be used in restoring the database.

Here we can see above that SQLTreeoDEMO_New database is restored as a copy to SQLTreeoDEMO.

2)Restoring Full and then differential backup.

We already have a full backup, now let’s take a differential backup of the SQLTreeoDEMO database.

 

Now Let’s restore a full backup again with norecovery option. To make it simpler we have dropped the newly created database and we will create it again.

 

We can see that Newly created SQLTrreoDEMO_New database is created with Restoring mode. It just means that it can allow to further restore differential or logs backups.

We will restore the differential backup now which was taken earlier.

In above screenshot we can see that differential backup has been restored and it is in recovery state now. We cannot restore any further backup once we have allowed it restore with recovery.

 

3)Restoring a log backup after Full and Differential.

Let’s take a log backup and try to restore it after full and Differential backup.

 

The above image shows a log backup of the database in continuation after Full and Differential backup

Now let’s try to restore Full backup and Differential backup with norecovery option and restore the log backup after that.

FULL BACKUP Restore with Norecovery Option

Now after this let’s take the differential backup with norecovery option.

 

Now let’s take restore the recently taken log backup with recovery option. This will help us to recover the database to the latest time possible.

 

 

 

We have an option to restore the database to a point in time in case of any failures. If we have available log backups then we have an option to restore it with option STOPAT, here we can restore it with no dataloss. One thing we need to keep in mind is that if we don’t have differential backups then we might need to restore many log backups to achieve minimum or no data loss. Differential backups are helpful to reduce the log backup restore after the latest full backup as it captures the DCM pages changed after the last full backup.

STOPAT

4) Other Restore Options

 

There are other options available in restore. Let us one by one look into all the options.

• Restore Filelistonly

The RESTORE FILELISTONLY option allows you to see a list of the files that were backed up.  So for example if you have a full backup you will see all of the data files (mdf) and the log file (ldf).

The restorefilelistonly option can be simply issued as follows for a backup that exists on disk.  If there are multiple backups in one file and you do not specify "WITH FILE = X" you will only get information for the first backup in the file.

 

 

• Restore Verifyonly

The RESTORE VERIFYONLY command checks the backup to ensure it is complete and the entire backup is readable. The does not do an actual restore, but reads through the file to ensure that SQL Server can read it in the event that a restore using this backup needs to occur.

The RESTORE VERIFYONLY option is a good choice to check each backup after the backup has completed. Unfortunately this takes additional processing time for this to complete, but it is a good practice to put in place

 

 

• Restore HeaderOnly

The Restore HeaderOnlyreturns a result set containing all the backup header information for all backup sets on a particular backup device in SQL Server.Restore HeaderOnly and Restore FilelistOnly are the two most important statements a DBA should issue when preparing to restore a backup