Often times database administrators were asked to restore production database backup to UAT or development environment, sometimes it will needed to be perform on a regular bases as well. Making it an overhead as the task is manual. In order to solve that, I made some automated scripts which should help to achieve this task and might helps you to save some time.

First of all, the goal is to restore one or more databases on a daily/weekly bases, where it will take the latest backup copy of the production database. I would need to store the configuration somewhere in order to make it more dynamic, and we all wants to know the outcome of script, hence an email should be send out to all of parties who is interested.

There are three prerequisite setting required which I will not show in this post (as I assume you should already know). Those are linked server setup, database mail and enable xp_cmdshell. These three setup should be pretty straight forward. Keep in mind that all the setup needs to be perform on the destination server hence the linked server will be from the destination SQL instance to the source. One reminder is that the account used in the linked server will need to have read permission on msdb database backupset and backupmediafamily tables on the source instance.

With that out of the way, let me list out the tables I used below:

Table Name: AR_FolderConfig

Table Name: AR_DBLists

Table Name: AR_EmailRecip

The above three tables will store the configs required for the database restored, along with their restore location. Let me know in the actual script itself.

Step 1: usp_CopySrcBackup
Usage: This script will utilize the linked server to get the latest source database full backup location and file name, and use xp_cmdshell to copy the file to pre-configured local drive. It will perform the database backup copy for all of the configured databases.

Step 2: usp_RestoreDB
Usage: It will perform the actual restore of the databases, where it will kill all the existing connections to the given database (if it already exists) then perform the restore based on the config.

Step 3: usp_SetPermission
Usage: This is more of a custom place where you can set all your new permissions (as they might be different from the source - production). I also make sure that all the database got restored will change it recovery model to simple and change it owner back to sa.

Step 4: usp_RemoveSrcBackup
Usage: This script will help to perform the housekeeping, since we already restored all the databases, we will remove the backup files which we just copied and free up the space for the next run.

Step 5: usp_SendEmail
Usage: This is straight forward, just send out the email. It will take in a parameter @Result as one character (Y/N), If its a yes, it will send out a successful email to all configured recipients or a failure email otherwise.

The final step is to setup a SQL agent job and put the above scripts in them. Configure each step go to send email with failure code if the step failed. Give it a schedule and it should all goes well.

That's all there is, with the above setup, you should be able to setup an automatic database restore from a production SQL instance to any of your UAT or dev instances. It also support from one source database restore to two (or more) databases with different names if required. Given its getting it database name from a table (AR_DBLists), you can always add or remove database required to be restore. Not to mention modifying recipients is just as easy as adding or removing from the AR_EmailRecip table.

Next step will be setting it up as a self-service for all authorized users to perform ad-hoc restore, or even support of point-in-time restore to their UAT instances. I will leave that to you to enhance it!

Feel free to let me a comment, any feedback is welcome.