source

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