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.
- 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
- 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)
- 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
- 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
- Stop the instance of SQL Server if it is started.
- 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>');