Backup and Restore Strategies in SQL Server – Part 2


There are several high availability solutions, which can be used with SQL Server like AlwaysOn, Failover Clustering, Database mirroring. While these high availability solutions ensure maximum uptime for your databases, you need to set up backup and restore strategies to recover the data or minimize the risk of data loss in case a failure happens. In this article series, I am going to discuss in detail backup and restore strategies in SQL Server.

In my last article of the series, I talked about Backup and Restore strategies in detail. I talked about why there is need for appropriate backup and restore strategies and different types of backup processes. In this article of the series I am going discuss different types of restore processes and backup and restore scenario.

Types of Restore

Just as there are different types of backup processes, there are equivalent restoration types as well. But before I can talk about different types of restore, I would like to first discuss the recovery process while restorating.


Recovery is the process to roll forward all the committed transactions (redo phase) and roll back all the uncommitted transactions (undo phase) and is usually done with the last restore. When you do restore you need to specify either RECOVERY (default) or NORECOVERY clause.

NORECOVERY indicates that no recovery should occur. This allows roll forward to continue with the next restore operations in the chain. This should be specified with all the restore operations except the last one.

RECOVERY clause indicates that roll back will be done after roll forward is completed for the current restore. This should be used with the last restore in the overall restore process chain.

Based on the different types of backup, these are different restore options you might have:

Full Database Restore

You can restore an entire database from a full database backup, starting with a full database backup restore, which may be followed by restoring the last differential database backup and then restoring all the transaction log backups, if you have any, in sequence as discussed next. You should do the recovery of the database with the last restore only.

Transaction Log Restore

You can restore transaction log backups onto a database in chronological sequence to reach to the desired recovery point in the overall restore process, if you have the necessary transaction log backups. You generally need to back up Tail-log before starting to restore the database and restoring it as the final log backup before recovering the database to avoid data loss after a failure. Once you are done with restoring your last fulldifferential backup, you need to restore all the transaction logs back in chronological order created after the last fulldifferential backup.

Piecemeal Restore

You can restore a part of a database at one time. It begins by restoring the database in stages, first restoring the primary filegroup and then followed by restoration of one or more secondary file groups. You need to use RESTORE DATABASE along with the PARTIAL clause and specifying one or more secondary file groups to be restored.

File or Filegroup Restore

You can restore specific damaged files or file groups to a database in a multi-filegroup database.

Page Restore

If a page is corrupted or damaged, you can restore a specific page (only database page) to the database without restoring the whole database. It requires your database to be under full or bulk-logged recovery models and supported only for read/write file groups. When SQL Server encounters an error while accessing the page it marks it “suspect,” which can be identified in suspect_pages table in the msdb database. The goal of a page restore is to restore few individual pages for a faster restore than file restore and reduce the amount of data that would be offline during a restore operation. However, if you have more pages to restore, it is generally efficient to restore the file itself.

Backup and Restore Scenario

In a typical scenario, a database administrator sets up a process to take a full database backup occasionally, such as weekly, and takes a series of differential database backups at a shorter interval, such as daily. Next, the database administrator backs up the transaction log at frequent intervals, such as every 10 minutes or 1 hour.

This is just a recommendation not a rule as for a given type of backup, the optimal interval depends on factors such as the importance of the data, acceptance level of data loss, the size of the database, and modification being made to the database.

When you are restoring, you need to first restore the last full backup followed by the last differential back and then transaction log backup (taken after the last differential backup) in chronological order. Please note, you need to specify the NORECOVERY clause with all the restore operations other than the last one:

--Restore the last full database backup with NORECOVERY
RESTORE   DATABASE AdventureWorks2012
     FROM DISK = 'D:backupAdventureWorks2012.Bak'
--Restore the last differential database backup with NORECOVERY
RESTORE   DATABASE AdventureWorks2012
     FROM DISK = 'D:backupAdventureWorks2012Diff.Bak'
--Restore the transaction log backups in chronological sequence   and with the last restore use RECOVERY
RESTORE   LOG AdventureWorks2012
     FROM DISK = 'D:backupAdventureWorks2012Log.Bak'

Usually, you will use the RECOVERY clause with the last restore operation, as discussed above, but if you have not used it before, then you can execute the Restore command to recover a database without actually restoring a backup. This process is called recovery-only restore and can occur for an entire database or for one or more a files or file groups.

-- Restore database using WITH RECOVERY - Recovery-only restore
RESTORE   DATABASE AdventureWorks2012

If you want to do point-in-time recovery you can use STOPAT, STOPATMARK or STOPBEFOREMARK clause with the Restore command.


In this article of the series, I talked about different types of restore and discussed the backup and restore scenario.


Backup Overview (SQL Server)

BACKUP (Transact-SQL)

Restore and Recovery Overview (SQL Server)

RESTORE (Transact-SQL)

See all articles by Arshad Ali

Arshad Ali
Arshad Ali
Arshad Ali works with Microsoft India R&D Pvt Ltd. He has 8+ years of experience, mostly on Microsoft Technologies. Most recently, as a SQL Developer and BI Developer he has been working on a Data Warehousing project. Arshad is an MCSD, MCITP: Business Intelligence, MCITP: Database Developer 2008 and MCITP: Database Administrator 2008 certified and has presented at several technical events including SQL-School. On an educational front, he has an MCA (Master in Computer Applications) and MBA-IT. Disclaimer : I work for Microsoft and help people and businesses make better use of technology to realize their full potential. The opinions mentioned herein are solely mine and do not reflect those of my current employer or previous employers.

Get the Free Newsletter!

Subscribe to Cloud Insider for top news, trends & analysis

Latest Articles