Automate SQL Server restores to refresh test environments
Creating an automated task to restore your database as needed enables you to easily refresh a development or test environment. Edgewood Solutions' Greg Robidoux offers a list of steps and a sample script to help you automate restores in this tip.
Why and how to automate SQL Server restores
Continue Reading This Article
Enjoy this article as well as all of our content, including E-Guides, news, tips and more.
You most likely refresh a development or test environment frequently with a recent production environment backup. Depending on what you are testing or developing, current data may be critical to ensuring your results are valid. Creating an automated task to restore the database regularly (weekly, daily or even several times a day as needed) will save lots of time and guarantee that you do not miss any necessary restore steps.
At a high level, you should complete the following steps to refresh a test or development environment using backup and restore as the refresh method:
More on SQL Server backup and recovery
Learn how to solve SQL VSS writer problems during backup
Read about splitting SQL Server backups to multiple files
Check out this tip about fault tolerance, backups and snapshots
2. Kill connections to database: In order to do a restore, there can be no active connections to the database. All connections to the database must be killed before you start therestore.
3. Restore the database: Issue the restore command.
4. Re-link users and logins: Users and logins are associated by SIDs (security identifiers). These may not be the same on your production and test environments, so you will need to re-link them. For more information, see this previous tip: Restoring a database from another SQL Server
The biggest issue you will probably face is getting all users off the system to perform the restore. One feature in SQL Server 2000 allows you to set the database to single-user mode, which alerts users and cuts all connections to the database. Many scripts on the Internet allow you to kill connections, but this is a simpler approach to handling this necessary step.
Take the four steps above and turn them into code -- the following is what the code should look like. Steps 1 and 2 above are accomplished in step 1 below. Step 3 below is a new step to allow users back into the database after the restore is complete.
2. RESTORE DATABASE Northwind
FROM DISK = 'c:\Northwind.BAK'
WITH MOVE 'Northwind_Data' TO 'c:\data\Northwind.mdf',
MOVE 'Northwind _Log' TO 'c:\data\Northwind _log.ldf'
3. ALTER DATABASE Northwind SET MULTI_USER
4. EXEC sp_change_users_login 'Update_One', 'myuser', 'myuser'
To make this work for your environment, substitute your database, back up the file location and list all users who will need to be re-linked.
Note: The syntax in step 4 only works for standard SQL logins. To link Windows accounts with SQL Server users, refer once again to this previous tip, under the section 'Users/Moving Logins.'
In summary, the best way to automate the restore process is to create a scheduled task, which will be kicked off as needed. The only thing scheduled task change you may need to make is the name of the backup file. For instance, if you are using Maintenance Plans to perform backups, the file name is dynamically created with the database name and datetime the backup started as the file name. A sample script has been included to help you create a scheduled task that does the four items listed above. This is a good way to get all the necessary steps in place to further automate your weekly or daily refreshes of your test or development environments.
Sample script to automate restores
BEGIN TRANSACTION DECLARE @JobID BINARY(16) DECLARE @ReturnCode INT SELECT @ReturnCode = 0 IF (SELECT COUNT(*) FROM msdb.dbo.syscategories WHERE name = N'[Uncategorized (Local)]') < 1 EXECUTE msdb.dbo.sp_add_category @name = N'[Uncategorized (Local)]'
-- Delete the job with the same name (if it exists) SELECT @JobID = job_id FROM msdb.dbo.sysjobs WHERE (name = N'Automated Database Restore') IF (@JobID IS NOT NULL) BEGIN -- Check if the job is a multi-server job IF (EXISTS (SELECT * FROM msdb.dbo.sysjobservers WHERE (job_id = @JobID) AND (server_id <> 0))) BEGIN
-- There is, so abort the script RAISERROR (N'Unable to import job ''Automated Database Restore'' since there is already a multi-server job with this name.', 16, 1) GOTO QuitWithRollback END ELSE -- Delete the [local] job EXECUTE msdb.dbo.sp_delete_job @job_name = N'Automated Database Restore' SELECT @JobID = NULL END
-- Add the job EXECUTE @ReturnCode = msdb.dbo.sp_add_job @job_id = @JobID OUTPUT , @job_name = N'Automated Database Restore', @owner_login_name = N'sa', @description = N'No description available.', @category_name = N'[Uncategorized (Local)]', @enabled = 1, @notify_level_email = 0, @notify_level_page = 0, @notify_level_netsend = 0, @notify_level_eventlog = 2, @delete_level= 0 IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback -- Add the job steps EXECUTE @ReturnCode = msdb.dbo.sp_add_jobstep @job_id = @JobID, @step_id = 1, @step_name = N'Set SINGLE_USER', @command = N'ALTER DATABASE Northwind SET SINGLE_USER WITH ROLLBACK IMMEDIATE ', @database_name = N'master', @server = N'', @database_user_name = N'', @subsystem = N'TSQL', @cmdexec_success_code = 0, @flags = 0, @retry_attempts = 0, @retry_interval = 1, @output_file_name = N'', @on_success_step_id = 0, @on_success_action = 3, @on_fail_step_id = 0, @on_fail_action = 2 IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback EXECUTE @ReturnCode = msdb.dbo.sp_add_jobstep @job_id = @JobID, @step_id = 2, @step_name = N'Restore Database', @command = N'RESTORE DATABASE Northwind FROM DISK = ''c:\backup\Northwind.BAK'' WITH MOVE ''Northwind_Data'' TO ''c:\sql\data\Northwind.mdf'',
MOVE ''Northwind_Log'' TO ''c:\sql\data\Northwind_log.ldf''', @database_name = N'master', @server = N'', @database_user_name = N'', @subsystem = N'TSQL', @cmdexec_success_code = 0, @flags = 0, @retry_attempts = 0, @retry_interval = 1, @output_file_name = N'', @on_success_step_id = 0, @on_success_action = 3, @on_fail_step_id = 0, @on_fail_action = 2 IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback EXECUTE @ReturnCode = msdb.dbo.sp_add_jobstep @job_id = @JobID, @step_id = 3, @step_name = N'Set MULTI_USER', @command = N'ALTER DATABASE Northwind SET MULTI_USER ', @database_name = N'master', @server = N'', @database_user_name = N'', @subsystem = N'TSQL', @cmdexec_success_code = 0, @flags = 0, @retry_attempts = 0, @retry_interval = 1, @output_file_name = N'', @on_success_step_id = 0, @on_success_action = 3, @on_fail_step_id = 0, @on_fail_action = 2 IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback EXECUTE @ReturnCode = msdb.dbo.sp_add_jobstep @job_id = @JobID, @step_id = 4, @step_name = N'Relink users', @command = N'EXEC sp_change_users_login ''Update_One'', ''myuser'', ''myuser''
', @database_name = N'master', @server = N'', @database_user_name = N'', @subsystem = N'TSQL', @cmdexec_success_code = 0, @flags = 0, @retry_attempts = 0, @retry_interval = 1, @output_file_name = N'', @on_success_step_id = 0, @on_success_action = 1, @on_fail_step_id = 0, @on_fail_action = 2 IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback EXECUTE @ReturnCode = msdb.dbo.sp_update_job @job_id = @JobID, @start_step_id = 1 IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback -- Add the Target Servers EXECUTE @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @JobID, @server_name = N'(local)' IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback END COMMIT TRANSACTION GOTO EndSave QuitWithRollback: IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION EndSave:
About the author: Greg Robidoux is the president and founder of Edgewood Solutions LLC, a technology services company delivering professional services and product solutions for Microsoft SQL Server. He has authored numerous articles and has delivered presentations at regional SQL Server users' groups and national SQL Server events. Robidoux, who also serves as the SearchSQLServer.com Backup and Recovery expert, welcomes your questions.
When are you planning on adopting SQL Server 2005? Edgewood Solutions would like to know. Please take the survey today.
More information from SearchSQLServer.com
- Tip: Restoring a database from another SQL Server
- Ask the Experts: Connected users preventing restore
- Selecting a SQL Server rcovery model
-