How to move SQL Server user databases?

Moving user databases files to a new location is very common task for any SQL server DBA.

Planned reallocaiton

Since SQL Server 2005 there are 3 ways to accomplish this task.

1. Taking database offline to move the data, log, and full-text catalog files of a user database to a new location by specifying the new file location in the FILENAME clause of the ALTER DATABASE statement. It’s performed to move database files within the same SQL Server server
2. Backup and restore the database in the new location with or without move. The RESTORE … WITH MOVE option allows you to restore your database, but also specify the new location for the database files (mdf and ldf files)
3. Detach and Attach database. It’s used to move a databases to another instance of SQL Server or to another server. There are some restrictions for this operation. It not supported for
   • System databases
   • Mirrored databases
   • Databases in replicaiton
   • Databases that have a snapshot

Note: sp_attach_db command is depreciating after SQL Server 2008. The recommend way is to  use CREATE DATABASE database_name FOR ATTACH instead.

I found lovely script helps migrating all user database from one server to a new server location. http://trentgardner.net/sql/move-sql-server-database-files.

Note: Method 2 & 3 is usually used to move user databases to another server. You also must transfer all databases metadata including the logins, jobs etc to the new server as well

 

Moving Database files within a server

Why would you do this? The most common problem is running out of space on the system drive for example when database was installed with application. Last week I received error message from UAT team

 

After my investigation I noticed that database data and log files were installed on a C:\ drive. So the task is to move both data and log file to other drive with plenty of space.

Plan of action

 

1. Obtain the logical file name, query the name column in the sys.master_files catalog view.

SELECT name, physical_name AS CurrentLocation, state_descFROM sys.master_files
WHERE database_id = DB_ID(N'shifttrack');

2. Run the following statement.

ALTER DATABASE shifttrack SET OFFLINE

3. Move data and log files to the new location. 

4. Run the following statement.

ALTER DATABASE shifttrack MODIFY FILE ( NAME = shifttrack, FILENAME = 'D:\MSSQL\DATa\shifttrack.mdf)

ALTER DATABASE shifttrack MODIFY FILE ( NAME = shifttrack_log, FILENAME = 'D:\MSSQL\DATa\shifttrack_log.ldf)

5. Run the following statement.

ALTER DATABASE shifttrack SET ONLINE

6. Verify the file change by running the following query.

SELECT name, physical_name AS CurrentLocation, state_desc
FROM sys.master_files
WHERE database_id = DB_ID(N'shifttrack');

 

Some useful queries:

Check connection to the DB

USE master
SELECT * FROM sys.sysprocesses WHERE dbid = DB_ID('MyDB')

 To force disconnections

USE master
ALTER DATABASE MyDB SET SINGLE_USER WITH ROLLBACK IMMEDIATE

 

Reallocation due to failure

The sequence below is actually suggested by Microsoft

1. Stop the instance of SQL Server if it is started.
2. Start the instance of SQL Server in master-only recovery mode by entering one of the following commands at the command prompt.

For the default (MSSQLSERVER) instance, run the following command.

    NET START MSSQLSERVER /f /T3608

For a named instance, run the following command.

 o    NET START MSSQL$instancename /f /T3608

3. For each file to be moved, use sqlcmd commands or SQL Server Management Studio to run the following statement.. 

ALTER DATABASE database_name MODIFY FILE( NAME = logical_name , FILENAME = 'new_path\os_file_name' )

4. Exit the sqlcmd utility or SQL Server Management Studio.

5. Stop the instance of SQL Server.
6. Move the file or files to the new location.
7. Start the instance of SQL Server. Run: NET START MSSQLSERVER

8. Verify the file change by running the following query.

SELECT name, physical_name AS CurrentLocation, state_desc
FROM sys.master_files
WHERE database_id = DB_ID(N'<database_name>');