#2 Skill – Performing a Restore

This series of blog posts are related to my presentation, The Top Ten Skills You Need, which is scheduled for a few deliveries in 2011.

You Will Restore a Database

At some point you’ll need to restore a database. It might be a database on your local instance of SQL Server to correct a problem with a query or a patch, but you’ll need to restore data.

This goes along with the first skill of backing up a database as the counterpart. A backup saves the data (and objects) and a restore brings that data back.

Restores are fairly easy, but there are a couple of things you need to learn right away:

Always use NORECOVERY

By default the RESTORE command brings a database online by going through the recovery (redo and undo) processes. For a full database restore, this means you cannot restore additional logs. You might not to this time, but at some point you will.

So always use WITH NORECOVERY.

You need this in database mirroring, log shipping, and more scenarios. Always include this in your restore commands. To bring the database online when you are sure you are done restoring (even if this is only one restore), use the RESTORE command and WITH RECOVERY, as in:

RESTORE DATABASE db1 WITH RECOVERY 

Learn to move files

I find that many restores take place for practice, or on servers other than the original ones. In that case, the paths might not exist. Often the production servers, or the main servers you use, will have more drives than the test servers. In that case, having a file stored on the z: drive for a server doesn’t match up with a development server containing only a C: drive.

The WITH MOVE option is used to move the existing logical files in your restore to a new location. Here is the sample command from Books Online.

Script Restores

It’s easy to make mistakes with the GUI in SSMS. Learn to script restores and run the scripts. Even if you use SSMS to setup the restore, don’t click OK. Instead click this:

scriptrestore

About way0utwest

Editor, SQLServerCentral
This entry was posted in Blog and tagged , , , , . Bookmark the permalink.

1 Response to #2 Skill – Performing a Restore

  1. Pingback: The Top Ten Skills You Need « Voice of the DBA

Comments are closed.