Moving MS SQL Databases To A New Location

This document describes how to change the location of the data and log files for any MS SQL database. There are two ways to move MS SQL databases:

• Method 1
• Method 2

Method 1

1. Create new MS SQL data location (E:\MSSQL\data\)
2. Stop MS SQL server
3. Move all databases to a new location (move *.mdf and *.ldf files)
4. Create junction link between old and new MS SQL data folders. This can be done with Sysinternals Junction or any other utility of this kind
5. Start MS SQL server

In case you have some databases whith different from default locations, perform:

1. Detach these databases
2. Copy these databases from old location to a new location
3. Attach these databases from a new location

Method 2

1. Go to MS SQL Enterprise Manager
2. Choose the MS SQL server Properties option. For this, go to Expand SQL Server Group->MS SQL server <SQL Server_Name>
   

   

3. On the Database Settings tab, change New database location and set the path to:
   • Default data directory, i.e. a new logical disk (E:\MSSQL\DATA\)
   • Default log directory (E:\MSSQL\DATA\)

   

4. Create the following folder E:\MSSQL\DATA\
5. Set the same NTFS permissions as in the folder [drive]:\Program Files\Microsoft SQL\Server\MSSQL\DATA (the path where DB's are located ).
6. Go to MS SQL Enterprise Manager->Databases and right click on the Necessary database->All tasks->Detach Database with option Update statistics prior detach. Make sure to check database and database log files locations before detaching a database.
   

   

7. Go to [drive]:\Program Files\Microsoft SQL Server\MSSQL\DATA and copy Detached DB files (*.mdf and *.ldf) to a new folder E:\MSSQL\DATA\
8. Go to MS SQL Enterprise Manager->Databases and right click on Databases->Attach Database->.

   

9. Put the path to the necessary database (E:\MSSQL\DATA\) and select hsadmin in Specify database owner field.

   

10. Repeat steps 6-8 for the rest of databases.

All necessary information can be found in MS SQL documentation.