Automate a restore of a production database to a development environment

In this example the production database is backed up once a day to a shared file system. So to automate the process I have created a SSIS package which queries the production database and finds out the name and file-path of the last backup. It then restores the last backup to the development instance. This way we do not impact the production environment.



The SSIS Package  has two SQL tasks. The first will query the production backup and find out the name and path of the last backup to a shared file system. The 2nd task will then take the backup and restore it to the development instance.



Create connection managers

Before you start, create two new connection managers: one for the source production database, and the other for the destination development instance.



Create global variable

Create a variable which will be used to store the output of the above query (the location and filename of the last backup). The variable needs to have a scope of the whole package and a String data type.



Task 1 – Get last backup



The below statement queries the backupmediafamily and backupset tables. It extracts the filename and path of the last full backup.

SQL Statement

SELECT

physical_device_name

FROM msdb.dbo.backupmediafamily

WHERE media_set_id =(

SELECT TOP 1 media_set_id

FROM msdb.dbo.backupset

WHERE database_name='DATABASE_NAME'

AND type='D'

ORDER BY backup_start_date DESC)

In the result set tab, change the Result Name to 0 (zero, i.e. the first returned value) and the Variable Name should be your new variable name prefixed with User:: (I.e. User::FILEPATH).

 

Task 2 – Restore backup to Development

 
 

SQL Statement

restore database DATABASE_NAME from disk = ?

with move 'logical_file_name_in_backup' to 'operating_system_file_path.mdf',

move 'logical_log_name_in_backup' to 'operating_system_file_path.ldf'

, replace

In the parameter mapping tab the variable name is again the FILEPATH variable we have used in task 1. The direction is “Input” and the data type NVARCHAR.

Please note that the names that you can use as parameter names in the mappings between variables and parameters also vary by connection manager type.

As I have used the OLE DB connection manager type, this requires that we use the numeric value of a 0-based ordinal as the parameter name.

Once you have tested the package you can upload it to the server and run it using a SQL Agent job.

After you restore the database you may need to fix the login, users and permissions. I chose to just add read / write privileges to a number of specific users as there is a significant difference between the users in production and this development environment. You may choose to script out the accounts using sp_help_revlogin or just use the sp_change_users_login procedure to fix the orphaned accounts.

The package is very basic and could be extended to include some basic error handling, etc.

Links:

Parameters and Return Codes inthe Execute SQL Task

 

Restoring aBackup to a different Server - User Permissions