Restore a SQL Server Backup (WITH MOVE)

This is a basic skill can get overlooked, so let’s talk about a common DBA task: backing a database up and restoring it to another server.

First let’s dispel a couple of common misconceptions:

• You cannot restore a backup from a later version of SQL Server, to an earlier version. For example, a 2016 backup will not restore on to a 2012 instance!
• You cannot restore single objects from a native SQL Server backup. There are some third party tools that can do that, I believe, but SQL Server itself CANNOT restore a single table or SP.

So far, so good.

Now when we want to restore a backup, it’s very likely that the directory and/or file structure on the target server isn’t the same as the source. For example, maybe when we backed up DB1 on Server1, the database files were on D:\SQLServer\.

But we’re restoring to Server2, which doesn’t even have a D: drive. In fact, the data file(s) is supposed to go on M:\SQLData\, and the log should go on L:\SQLLog\.

Accomplishing this is a simple, two step process:

1. Get the logical file names and physical file names that you’ll be restoring. You can either look on the source server (using exec sp_helpdb ‘DBNAME’) or you can do FILELISTONLY.
2. Write your restore statement WITH MOVE.

Let’s talk about FILELISTONLY and WITH MOVE.

RESTORE FILELISTONLY

“FILELISTONLY” is a restore option that lets you see the logical and physical file names contained in the  backup. This is important to know, because you won’t always have access to the source server.

To use FILELISTONLY, simply run:

RESTORE FILELISTONLY 
FROM DISK = '\\location\folder\backupname.bak";

This will return the logical file name, physical file name, file type, and a bunch more information.

Let’s say that our results are:

Now that we have that info, we can write the RESTORE…WITH MOVE statement.

RESTORE WITH MOVE

Remember, we said we needed to restore DB1 so that the data file is in M:\SQLData\, and the log file is in L:\SQLLog\. Here is our restore statement:

RESTORE DATABASE [DB1]
FROM DISK = '\\location\folder\backupname.bak"
WITH MOVE 'DB1' TO 'M:\SQLData\DB1.mdf',
MOVE 'DB1_Log' TO 'L:\SQLLog\DB1_log.ldf';

Once you get used to the idea, RESTORE WITH MOVE makes perfect sense!