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:

Figure L

New query

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.

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

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.

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 !

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

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.

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

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.

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

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.