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'

2. 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).

Advertisements
Tagged with: , ,
Posted in General, SQL Server, Tips and others

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow me
   RSS Feed    Twitter    Linked In

Write you email address to subscribe at this blog and to receive new posts by mail.

Join 567 other followers

Who I am?






%d bloggers like this: