# Date: June 20th, 2007
# Author: Steven Warren
# Category: Backup, Data Recovery
# Tags: Database, Microsoft SQL Server, Backup, Server
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
[Only registered and activated users can see links. Click Here To Register...]
Search field
Figure B
[Only registered and activated users can see links. Click Here To Register...]
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
[Only registered and activated users can see links. Click Here To Register...]
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
[Only registered and activated users can see links. Click Here To Register...]
To Database
Figure E
[Only registered and activated users can see links. Click Here To Register...]
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
[Only registered and activated users can see links. Click Here To Register...]
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
</ul>
Figure G
[Only registered and activated users can see links. Click Here To Register...]
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
[Only registered and activated users can see links. Click Here To Register...]
Move to path
You are now ready to click OK and let the database be restored (Figure I).
Figure I
[Only registered and activated users can see links. Click Here To Register...]
Progress
You have now successfully restored and moved the database files as shown in Figure J and Figure K.
Figure J
[Only registered and activated users can see links. Click Here To Register...]
Restored
Figure K
[Only registered and activated users can see links. Click Here To Register...]
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
[Only registered and activated users can see links. Click Here To Register...]
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
[Only registered and activated users can see links. Click Here To Register...]
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
[Only registered and activated users can see links. Click Here To Register...]
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
[Only registered and activated users can see links. Click Here To Register...]
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.
i found it very usefull, and it helped me allot ! :)
# Author: Steven Warren
# Category: Backup, Data Recovery
# Tags: Database, Microsoft SQL Server, Backup, Server
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
[Only registered and activated users can see links. Click Here To Register...]
Search field
Figure B
[Only registered and activated users can see links. Click Here To Register...]
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
[Only registered and activated users can see links. Click Here To Register...]
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
[Only registered and activated users can see links. Click Here To Register...]
To Database
Figure E
[Only registered and activated users can see links. Click Here To Register...]
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
[Only registered and activated users can see links. Click Here To Register...]
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
</ul>
Figure G
[Only registered and activated users can see links. Click Here To Register...]
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
[Only registered and activated users can see links. Click Here To Register...]
Move to path
You are now ready to click OK and let the database be restored (Figure I).
Figure I
[Only registered and activated users can see links. Click Here To Register...]
Progress
You have now successfully restored and moved the database files as shown in Figure J and Figure K.
Figure J
[Only registered and activated users can see links. Click Here To Register...]
Restored
Figure K
[Only registered and activated users can see links. Click Here To Register...]
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
[Only registered and activated users can see links. Click Here To Register...]
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
[Only registered and activated users can see links. Click Here To Register...]
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
[Only registered and activated users can see links. Click Here To Register...]
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
[Only registered and activated users can see links. Click Here To Register...]
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.
i found it very usefull, and it helped me allot ! :)