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.
|