Restoring Live into Test
Last updated
Last updated
Quite a common requirement is to move Live Script and (other data, e.g. Users & History) into a Test (or Training) environment. Assuming you have a Test database (and a Test instance of Inter•View) installed, this is a relatively simple task which requires an SQL Backup of the Live database and a restore of this backup over a Test database. This can be done as part of a scheduled Maintenance Plan (for general information and useful links describing about Database backups and Maintenance Plans, ). An example below describes how to restore KF_Example_40_Live over KF_Example_40_Test.
This example takes a simple approach of running a backup then a restore; this could be improved by pointing at the most recent live backup media and simply running the restore from there.
Start SQL Server Business Intelligence Development Studio (BIDS)
Start a new project using the 'Integration Services Project' Template and provide a suitable name:
Unless already visible, display the Toolbox pane and drag the Back Up Database Task from the Maintenance Plan Tasks onto the Workflow design area:
Right click the Back Up Database Task and select Edit. Fill in the contents as per the following example, adjusting paths to suit:
As there is no equivalent of the Backup Task in the Toolbox, you will need to craft a T-SQL command to perform the restore. Drag and drop an Execute T-SQL Statement Task onto the Workflow design pane and connect to the previous backup Task
Right click this Task and enter a command as below,adjusting names and paths to suit:
When you are happy all names and paths are correct try debug the job by hitting F5 (you can run the job without debugging by hitting CTRL+F5). If both steps complete successfully, the tasks should turn green. If either task has failed, the reason will be displayed in the Progress tab and the task item(s) will appear in red:
Once the Tasks are running successfully, you may want to schedule these to run daily, say, at 2am every morning. This is done within SQL Enterprise Manager by adding a New Job under the SQL Server Agent. When you define a Step, select the package just created (I have assumed you saved the package to disk):
Specify a schedule, in this example the job will run at 2am every day:
It should be noted that if a restore takes place on a different SQL Server to where the backup was run, it will be necessary to run the following stored procedure on the newly restored database before it will become available:
