Data Management

How Do I... Restore a SQL Server database to a new server?


This blog entry is also available as a TechRepublic gallery and as a TechRepublic download.

As a DBA, I have been in many situations where I had to move databases to a new server due to old hardware, failed hardware, failed drives, etc. You have two options you could use to carry out this task:

  • SQL Server Management Studio (GUI)
  • T-SQL (Command line)

The method I prefer is T-SQL. It is simple and easy and avoids clicking the mouse all over the screen. This How do I... will show you both methods and allow you to choose the one you prefer.

Moving a database with Microsoft SQL Server Management Studio

We will begin by opening SQL Server Management Studio from the Start Menu by choosing Start and typing SQL Server in the Instant Search field (Figure A) The SQL Server Management Studio appears (Figure B) and it will be the main area you use to restore your backups.

Figure A

Search field

Figure B

SQL Server Management Studio
Note: I am going to assume that you already know how to backup a SQL Server database and that you have placed the backups on a file server or copied the backups to the new server. We will continue the tutorial from this point. Now that you have the Management Studio opened, right-click on Databases and choose Restore Database (Figure C).

Figure C

Restore Database
The Restore Database window appears and we will begin by typing the name of the Database we want to restore in the To Database field (Figure D) and choosing the From Device radio button to choose where your backup file is, shown in Figure E.

Figure D

To Database

Figure E

From Device
Your file now appears in the Select backups to restore text box. Place a check in the checkbox to continue as shown in Figure F.

Figure F

Select backups
You are now at the critical point of the restore where you choose Options from Select a Page. This is where you specify a new path for your database files. It is the same as the move option that will be discussed later in this tutorial. Simply type a new path to the database and log file (Figure G). For example, the current structure is the following:
  • C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\Database_Name_Here.mdf
  • C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\Database_Name_Here_1.ldf

Figure G

New path
We want to move these database files to a new path. Simply type the new path (Figure H). For purposes of this tutorial, we will move it to the following:
  • D:\ SQL\DATA\Database_Name_Here.mdf
  • D:\SQL\Logs\Database_Name_Here_1.ldf

Figure H

Move to path
You are now ready to click OK and let the database be restored (Figure I).

Figure I

Progress
You have now successfully restored and moved the database files as shown in Figure J and Figure K.

Figure J

Restored

Figure K

Database moved

Let's move on to my preferred method which eliminates all the point and clicking. You can do this same thing using a TSQL Restore with move statement.

Moving a database with T-SQL

Let's begin by opening up SQL Server Management Studio and clicking the New Query button (Figure L).

Figure L

New query

Our first step will be to run the following query:

Restore FILELISTONLY FROM DISK='d:\Business_Data.bak'

This query allows us to find out the logical name of the database and log file which is needed to appropriately restore a database to a new path (Figure M).

Figure M

Logical names

Once we have these names, we will use the following query to restore a database to a new location.

RESTORE DATABASE Business_Data_TSQL

FROM DISK='d:\Business_Data.bak'

WITH

MOVE 'Business_Data' TO 'D:\TSQL\Business_Data.mdf',

MOVE 'Business_Data_log' TO 'D:\TSQL\Business_Data_log.ldf'

This query will restore the database to a new path (Figure N).

Figure N

Restore to new path
You can see where the logical name and the physical name are necessary for the Restore FileListOnly TSQL statement. You can also add the stats clause if it is a big database to know the percentage finished (Figure O).

RESTORE DATABASE Business_Data_TSQL

FROM DISK='d:\Business_Data.bak'

WITH

MOVE 'Business_Data' TO 'D:\TSQL\Business_Data.mdf',

MOVE 'Business_Data_log' TO 'D:\TSQL\Business_Data_log.ldf', STATS=5

Figure O

Percentage finished

In this tutorial, I restored Full Backups. If you are restoring differential or transactions log backups, do not forget to use the With NORECOVERY clause in your statement.

28 comments
microsoftpabitra
microsoftpabitra

RESTORE DATABASE Business_Data_TSQL

FROM DISK='d:\PabitraDemoDb.bak'

Its Working.

Pabitra Behera.

Bhubaneswar,Odisha ,India

microsoftpabitra
microsoftpabitra

RESTORE DATABASE Business_Data_TSQL

FROM DISK='d:\PabitraDemoDb.bak'

