Published on

September 2, 2010

Best Practices for Moving SQL Server Database Files

When it comes to moving data and log files within the same SQL Server instance, there are two commonly used methods: detaching and reattaching the database, or using the ALTER DATABASE statement with the MODIFY FILE option. While both methods achieve the same result, there are several reasons why the ALTER DATABASE method is considered a best practice.

Detaching and Reattaching the Database

The traditional approach to moving database files involves detaching the database from the SQL Server instance, moving the files to the new location in the operating system, and then reattaching the database. This method has been used for a long time and is familiar to many SQL Server users.

To detach the AdventureWorks database, for example, you would execute the following T-SQL statement:

EXEC sp_detach_db N'AdventureWorks'

After detaching the database, you can move the data files to their new location and then attach the database to the SQL instance using the following T-SQL 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'

While this method has been widely used, it is important to note that it is considered a legacy method and will be deprecated in future versions of SQL Server. Microsoft recommends using the CREATE DATABASE statement with the FOR ATTACH option instead.

Using ALTER DATABASE with MODIFY FILE

The preferred method for moving database files within the same SQL Server instance is to use the ALTER DATABASE statement with the MODIFY FILE option. This method offers several advantages over detaching and reattaching the database.

To move the data files for the AdventureWorks database using ALTER DATABASE, you first need to identify the filenames associated with the database:

SELECT name, physical_name FROM sys.master_files WHERE database_id = db_id('AdventureWorks')

Once you have the filenames and physical paths, you can use the ALTER DATABASE statement to move the files:

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');

After running these statements, you need to set the database offline:

ALTER DATABASE AdventureWorks SET OFFLINE

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

ALTER DATABASE AdventureWorks SET ONLINE

Advantages of Using ALTER DATABASE

Using ALTER DATABASE with the MODIFY FILE option offers several advantages over the detach/attach method:

  • Service Broker: When using detach/attach, Service Broker is disabled on the database, whereas it remains enabled when using ALTER DATABASE. Enabling Service Broker after a detach/attach operation requires exclusive access to the database.
  • TRUSTWORTHY: Attach/detach disables the TRUSTWORTHY setting for the database, while ALTER DATABASE does not. This is important for security reasons, as attaching a database from an untrusted source may pose a risk.
  • Stability and Consistency: While it is still possible to move a database using detach/attach, there may be potential unplanned consequences. Using ALTER DATABASE ensures expedience and stability of your application/database.

For these reasons, it is recommended to use the ALTER DATABASE method when moving a database within the same SQL Server instance.

By following best practices and using the appropriate method, you can ensure a smooth and secure process when moving SQL Server database files.

Click to rate this post!
[Total: 0 Average: 0]

Let's work together

Send us a message or book free introductory meeting with us using button below.