Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Scripts          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Automating a SQL Server Database Refresh


By:   |   Last Updated: 2018-05-18   |   Comments (2)   |   Related Tips: More > Restore

Problem

You need to automate refreshing a SQL Server database to refresh test or dev, test backups, etc.  In this tip we show how this can be done.

Solution

We can accomplish this with a little PowerShell, a little T-SQL code and SQL Server Agent.

For this tip we have a backup of a database named AutomatedDbRefresh.bak which we will use to automate the restore.  Let's say the backup is done on one server and we want to automate the restore do a different server.

We’ll start by copying the following code into the PowerShell editor of your choice, configure $BackupDir and $WorkDir for your environment, run it and verify the file copied from the BackupDir to the WorkDir

# begin set vars
$BackupDir = "\\JGAVIN-L\Backups\sourcedb" # where backups are stored
$WorkDir =   "C:\AutomatedDbRefresh"       # where you are copying backup to
# end set vars
 
Set-Location $WorkDir
$LatestBackupFileName = (Get-ChildItem $BackupDir\*.bak | sort LastWriteTime | select -last 1)
Copy-Item $LatestBackupFileName -Destination $WorkDir\AutomatedDbRefresh.bak -Force
			
windows powershell

I originally planned to do this all in PowerShell, but found it simpler and more reliable to use a 2-part solution. Also, I found it cleaner to hardcode edits in a few places rather than to use variables, create SQL statement variables, etc. as this would not be something requiring regular edits.

Let’s continue.

Copy the following T-SQL into SQL Server Management Studio (SSMS) and configure it to your environment. 

The code will do a few things:

  • kill any active connections for the database that we want to restore by putting database in single user mode
  • restore the backup file that was copied
  • put the restored database back to multi user mode
  • change the compatibility of the database if needed
  • change the database owner of the database
  • rename the logical database files
  • set the database to simple recovery
  • shrink the database log file
  • run a checkdb to make sure there are no issues
-- kill any connections in db
USE [master]
GO
ALTER DATABASE [targetdb] SET SINGLE_USER WITH ROLLBACK IMMEDIATE /* set target db name here */
GO
 
-- refresh db
RESTORE DATABASE [targetdb] -- set target db name here 
FROM DISK = N'C:\AutomatedDbRefresh\AutomatedDbRefresh.bak' -- fully qualified path to backup file to restore 
WITH FILE = 1,  
MOVE N'sourcedb' TO N'C:\Program Files\Microsoft SQL Server\MSSQL13.SQL2016\MSSQL\DATA\targetdb.mdf', -- logical data file name of source db and fully qualified physical file name of target db data file
MOVE N'sourcedb_log' TO N'C:\Program Files\Microsoft SQL Server\MSSQL13.SQL2016\MSSQL\DATA\targetdb_log.ldf', --   logical log file name of source db and fully qualified physical file name of target db log file 
REPLACE  
GO
 
-- return database back to multi user
USE [master]
GO
ALTER DATABASE [targetdb] SET MULTI_USER -- set target db name here
GO
 
-- set compat level if backup is from an earlier version and you want to set it to current level or you can just comment it out or delete it if not needed
-- 2017=140, 2016=130, 2014=120, 2012=110, 2008 R2=100
USE [master]
GO
ALTER DATABASE [targetdb] SET COMPATIBILITY_LEVEL = 130 -- set target db name and compat level here
GO
 
-- change owner to sa
USE [targetdb] -- set target db name here 
GO
EXEC sp_changedbowner sa
 
-- rename logical files
USE MASTER
GO
ALTER DATABASE targetdb -- set target db name here
MODIFY FILE (NAME='sourcedb', NEWNAME='targetdb')  -- change sourcedb to logical data file name of source db and targetdb to logical file name of targetdb data file
GO
ALTER DATABASE targetdb -- set target db name here
MODIFY FILE (NAME='sourcedb_log', NEWNAME='targetdb_log') -- change sourcedb_log to logical log file name of source db and targetdb_log to logical file name of targetdb log file
GO
 
-- set simple recovery and shrink log
USE [master]
GO
ALTER DATABASE [targetdb] SET RECOVERY SIMPLE WITH NO_WAIT -- set target db name here
GO
 
USE [targetdb] -- set target db name here
GO
DBCC SHRINKFILE (N'targetdb_log', 1024) -- change targetdb_log to logical file name of targetdb log file
GO
 
-- dbcc checkdb
DBCC checkdb([targetdb]) WITH NO_INFOMSGS -- set target db name here
			

These are the lines that need to be edited in the highlighted sections:

  • ALTER DATABASE [targetdb] SET SINGLE_USER WITH ROLLBACK IMMEDIATE /* set target db name here */
  • RESTORE DATABASE [targetdb] -- set target db name here
  • FROM DISK = N'C:\AutomatedDbRefresh\AutomatedDbRefresh.bak' -- fully qualified path to backup file to restore
  • MOVE N'sourcedb' TO N'C:\Program Files\Microsoft SQL Server\MSSQL13.SQL2016\MSSQL\DATA\targetdb.mdf', -- logical data file name of source db and fully qualified physical file name of target db data file
  • MOVE N'sourcedb_log' TO N'C:\Program Files\Microsoft SQL Server\MSSQL13.SQL2016\MSSQL\DATA\targetdb_log.ldf', -- logical log file name of source db and fully qualified physical file name of target db log file
  • ALTER DATABASE [targetdb] SET MULTI_USER -- set target db name here
  • ALTER DATABASE [targetdb] SET COMPATIBILITY_LEVEL = 130 -- set target db name and compat level here (2017=140, 2016=130, 2014=120, 2012=110, 2008 R2=100)
  • USE [targetdb] -- set target db name here
  • ALTER DATABASE targetdb -- set target db name here
  • MODIFY FILE (NAME='sourcedb', NEWNAME='targetdb') -- change sourcedb to logical data file name of source db and targetdb to logical file name of targetdb data file
  • ALTER DATABASE targetdb -- set target db name here
  • MODIFY FILE (NAME='sourcedb_log', NEWNAME='targetdb_log') -- change sourcedb_log to logical log file name of source db and targetdb_log to logical file name of targetdb log file
  • ALTER DATABASE [targetdb] SET RECOVERY SIMPLE WITH NO_WAIT -- set target db name here
  • USE [targetdb] -- set target db name here
  • DBCC SHRINKFILE (N'targetdb_log', 1024) -- change targetdb_log to logical file name of targetdb log file
  • DBCC checkdb([targetdb]) WITH NO_INFOMSGS -- set target db name here

Execute it and check the errorlog.

log file summary

And verify file names and options.

recovery

Create SQL Server Agent Job

Now that we’ve configured and tested everything it’s time to put it all together in a SQL Agent job.

Expand SQL Server Agent > Right click New Job…

object explorer

Give it a name and enter a description.

restore target

Goto Steps

Give it a step name, select PowerShell as the type from the dropdown and paste your edited PowerShell into the Command window and then click OK.

select a page

Click New for a new job step.  Name the job step, use Transact-SQL script (T-SQL) for type and paste your edited SQL into the Command window and click OK.

restore backup file

Goto Schedules

Name it, choose frequency and time and click OK.  Then click OK again to save the job.

select a page

Run SQL Server Agent Job

Right click on our new job and choose Start Job at Step…

start job at step

Then click Start.

copy backup file

The Start Jobs window should return Success for both Actions.

success

Verify Process Using SQL Server Error Log

Finally, verify completion by checking out the SQL Server error log.

dbcc check
Next Steps

Now that you have the basic outline of what needs to be done, see if you can this a step further and make this dynamic by passing in just a few parameters.

Here are links to tips with a wealth of information on the topics we covered in this tip:



Last Updated: 2018-05-18


get scripts

next tip button



About the author
MSSQLTips author Joe Gavin Joe Gavin is from Greater Boston. He has held many roles in IT and is currently a SQL Server Database Administrator.

View all my tips
Related Resources




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



Thursday, May 31, 2018 - 11:03:33 AM - Alen teplitsky Back To Top

 No automatic adding to an Always On group?


Thursday, May 31, 2018 - 9:39:54 AM - Richard Garrett Back To Top

 

I have something similar; however, since the purpose is to restore to a test/dev system, the permissions on those systems may be a little different.  The approach I have is to run a script from the target server.  It validates the backup file, generates a restore script and dumps out all of the existing permissions on the database.  The results of this script is another script which can then be run on the target system.  The restore is done and permissions are maintained.


Learn more about SQL Server tools