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
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.
And verify file names and options.
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…
Give it a name and enter a description.
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.
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.
Goto Schedules
Name it, choose frequency and time and click OK. Then click OK again to save the job.
Run SQL Server Agent Job
Right click on our new job and choose Start Job at Step…
Then click Start.
The Start Jobs window should return Success for both Actions.
Verify Process Using SQL Server Error Log
Finally, verify completion by checking out the SQL Server error log.
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:
