//
you're reading...
Microsoft SQL

Restore SQL database from .bak as a different name

Follow the below to restore an MS SQL database backup (.bak) as a different name, changing the paths to the .bak, .mdf and .ldf files as appropriate in your environment.

The only caveat with this is that the Logical Names of the database will remain the same as the database it was restored from, though this will not affect functionality of the database under the new name.

1. Run the script below using, using the path to the .bak file to restore

RESTORE FILELISTONLY
FROM DISK =
‘C:\SQLBackups\My_DB.bak’

From the results of this, you need to note down the LogicalName of the mdf and ldf files. In my example below the mdf logical name is AdventureWorks2008R2_Data and the ldf name is AdventureWorks2008R2_Log

sql_restore_filelistonly
Example output showing mdf and ldf logical names

2. Now run the following on the SQL server to restore the database as a new name, replace MyNewDatabaseName with what you want to call your database, and use the logical names from above in the MOVE statements

RESTORE DATABASE [MyNewDatabaseName]
FROM DISK = ‘C:\SQLBackups\AdventureWorks2008R2.bak’
WITH FILE = 1,
NOUNLOAD,
STATS = 10,
RECOVERY,
REPLACE,
MOVE ‘AdventureWorks2008R2_Data’ TO ‘C:\SQLData\MyNewDatabaseName_Data.mdf’,
MOVE ‘AdventureWorks2008R2_Log’ TO ‘G:\BAProject\MyNewDatabaseName_Log.ldf’
GO

Discussion

No comments yet.

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 )

Google+ photo

You are commenting using your Google+ 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 )

w

Connecting to %s

%d bloggers like this: