Tara Kizer Blog

Tara Kizer

How to refresh a SQL Server database automatically

As a DBA, we are often asked to refresh a database, which means to overwrite an existing database using a different database's backup.  If you are rarely asked to do this, you may decide to do it manually.  If you are asked to do this on a regular and perhaps scheduled basis, then you'd want to automate it.  Developers often want to write and debug code against a copy of the production database, so it makes sense to refresh their databases on a daily or weekly basis. 

If you were to do the refresh manually, you would probably follow these steps more or less:

  • Copy the backup file
  • Restore the database
  • Unorphan the accounts if the SIDs aren't in sync
  • Provide db_owner or similar permissions to the development staff
  • Run a script for anything else not handled above

Depending upon the size of the backup, you may want to compress it after the first step from above using tools like WinRAR, PKZIP, or WinZip.  These three tools have command line versions, so it can be easily added to the refresh process.

To automate this process, we need to write code that does the above steps and then to schedule that code to run via the SQL Server Agent.  To make this easier to understand, I will be referring to the source server as Server1 and the destination server as Server2.  An example of a source server is production, and an example of a destination server is development.

Here's how I have the refresh automated in a couple of my environments:

  • On Server1, a job exists that compresses the latest full backup
  • On Server2, there's a job that performs the following steps:
    • Copies the compressed file to a local drive
    • Decompresses the file to make the full backup available
    • Kills all sessions to the database that is about to be restored
    • Restores the database
    • Sets the recovery model to Simple
    • Grants db_owner privileges to the developers

You'll need to schedule the job on Server2 so that it doesn't run until the job on Server1 completes.  I just watch how long the job on Server1 takes and then schedule the job on Server2 to run a few hours afterwards. 

You can download all of my code here.  The file for Server1 is named RefreshServer1.sql, and the file for Server2 is named RefreshServer2.sql.  I perform all of the steps on Server2 in a single stored procedure, which means I only have one job step. 

In the code, you'll notice that I am using rar.exe and CPAU.exe.  Your refresh process may not need these. 

We use WinRAR as our compression utility.  WinRAR's command line version is rar.exe.  CPAU.exe is used to provide credentials to authenticate to Server1 as Server1 and Server2 are in different domains in my environment, and unfortunately we don't have a trust relationship setup between the two domains.  For more information on CPAU, see this blog post.  In the comments, I included the restore command if you are using Quest's LiteSpeed.  We use it for our backups, so we use their xp_restore_database extended stored procedure to restore them.

I don't have any code to unorphan the accounts in my refresh process as I ensure that the SIDs are identical between the two environments.  For more information on that, see this blog post.  In case you need a script to unorphan the accounts, I've included it in the download as UnorphanAccounts.sql.

You can easily rewrite the code to handle multiple databases that need to be refreshed on the same Server1 and Server2.  In fact, that's what I am doing in my environments as some of the applications that I support use multiple databases.  To handle multiple databases on the same instances, you'll need to loop through them in isp_RefreshServer1.  My looping code is in there, but it is commented out.  You'll also need to perform multiple restores in isp_RefreshServer2.  I left the code for multiple databases in that stored procedure too, but I commented it out. 

If you have a lot of databases to refresh and they all follow the same code, I would suggest parameterizing the stored procedures rather than hard-coding the various things. 

Legacy Comments

Remote DBA
re: How to refresh a SQL Server database automatically
nice solution. thanks

Ed Swiedler
re: How to refresh a SQL Server database automatically
I like the solution, except for scheduling the process on Server2 to start a couple of hours after Server1 finishes. There are many reasons that the process on Server1 could take more or less time and depending on that time to remain under 2 hours longer than expected may be asking for trouble.

I would have a look at Service Broker as it is very simple to set-up for just this scenario.

re: How to refresh a SQL Server database automatically

I agree that your way is a better approach, however due to the current network config, we can't do that in my environment.

Matt Gauch
re: How to refresh a SQL Server database automatically
I agree that Service Broker is a great approach. But it's just as simple to do in SQL Server alone, assuming you can configure a linked server on both source and destination servers to point to the other server - the final step in your backup job on the source (server1) can kick off the job on the destination (Server2):

EXEC SERVER2.msdb.dbo.sp_start_job 'SERVER2_Refresh_datbase_job'

This way, there is NO lag between the backup job completing on Server1 and the file copy/restore on Server2.

re: How to refresh a SQL Server database automatically
I like the solution, but i would like to know how this will be suitable for Larger DB(150GB) refresh is there any alternative approch?

re: How to refresh a SQL Server database automatically
Yes it's suitable for large databases, just make sure you compress the backup file to make the copy as fast as possible. Either compress the backups via Quest's LiteSpeed product or Red Gate's SQL Backup product, or compress the backup file using WinZip, Pkzip, WinRAR, or similar.

re: How to refresh a SQL Server database automatically
I've used SSIS in the past to do things like this. If your DB isn't too big, you can very simply use the "Transfer Database" task to do this. Takes about 2 minutes to set this up in SSIS, then you just need to import the package into SQL server and then set up a scheduled job to run it at a convenient time.

If it needs zipping, then you can use other tasks in SSIS, including the scripting task, which you can use to zip up the backup. This also gives you the option of FTPing the backup in the case you don't have shared-drive access.

re: How to refresh a SQL Server database automatically
In my opinion, SSIS is overcomplicates a task like this. With backup/restore, I can ensure that the databases are the same.