How to restore a SQL Server Database to a new location and name

This post explains how to restore a full database backup to a new location and with a new name. This procedure enables you to move a database or create a copy of a database on either the same server instance or a different server instance. This example is usefully for a LocalDB instance of SQL Server 2012.

1. First, you need to know the logical database names (ex. “MyDatabase_data” and “MyDatabase_log”).

RESTORE FILELISTONLY FROM DISK = 'C:\MyBackup.bak'

1. Next, you can restore the backup to a new Database.

RESTORE DATABASE MyNewDataBase
 FROM DISK = 'C:\MyBackup.bak'
 WITH RECOVERY,
 MOVE 'MyDatabase_Data' TO 'C:\Data\NewData.mdf',
 MOVE 'MyDatabase_Log' TO 'C:\Data\NewData_Log.ldf'

Or, you can restore to an existing database, using same target folder files.

RESTORE DATABASE MyNewDataBase
 FROM DISK = 'C:\MyBackup.bak'
 WITH RECOVERY, REPLACE

To execute these commandes, you can use SQLCMD utility (available in C:\Program Files\Microsoft SQL Server\110\Tools\Binn).