s I’ve shown in my previous 10-Minute Solution, “Choosing the Right Backup in SQL Server,” SQL Server provides you with numerous ways to back up your database, allowing you to choose the options that best suit your situation. However, the best backup solution comes to naught if you don’t know how to restore your backups. Unless you know how to use the RESTORE command properly, however, you risk losing data.
In this 10-Minute Solution, I walk you through the RESTORE command and demonstrate its various options.
SQL Server has added powerful capabilities to the recovery process, such as creating a standby database. Unless you know how to use the RESTORE command properly, however, you risk losing data. What are the various options of the RESTORE command and when should you use them?
- The RESTORE database command will recover a complete database from a full backup.
- Use RESTORE FILELISTONLY in conjunction with the MOVE option when you need to move files to different locations.
- Always use the NORECOVERY option unless you are sure you are finished applying your backups.
- The WITH STANDBY option allows you to use run queries on your backup database.
- The RESTORE DATABASE command is also used for differential backups.
- To apply file backups, you need to issue both the RESTORE DATABASE and RESTORE LOG commands.
A full database restore can be executed by simply typing this:
As an example, I’ve backed up the Northwind database on my machine using the following command:
restore database [db_name]from [file_name]
In order to demonstrate the power of the RESTORE command, I will drop the Northwind database by executing:
backup database northwind to disk = ‘c:
The output from the DROP DATABASE command shows that the files used by the Northwind database have been deleted too.
drop database NorthwindDeleting database file ‘e:mssqlserverMSSQL$JOELAX2000_1data
orthwnd.ldf’.Deleting database file ‘e:mssqlserverMSSQL$JOELAX2000_1data
To restore the Northwind database from that file I can simply type:
SQL Server reads all the relevant information about the files used by the database from the backup and creates those files during the restore process. You can see how the files have been recreated after the restore process by executing:
restore database northwind from disk = ‘c:
You will see the same filenames as those previously deleted during the DROP command.
In this example, the syntax for the RESTORE command is very simple because I didn’t try to change anything regarding the database during the restore process. For instance, I kept the file locations the same. However, often enough during the restore process, you might want to change some of those options. As an example, your production database might exist on the D drive of the server while the database needs to be on the C drive of your backup machine.
Changing the File Location During the Restore Process
To demonstrate this technique, I will first attempt to create a second copy of the Northwind database on my machine, called Northwindrep. I’d like to have a second copy on a different set of drives that will be used for extensive reporting purposes.
However, when I execute this:
I get the following errors:
restore database Northwindrep from disk = ‘c:
In order for me to execute the restore statement, I have to specify different locations for the files because the current locations are being used by the Northwind database. To do so, I need to use the MOVE option, which uses the following syntax:
Server: Msg 1834, Level 16, State 1, Line 1The file ‘e:mssqlserverMSSQL$JOELAX2000_1data
orthwnd.mdf’ cannot be overwritten. It is being used by database ‘northwind’.Server: Msg 3156, Level 16, State 1, Line 1File ‘Northwind’ cannot be restored to ‘e:mssqlserverMSSQL$JOELAX2000_1data
orthwnd.mdf’. Use WITH MOVE to identify a valid location for the file.Server: Msg 1834, Level 16, State 1, Line 1The file ‘e:mssqlserverMSSQL$JOELAX2000_1data
orthwnd.ldf’ cannot be overwritten. It is being used by database ‘northwind’.Server: Msg 3156, Level 16, State 1, Line 1File ‘Northwind_log’ cannot be restored to ‘e:mssqlserverMSSQL$JOELAX2000_1data
orthwnd.ldf’. Use WITH MOVE to identify a valid location for the file.Server: Msg 3013, Level 16, State 1, Line 1RESTORE DATABASE is terminating abnormally.
If you aren’t familiar with the concept of a logical and physical filename, look up the syntax of the CREATE DATABASE statement in Books Online. When specifying a file while creating a database, you use the syntax:
move ‘[logical_file_name]’ to ‘[operating_system_file_name]’
name = [logical_file_name], filename = [physical_location]
Because I can’t remember by heart what the logical filenames are, I will first use another helpful command. Instead of RESTORE DATABASE, I type this:
name = Northwind, filename = ‘e:mssqlserverMSSQL$JOELAX2000_1data
This command provides a list of the logical database files and their corresponding physical locations. This allows me to find that I have two logical files, one called Northwind and the other called Northwind_log.
restore filelistonlyfrom disk = ‘c:
Using this information I can now complete the restore process by typing:
Notice, however, that while I was able to restore the backup to a different database name and change the location of the files, I couldn’t change the logical filename. This means that while the database name (Northwindrep) will be consistent with the physical names used, the logical filenames will still be Northwind and Northwind_log.To Recover or Not to Recover…
restore database Northwindrepfrom disk = ‘c:
orthwind.bak’with move ‘Northwind’ to ‘c:Northwindrep.mdf’,move ‘Northwind_log’ to ‘c:Northwindrep.ldf’
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:
You will receive the following error message:
select *from Northwind.dbo.categories
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.
Server: Msg 927, Level 14, State 2, Line 1Database ‘northwind’ cannot be opened. It is in the middle of a restore.
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 Northwindset recovery FULLbackup database northwind to disk = ‘c:
orthwind.bak’ with initbackup database northwind to disk = ‘c:
orthwind.df1’ with init,differentialbackup database northwind to disk = ‘c:
orthwind.df2’ with init,differentialrestore database northwind from disk = ‘c:
orthwind.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:
orthwind.df2’ with NORECOVERYrestore 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.