Browse DevX
Sign up for e-mail newsletters from DevX


Restoring Your Database Safely-3 : Page 3




Building the Right Environment to Support AI, Machine Learning and Deep Learning

To Recover or Not to Recover...
SQL Server allows you to create backups while the database is in use. This means that your backup can contain transactions that have not yet been committed. For the restored database to be in a consistent state, these uncommitted transactions need to be rolled back. This process is called "recovery." By default, if you don't specify any other option in the RESTORE command, the database will undergo this process.

What happens if you need to apply more than one backup in order to bring your database up to date? For example, suppose you have a full database backup and several transaction log backups that need to be applied. Each backup created might contain part of a transaction that is only completed and committed in the next file. When applying multiple backups, you don't want any uncommitted transactions to be rolled back until after the last backup is applied.

SQL Server therefore allows you to specify either RECOVERY or NORECOVERY in your RESTORE commands. Once you specify RECOVERY, you can no longer apply any more backup files without overwriting the whole database—that is, starting over with a full database restore. On the other hand, till the database is recovered, it is not usable.

In Listing 1 I've created a full backup of the Northwind database and then two transaction log backups. I then follow it with the corresponding RESTORE commands.

Let's look at the Restore 3 and 4 commands in more detail. Note that even in the last file I still specify the NORECOVERY clause, so the database still has not recovered yet. I then use a separate RESTORE command in which I don't specify a filename to use. Instead, I simply tell SQL Server to recover the Northwind database. I could have combined the two operations by simply changing WITH NORECOVERY in Restore 3 to WITH RECOVERY. However, I typically like to code the RECOVERY in a separate command. It gives me one last chance to check if indeed I've applied all the necessary files before starting the irrevocable recovery process.

...Or Just to Stand By?
Once you've issued a RESTORE command using WITH NORECOVERY the database is in an unusable state. You can demonstrate this by running the first command from the above script and then issuing a SELECT statement that queries the Northwind database:

select * from Northwind.dbo.categories

You will receive the following error message:

Server: Msg 927, Level 14, State 2, Line 1 Database 'northwind' cannot be opened. It is in the middle of a restore.

But SQL Server provides another very powerful option: WITH STANDBY. Instead of keeping the database unusable, the database can be put in a state where it is available for queries (not updates) yet can also be used if additional backups need to be applied. This means you can maintain a reporting server that can be fairly recent and used for heavy analysis. To update it, you simply continue to apply your transaction or differential logs at periodic intervals. If your production server goes down, you can quickly bring your reporting server up to date by applying the remaining backup files and issuing a RESTORE command with RECOVERY.

What WITH STANDBY = 'undo filename' does is tell SQL Server to "recover" the database to a consistent state, so all uncommitted transactions will be rolled back. However, the file specified stores the actions that SQL Server took to roll back those transactions. If you want to apply another backup, SQL Server can undo the effects of the recovery process and continue applying backup files.

You can try this for yourself by modifying Listing 1. Simply replace every occurrence of "WITH NORECOVERY" with "WITH STANDBY = [some _filename]".

Before issuing the last RESTORE command, try a SELECT statement. In this instance, you will get no error. Remember that when in standby mode, you can't update the database; only queries are allowed.

Restoring Differential Backups
Restoring a differential backup uses the exact same syntax as a full database restore. SQL Server tells from the backup file whether it is a full database backup or a differential backup and processes it accordingly. The following example demonstrates a full backup, a differential backup, and then the necessary restores:

alter database Northwind set recovery FULL backup database northwind to disk = 'c:\northwind.bak' with init backup database northwind to disk = 'c:\northwind.df1' with init,differential backup database northwind to disk = 'c:\northwind.df2' with init,differential restore database northwind from disk = 'c:\northwind.bak' with NORECOVERY /* Because I'm using differential backups, I only need the last differential backup made. See my article on "Choosing the Right Backup in SQL Server." */ restore database northwind from disk = 'c:\northwind.df2' with NORECOVERY restore database northwind with RECOVERY

Restoring File Backups
Restoring file backups can be a bit tricky. Remember that you will need to restore not only the file backup but also all the transaction logs since the file backup was taken. Otherwise, the files in the database won't be up to the same moment in time.

For the example shown in Listing 2, I use the test database that I created in my previous 10-Minute Solution, "Choosing the Right Backup in SQL Server." First, I back up the primary file group. Then I create two transaction log backups. Following those, I create a file backup for group2 and then one more log backup. In the restore process, I only restore the primary group (suppose that only those disks were damaged). In order to bring the primary group to the same state that group2 is up to, I also need to apply the transaction logs that I've created.

To make this example more realistic, you might want to execute transactions in between each backup.

Joe Lax has spent the last 10 years working in various database environments and has been a practicing DBA on all versions of SQL Server from version 4.2 onward. Joe is also an MCSE and an MCT. Recently, he has begun using Oracle and is on his way to obtaining his DBA certification as an Oracle DBA. He's always looking for another good project to get his hands dirty in.
Comment and Contribute






(Maximum characters: 1200). You have 1200 characters left.



Thanks for your registration, follow us on our social networks to keep up-to-date