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.
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: