Detaching a Database
Detaching a database removes it from the instance of SQL Server but leaves the database intact within its data files and transaction log files. These files can then be used to attach the database to any instance of SQL Server, including the server from which the database was detached.
-- Cannot detach the database 'TestBackup01' because it is currently in use.
--Kick all users off of the database NOW
ALTER DATABASE TestBackup01 SET SINGLE_USER WITH ROLLBACK IMMEDIATE
--Kick all but after 60 seconds
ALTER DATABASE TestBackup01 SET SINGLE_USER WITH ROLLBACK AFTER 60 SECONDS
-- Set Offline
ALTER DATABASE TestBackup01 SET OFFLINE
--restore connection to users
ALTER DATABASE TestBackup01 SET MULTI_USER
EXEC sp_detach_db 'TestBackup01', 'true'; You cannot detach a database if any of the following are true:
- The database is replicated and published. If replicated, the database must be unpublished. Before you can detach it, you must disable publishing by running sp_replicationdboption. NoteIf you cannot use sp_replicationdboption, you can remove replication by running sp_removedbreplication.
- A database snapshot exists on the database.Before you can detach the database, you must drop all of its snapshots. For more information, see Drop a Database Snapshot (Transact-SQL). NoteA database snapshot cannot be detached or attached.
- The database is being mirrored in a database mirroring session.The database cannot be detached unless the session is terminated. For more information, see Removing Database Mirroring (SQL Server).
- The database is suspect. A suspect database cannot be detached; before you can detach it, you must put it into emergency mode. For more information about how to put a database into emergency mode, see ALTER DATABASE (Transact-SQL).
- The database is a system database.
Attaching a Database
When you attach a database, all data files (MDF and NDF files) must be available. If any data file has a different path from when the database was first created or last attached, you must specify the current path of the file.
CREATE DATABASE TestBackup02
ON (FILENAME = 'D:\MSSQL\DATA\TestBackup2\TestBackup.mdf'),
(FILENAME = 'D:\MSSQL\DATA\TestBackup2\TestBackup_log.ldf')
FOR ATTACH; Alternatively, you can use the sp_attach_db or sp_attach_single_file_db stored procedure. However, these procedures 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 ... FOR ATTACHinstead.
Sources:
Comments