Its Working

Tony Hopkinson
Tony Hopkinson

If you mean you have differential / tranactional backups. then you restore each one in order. You end up with new sterver in teh same state as the old as per the time of the last backup. If you mean you have a .bak file for each database on the server, then simply restore each one. You can do backup sets, but that's pretty much a waste of time as you'd have to dig each database you wanted to restore out the set anyway and you'd have to backup to a set on the old machine. As for "merging" .bak files. Not just no, but hell no... That's essentially merging databases, which is a whole 'nother ball game...

Anamika17
Anamika17

where shall i add my destination server name?

mbheitz
mbheitz

Thank you, thank you, THANK YOU! Your instructions worked perfectly.

vikramkasha
vikramkasha

how can i find out where the restore is going to ,i mean i have boxes like dev,test and production then how can i find out using T-SQL is that possible??

tran_toan
tran_toan

Restoring Full Backup and Differential backup is straight-forwarded. Restoring Log Backup is tricky. I wish you could show the Log Backup so that I can have everything from the old database moved to the new database in the new server.

mkek
mkek

Good info. I will try this. I would like to know if you can extract a program and database(without data) onto a CD?

vnnshah
vnnshah

Was really informative. Let me try it once and post my feedback soon

tharnish04
tharnish04

Ok i am satisfied and feel good one

mdekarske
mdekarske

That's the easy part. Now try to get the logons and security right. There is a lot of handwork involved there. Does anyone have any short cuts to copying logons and security?

lemor2011
lemor2011

Hi sir, how can i merge a different .bak file in one database. cu'z we have different location to transfer a new server Is there any possible solution ? thanks a lot:)

locotx_ftw_2002
locotx_ftw_2002

I just needed a way to copy a database (everything) from a server that was saving the database data onto the D:\ drive. But when I tried to restore on another box, the D:\ drive was assigned to the CD/DVD rom. The T-SQL script worked like a charm. Thanks !

CGSJohnson
CGSJohnson

MKEK, did you mean like a VB program or something that uses a SQL DB? Yeah, you can do this. Just use the Packaging and Deployment wizard. Also, if you script out the table and other database object definitions and include those in a folder with instructions, that folder can be written to CD. I hope that this helps. Thanks. Chris

Steven S. Warren
Steven S. Warren

I will continue this How Do I article with how I move logins and reset permission from one server to another. Are you interested?

jd
jd

Good point! There is a free tool that will create a T-SQL script to automatically copy or move SQL Server users and permissions from one database to another -- saving a lot of time and confusion. It's called SQLpermissions and it can be downloaded free at www.idera.com/freetools.

mg_roberts
mg_roberts

I just script out all the logons and other objects i create in the Db like maintenance plans and such, then when moving to a new server i just run the scripts to re-create them.

mkek
mkek

Hi Chris, Yes, the program is written in Visual Studio.net 2003 and uses SQL 2000 as the database. Sorry, I'm not a DBA or programmer. Is the packaging and deployemnt wizard located in SQL? Thank you for your help. Mike

mharvey
mharvey

I can't find the follow on topic where we can learn how to move the logons and permissions to the new server. please help point me in the right direction

Steven S. Warren
Steven S. Warren

Microsoft also has a sp called sp_help_revlogin that will moved logins with passwords from one server to another.

CGSJohnson
CGSJohnson

No problem. I hope that it works for you. Thanks. Chris

mkek
mkek

Hi Chris, Yes, it is a VB.Net program with SQL db on the back end. I did copy the project folder located in IIS, and the program folder which holds the RDL files onto a CD. I will try your suggestion. Thanks a lot for your help.

CGSJohnson
CGSJohnson

Hey, Mike. If it's a VB.Net program, you should be able to just transfer the project folder to a CD. Also, scripting the database objects can be done via Enterprise Manager or SQL Management Studio (right click on the database or the database object and select "Generate SQL Script"). Then just include those scripts in a subfolder under the Visual Studio project. I don't really use Visual Studio all that much, so I am not sure about that. However, I know that you can generate the SQL scripts in the way that I described. I hope that this helps. Thanks. Chris

jd
jd

We offer a free tool called SQLpermissions that will create a custom T-SQL script for you so that you can quickly move logons and permissions from one server to another. Go to www.idera.com/freetools to download.

Editor's Picks