How to automatically refresh a SQL Server database

As a DBA, I am often asked to automate tasks. In addition to this, I generally work with a lot of developers that need to test with data refreshing on a regular basis. A lot of the time, I am asked to either stand up a new test environment or overwrite/refresh an existing one. Now, these tasks are not difficult to complete, but why not just automate them into a SQL Agent job and just run it whenever you need it or schedule it and let the job do all the work? If you have read some of my previous articles (What is causing database slowdowns or Simple SQL Server automated user auditing), you will know I am a big proponent of automating repeatable tasks. The best part of this is if you have multiple clients like I do is that you can script out the job, change the variables and use it over and over again. I cannot tell you how many times I say “I have a job for that” when a client asks me to automate a task.

Real Life Situations

Recently a new client asked me to do such a task. The problem that I needed to solve was to restore the Test/Development Database from a copy of the Production Database every evening so that the developers would have fresh data to work with each morning. They wanted to use the latest full back up which ran the night before at midnight. So, in the end, the data was really never older than a day. For this very simple task, I decided to compose a SQL Script and put it into a scheduled SQL Agent Job. For this to work, you will need at least SQL Server Standard Edition as the agent is not available in SQL Express Edition. I built this on SQL Server 2014 however it should run on versions beginning from 2008 – current.

The SQL Script below will navigate into a source directory on the file system where the backups are stored and restores the latest full back up to a destination database that is a variable within the script. I have added <CHANGE HERE> to indicate where the variables are that need updating. Seriously, it cannot be any simpler than this!

What you will need

SQL Server Standard Edition or higher
SQL Server Management Studio
New Query Window
The logical Name of the source database
The database file name and file locations of the source database files
The source location of the backup file
SQL Server Agent
DBMail enabled and configured

The Script

The script is fairly simple. You will need to change the script below with your variables for the file location. First, it looks into the backup folder for the latest backup file. Then it creates a temporary table to house the file names. It will then select the latest backup file to use from the temporary table. Finally, it will execute the restore using the latest backup file listed in the temporary table and take it from the backup file system folder. In addition to this, I have created an audit log to capture job activity each time the job runs which I generally use if the job fails to help troubleshoot. I have also enabled notifications to email me if the job fails. Both of these last 2 items are just good practice and I usually do these on every SQL Agent Job I create.

Copy and paste this script into a new query window and change the variables marked /*<CHANGE HERE>*/ below.

–get the last backup file name and path Drop table #BackUpFilesAvailable Declare @LatestBackupFile varChar(255) Declare @BackUpFolder varchar(255) set @LatestBackupFile = null set @BackUpFolder = ‘/*<CHANGE HERE>*/ C:\Program Files\Microsoft SQL Server\MSSQL12.SQLEXPRESS2014\MSSQL\Backup\’ create table #BackUpFilesAvailable ( FileName varchar(255), levels int, files int) –get all the files and folders in the backup folder and put them in temporary table insert into #BackUpFilesAvailable exec xp_dirtree @BackUpFolder,0,1 –get the latest backup file name select top 1 @LatestBackupFile = @BackUpFolder + FileName from #BackUpFilesAvailable where Filename like ‘%.bak’ order by filename desc –execute the restore RESTORE DATABASE /*<CHANGE HERE>*/ [TestDevelopmentDB] FROM DISK = @LatestBackupFile WITH /*<CHANGE HERE>*/ MOVE N’ProductionDB’ TO N’C:\Program Files\Microsoft SQL Server\MSSQL12.SQLEXPRESS2014\MSSQL\DATA\TestDevelopmentDB.mdf’, MOVE N’ProductionDB_log’ TO N’C:\Program Files\Microsoft SQL Server\MSSQL12.SQLEXPRESS2014\MSSQL\DATA\TestDevelopmentDB_log.ldf’, REPLACE

Run the Script

If all variables are changed correctly, you will see a confirmation in the messages window below the query window.

The Agent Job

Now here’s the best part! Automate the job by putting it into a SQL Server Agent job and schedule it to run at a specific time. To add it to an agent job, open SQL Server Management Studio and expand the left branch down to SQL Server Agent and expand it. Note the SQL Agent must be running or this branch will be disabled.

Then right-click on Jobs and select New Job and give the job a name. Note that the owner column will default to you the logged in user. I usually change this to SA or a user with SA but that is up to you.

Click on Steps in the left menu.

Click on the New button in the lower portion of the screen. When the New screen opens, give the step a name in Step Name, paste the modified script from above then click on Parse to test the script. If no errors, then click Advanced in the left menu. Change On Success Action to Quit the job reporting success.

Auditing Job Success / Failure

One thing I like to do on all jobs I create is to create an audit log so if there are failures or issues they will be captured in a text file on the file system. So complete the Output file section as I have below.

In addition to this audit history, I also like to add an email notification on job failure so that I am notified if such occurs. To do so, click OK on the screen above and then click on Notifications in the left menu.

Check e-mail and add the recipient. Please note that you must have dbMail enabled and configured in order for this to work as well as operators setup on your server. I’m not going to go through how to configure dbMail or adding operators as they are already readily available from other sources. Once notification is completed, click on Schedules in the left menu then click new.

Add your run schedule for the restore job. For my example here, I have scheduled a daily job that runs at 4:30 AM with no end date.

Conclusion

This is a quick and easy way to restore a database via a script that can be automated. Again, to automate this, I put a script into a SQL Agent Job and scheduled it to run on a daily basis at a specific time. I also created an audit log in the event of failures.

Kimberly Killian

With over 20 years of total IT experience, Kim has a very diverse background. She has worked in positions as a programmer, IT architect, DB Engineer, DBA, ETL developer as well as Senior Level Technical Consultant.

For the last 15 years has focused her technology experience within the world of database solutions and specifically with MS SQL Server. She has published numerous articles spotlighting SQL Server and is an expert in monitoring and reporting on SQL Server performance.

View all posts by Kimberly Killian

Latest posts by Kimberly Killian (see all)

How to automatically refresh a SQL Server database - October 4, 2017
SQL Server Index vs Statistics a consultants woes…or rants - December 29, 2016
How to Split a Comma Separated Value (CSV) file into SQL Server Columns - December 29, 2016