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.