This topic describes how to move a detached database to another location and re-attach it to the same or a different server instance in SQL Server. However, we recommend that you move databases by using the ALTER DATABASE planned relocation procedure, instead of using detach and attach. For more information, see Move User Databases.
Procedure
To move a database by using detach and attach
-
Detach the database. For more information, see Detach a Database.
-
In a Windows Explorer or Windows Command Prompt window, move the detached database file or files and log file or files to the new location.
You should move the log files even if you intend to create new log files. In some cases, reattaching a database requires its existing log files. Therefore, always keep all the detached log files until the database has been successfully attached without them.
-
Attach the copied files. For more information, see Attach a Database.
Example
The following example creates a copy of the AdventureWorks2022 database named MyAdventureWorks. The Transact-SQL statements are executed in a Query Editor window that is connected to the server instance to which is attached.
-
Detach the AdventureWorks2022 database by executing the following Transact-SQL statements:
USE master; GO EXEC sp_detach_db @dbname = N’AdventureWorks2022′; GO
-
Using the method of your choice, copy the database files (AdventureWorks208R2_Data.mdf and AdventureWorks208R2_log) to: C:MySQLServerAdventureWorks208R2_Data.mdf and C:MySQLServerAdventureWorks208R2_Log.ldf, respectively.
To copy files over the network to a disk on a remote computer, use the universal naming convention (UNC) name of the remote location. A UNC name takes the form ServernameSharenamePathFilename. As with writing files to the local hard disk, the appropriate permissions that are required to read or write to a file on the remote disk must be granted to the user account used by the instance of SQL Server.
-
Attach the moved database and, optionally, its log by executing the following Transact-SQL statements:
USE master; GO CREATE DATABASE MyAdventureWorks ON (FILENAME = ‘C:MySQLServerAdventureWorks2022_Data.mdf’), (FILENAME = ‘C:MySQLServerAdventureWorks2022_Log.ldf’) FOR ATTACH; GO
In SQL Server Management Studio, a newly attached database is not immediately visible in Object Explorer. To view the database, in Object Explorer, click View, and then Refresh. When the Databases node is expanded in Object Explorer, the newly attached database now appears in the list of databases.
See Also
Database Detach and Attach (SQL Server)