This Content Component encountered an error

Chapter 2: SQL Server

Maintenance and Troubleshooting <<previous|next>>

SQL Server Backup and Recovery

Automate SQL Server restores to refresh test environments

By Greg Robidoux, Edgewood Solutions

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:

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

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.


FROM DISK = 'c:\Northwind.BAK'
WITH MOVE 'Northwind_Data' TO 'c:\data\Northwind.mdf',
MOVE 'Northwind _Log' TO 'c:\data\Northwind _log.ldf'


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

  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)    
  -- Check if the job is a multi-server job  
              FROM    msdb.dbo.sysjobservers 
              WHERE   (job_id = @JobID) AND (server_id <> 0))) 
    -- 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  
    -- Delete the [local] job 
    EXECUTE msdb.dbo.sp_delete_job @job_name = N'Automated Database Restore' 
-- 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 

', @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 

', @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 =

  IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback 

GOTO   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 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

01 Sep 2005

Disclaimer: Our Tips Exchange is a forum for you to share technical advice and expertise with your peers and to learn from other enterprise IT professionals. TechTarget provides the infrastructure to facilitate this sharing of information. However, we cannot guarantee the accuracy or validity of the material submitted. You agree that your use of the Ask The Expert services and your reliance on any questions, answers, information or other materials received through this Web site is at your own risk.