At times it can be necessary to move the data and or log files from one location to another on the same SQL Server. There are two ways to go about doing this task, detaching the database from the SQL Server Instance, moving the files to the new location in the operating system, and then reattaching the database to the SQL Server Instance, and using ALTER DATABASE with the MODIFY FILE option to move the files through a metadata switch, taking the database offline, moving the file in the operating system and then bringing the database back online. Both accomplish the same task, but there are a number of reasons why the ALTER DATABASE method can make more sense for doing this kind of task.

First lets look at the syntax of both operations. Using the AdventureWorks database as an example, to move the database files from their current location to a new one by detatching the database issue the following TSQL statement:

EXEC sp_detach_db N'AdventureWorks'

After the database is detached, the data files can be moved to their new location and the database can then be attached to the SQL instance with the following TSQL statement:

EXEC sp_attach_db N'AdventureWorks',
'c:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\AdventureWorks_Data.mdf',
'c:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\AdventureWorks_Log.LDF'

This probably isn't news to many people since this is how moving databases has been performed in SQL Server for a long time. In fact there are Microsoft Knowledgebase articles that cover through SQL Server 2005, showing this as an appropriate method to move database files.

However, there are a number of problems that can be introduced by using this legacy, and soon to be deprecated method in SQL Server 2005 and SQL Server 2008. The sp_attach_db command topic in the Books Online has the following common warning for features that will be removed in the future:

This feature will be removed in a future version of Microsoft SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use this feature. We recommend that you use CREATE DATABASE database_name FOR ATTACH instead. For more information, see CREATE DATABASE (Transact-SQL).

To move the data files for AdventureWorks Doing a detach/attach operation on a simple database like AdventureWorks using the more current ALTER DATABASE first you need to identify the filenames associated with the database:

select name, physical_name
from sys.master_files
where database_id = db_id('AdventureWorks')

Once the filename and physical_name have been determined, the database can be moved using ALTER DATABASE with the MODIFY FILE command as follows:

ALTER DATABASE AdventureWorks
MODIFY FILE (NAME = AdventureWorks_Data, FILENAME = 'D:\SQLData\AdventureWorks_Data.mdf');
ALTER DATABASE AdventureWorks
MODIFY FILE (NAME = AdventureWorks_Log, FILENAME = 'D:\SQLData\AdventureWorks_Log.ldf');

Once you have run the above statements, to complete the move, set the database offline:

ALTER DATABASE AdventureWorks SET OFFLINE

and then move the data files to the new location, then bring the database back online:

ALTER DATABASE database_name SET ONLINE

So why exactly is this important, and what difference does it really make? Well, there are a number of things that can be affected by the use of attach/detach that are not affected when using ALTER DATABASE. For example if your database uses Service Broker, by using detach/attach, Service Broker is disabled on the database, whereas when using ALTER DATABASE MODIFY FILE, Service Broker remains enabled. To re-enable Service Broker for the database requires exclusive access to the database, which means that you will have to kick any active connection out of the database to use ALTER DATABASE ENABLE BROKER, once you realize that there is a problem. In addition, if you have enable TRUSTWORTHY for the database for SQLCLR or cross database ownership chaining, this is disabled using attach/detach where it is not using ALTER DATABASE MODIFY FILE. The reason for this is security. When you attach a database, it may not be from a trusted source, and for this reason, TRUSTWORTHY is always disabled upon attaching the database making it necessary for a DBA to reset this flag marking the database as trusted.

While it is possible to still move a database to a different file system location using detach/attach, there are potential unplanned consequences to doing so. For expedience and stability of your application/database, ALTER DATABASE should be the preferred method of moving the database inside of the same SQL Instance.