In SQL Server, you can use Split (Detach) and additional (Attach) methods to move the database. A split database removes a logical database from the server, but does not delete the database files from the operating system. Attaching the database creates a new database and copies the data stored in the existing database files and transaction log files. Use system stored procedure sp_detach_db to split the database, and use system stored procedure sp_attach_db to attach the database.
The syntax for
sp_detach_db system stored procedures is as follows:
sp_detach_db [@dbname =] ' database_name '
[, [@skipchecks =] ' skipchecks ']
the value skipchecks in the [@skipchecks =] ' skipchecks ' clause is true or FALSE. When the value of Skipchecks is true, specifies that you do not need to perform the update STATISTICS command on all tables in the database before performing this procedure, and when false, you need to perform the update STATISTICS command.
The syntax for
sp_attach_db system stored procedures is as follows:
sp_attach_db [@dbname =] ' dbname ',
[@filename1 =] ' Filename_n ' [,... 16]
where "Filename_n" includes the path and physical name of the file. You can specify up to 16 files. The file must contain the primary database file. If you need to attach more than 16 files, you must replace the Create DATABASE command with the FOR ATTACH clause.
Note : sp_attach_db system stored procedures can only work on databases that have been split from the server using sp_detach_db system stored procedures.
example 6-16: Move the database mytest to the E:\SQL data directory.
(1) Run the system stored procedure sp_helpdb in SQL Server Query Analyzer To obtain information about the file name, number, type, location, etc. contained in the MyTest database. The command statement is as follows:
exec sp_helpdb mytest
(2) runs the sp_detach_db system stored procedure in SQL Server Query Analyzer and splits the MyTest database. The command statement is as follows:
exec sp_detach_db mytest, True
The results of the operation are as follows:
Successfully detached database ' Mytest1 '.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
(3) Move the database-related operating system files directly to the e:\ in the operating system's folder window using the Cut or Paste method SQL Data Server directory. Move D:\SQL data\mytest_data.mdf files and D:\SQLData\mytest_Log.LDF files to E:\ SQL Data 2000
(4) Run the sp_attach_db system stored procedure in SQL Server Query Analyzer, attaching files to the MyTest database. The command statements are as follows:
exec sp_attach_db @dbname = ' mytest ',
@filename1 = ' E:\sql data\mytest_data.mdf ',
@filename2 = ' E:\sql data\mytest_log.ldf '
The results of the operation are as follows:
Successfully attached database ' mytest '.
Now that you have finished moving the database, you can view information from the MyTest database in Enterprise Manager, or you can run the system stored procedure sp_helpdb in SQL Server Query Analyzer to see if the move was successful.
