//
you're reading...
Microsoft SQL

Restore MS SQL 2008 Database Backup (.bak) To A New SQL Server/Instance

Here’s how to restore an MS SQL .bak file onto a new SQL server/SQL instance with a simple script. I’ll show what to edit in the example below to restore the database My_DB.bak onto your new server

First things first, if you have no idea what the database name and file paths are within your .bak file, run the following script, editing it with the path to your .bak file. If you already know the details, skip to the Restore Database script

Get the Database names/file names

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

Provides the LogicalName of the Database and Log file, and the PhysicalPath of where the mdf and ldf files were origionally located. You can now use these details to restore the database with the correct name and if desired the same path using the RESTORE DATABASE script below

Restore Database Script

RESTORE DATABASE My_DB
FROM DISK = ‘C:\SQLBackups\My_DB.bak’
WITH REPLACE, MOVE ‘mdfLogicalName’ TO ‘C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\My_DB.mdf’,
MOVE ‘ldfLogicalName’ TO ‘C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\My_DB_log.ldf’

In the script, you just need to change ‘My_DB’ to your database name, mdfLogicalName/ldfLogicalName to the logical name of the mdf/ldf files, change the FROM DISK = path on line 2 to where your .bak file is, and also if needed change the paths where you want to restore the mdf and ldf files to on lines 3 and 4. In the script above, the paths are the default for MS SQL 2008 R2.

Lee

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: