Detach and Reattach a Database
Overview
Occasionally you may want to detach a database while you perform some operation on the SQL Server instance, then re-attach the database after you’ve finished.
One such example might be when modifying the SQL Server instance collation without affecting any user-databases, by running sqlsrvr.exe -q, as described by Solomon Rutzky in this article.
This post provides a simple script that generates the T-SQL commands to detach and attach a database. It creates a SQL Server Agent job for each action. I prefer to use a SQL Server Agent Job to do both the detach and the attach since SQL Server will adjust the filesystem security of the detached files based on the Active Directory account that does the detach operation. Using SQL Server Agent means anyone with proper access can run the detach, and someone else can run the attach later, without any “Access Denied” errors.
The script
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 |
/* Creates two SQL Server Agent Jobs. The first detaches a database. The second re-attaches that database. Modify the @DBName parameter as required. By: Hannah Vernon, 2019-05-01 */ SET NOCOUNT ON; DECLARE @DBName sysname; DECLARE @msg nvarchar(max); DECLARE @Command nvarchar(max); DECLARE @jn sysname; DECLARE @ReturnCode int; DECLARE @jobId uniqueidentifier; DECLARE @Description nvarchar(512); SET @DBName = N'<database_name_here>'; --modify this! /* Create the job to Detach the database */ BEGIN TRANSACTION; SET @Command = N''; SELECT @Command = N'ALTER DATABASE ' + QUOTENAME(d.name) + N' SET SINGLE_USER WITH ROLLBACK IMMEDIATE; EXEC master.dbo.sp_detach_db @dbname = N''' + d.name + N''', @skipchecks = ''true'', @keepfulltextindexfile = ''true'';' FROM sys.databases d INNER JOIN sys.master_files mf ON d.database_id = mf.database_id WHERE d.name = @DBName AND mf.file_id = 1 AND mf.type_desc = N'ROWS'; SELECT @ReturnCode = 0 SET @jn = @DBName + N' : Detach Database'; IF EXISTS (SELECT 1 FROM msdb.dbo.sysjobs sj WHERE sj.name = @jn) BEGIN EXEC msdb.dbo.sp_delete_job @job_name = @jn, @delete_history = 0; END SET @jobId = NULL; SET @Description = N'Detach ' + @DBName; EXEC @ReturnCode = msdb.dbo.sp_add_job @job_name = @jn , @enabled = 1 , @notify_level_eventlog = 3 , @notify_level_email = 2 , @notify_level_netsend = 0 , @notify_level_page = 0 , @delete_level = 0 , @description = @Description , @owner_login_name = N'sa' , @notify_email_operator_name = N'DBA' , @job_id = @jobId OUTPUT; IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Detach Database' , @step_id=1 , @cmdexec_success_code = 0 , @on_success_action = 1 , @on_success_step_id = 0 , @on_fail_action = 2 , @on_fail_step_id = 0 , @retry_attempts = 0 , @retry_interval = 0 , @os_run_priority = 0 , @subsystem = N'TSQL' , @command = @Command , @database_name = N'master' , @flags=0; IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1 IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)' IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback PRINT N''; PRINT N'Job "' + @jn + N'" has been created.'; PRINT N'Run the job to Detach the ' + QUOTENAME(@DBName) + N' database.'; PRINT N'' /* Create the job to attach the database */ SET @Command = N''; SET @jobId = NULL; SELECT @Command = N'CREATE DATABASE ' + QUOTENAME(d.name) + N' ON PRIMARY (FILENAME = ''' + mf.physical_name + N''') FOR ATTACH; ' FROM sys.databases d INNER JOIN sys.master_files mf ON d.database_id = mf.database_id WHERE d.name = @DBName AND mf.file_id = 1 AND mf.type_desc = N'ROWS'; SELECT @ReturnCode = 0 SET @jn = @DBName + N' : Attach Database'; IF EXISTS (SELECT 1 FROM msdb.dbo.sysjobs sj WHERE sj.name = @jn) BEGIN EXEC msdb.dbo.sp_delete_job @job_name = @jn, @delete_history = 0; END SET @Description = N'Attach ' + @DBName; EXEC @ReturnCode = msdb.dbo.sp_add_job @job_name = @jn , @enabled = 1 , @notify_level_eventlog = 3 , @notify_level_email = 2 , @notify_level_netsend = 0 , @notify_level_page = 0 , @delete_level = 0 , @description = @Description , @owner_login_name = N'sa' , @notify_email_operator_name = N'DBA' , @job_id = @jobId OUTPUT; IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Attach Database' , @step_id=1 , @cmdexec_success_code = 0 , @on_success_action = 1 , @on_success_step_id = 0 , @on_fail_action = 2 , @on_fail_step_id = 0 , @retry_attempts = 0 , @retry_interval = 0 , @os_run_priority = 0 , @subsystem = N'TSQL' , @command = @Command , @database_name = N'master' , @flags=0; IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1 IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)' IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback COMMIT TRANSACTION PRINT N''; PRINT N'Job "' + @jn + N'" has been created.'; PRINT N'Run the job to re-attach the ' + QUOTENAME(@DBName) + N' database.'; PRINT N'' GOTO EndSave QuitWithRollback: IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION EndSave: |
Check the Microsoft Documentation for details about sp_detach_db.
See the rest of our SQL Server Tools here.