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:
