Automate SQL Server restores to refresh test environments

Why and how to automate SQL Server restores

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:

1. Back up the production database: You should already have a scheduled process that creates daily full backups, so use the backup files that are already in place.

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.

1. ALTER DATABASE Northwind SET SINGLE_USER WITH ROLLBACK IMMEDIATE

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