Choose the Best Method for SQL server Database Moving

At times, it may require to moving SQL server data or log files from one to other location on the same SQL server. There are two methods that help you in performing this task; Attach/Detach & ALTER Database. Both methods perform the same task but there are numerous reasons why prefer Alter Database over Attach/detach method.

Attach/Detach Method: This method is used to move SQL server data or log files from one location to another location as mentioned above. Now have a look on the syntax of both the operations.

EXEC sp_detach_db 'your-database-name

After the successful detach of the database from SQL server instance, you can move the data & log file to a new location then after attach the files to new SQL server instance by below syntax.

EXEC sp_attach_db 'your-database-name

'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\your-database-name_Data.mdf',

'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\your-database-name_Log.LDF'

Microsoft’s online book topic sp_attach_db command has mentioned the below warning message:

“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).”

Alter Database Method: You cannot use this method without identifying the filenames associated with the database. So first identify the filenames by below query:

Select name, physical name from sys.master_files where database_id = ('your-database-name')

Now you will be able to use Alter database with Modify command for moving SQL server database files from one SQL server instance to another.

Aleter Database your-database-name Modify File (Name = your-database-name_Data, Filename = 'D:\SQLData\your-database-name_Data.mdf');

Aleter Database your-database-name Modify File (Name = your-database-name_Log, Filename = 'D:\SQLData\your-database-name_Log.ldf');

Now set your database as offline to complete the moving process by below syntax.

Alter Database your-database-name Set Offline

Move the files to a new location & bring your database online by below syntax.

Alter database your-database-name Set online

Attach/Detach	ALTER DATABASE
It will disable the service broker on the database.	It will not disable the service broker in the database.
It will disable the Trustworthy setting of the database.	It will not disable the Trustworthy setting for the database, if enableed.