Recently I had a situation where I had to restore several full backups of multiple databases on a SQL Server instance daily. The databases would already be there and a simple restore with replace option would suffice. As I’m a fan of Powershell I wanted to restore a database using Powershell.
As lazy as I am I don’t want to make a script for each of the databases, I just want to supply a few parameters to a script and that the script does the rest for me.
I wanted the script to do the following:
- Get the latest backup file from a directory
- Restore the database with replace option
- Be able to use the script in a SQL Server Agent job
The script would have to have the following parameters to do everything:
- Directory: Directory to search through to get the latest file.
- SQL Server instance: Which instance the server needed to restore to
- Database: The database to restore to
- Filter: Which extension to search for like “BAK”
Eventually the script looks like this
Summary: Restores databases in SQL Server
SQL Server Versions: 2005 onwards
Written by Sander Stad, SQLStad.nl
(c) 2015, SQLStad.nl. All rights reserved.
For more scripts and sample code, check out http://www.SQLStad.nl
You may alter this code for your own *non-commercial* purposes (e.g. in a
for-sale commercial tool). Use in your own environment is encouraged.
You may republish altered code as long as you include this copyright and
give due credit, but you must obtain prior permission before blogging
THIS CODE AND INFORMATION ARE PROVIDED "AS IS" WITHOUT WARRANTY OF
ANY KIND, EITHER EXPRESSED OR IMPLIED, INCLUDING BUT NOT LIMITED
TO THE IMPLIED WARRANTIES OF MERCHANTABILITY AND/OR FITNESS FOR A
$directory = $d
$serverInstance = $s
$database = $db
$filter = $f
# Get latest file from the directory
$latestBackup = Get-ChildItem -Path $directory -File -Recurse -Filter $filter | Sort-Object LastAccessTime -Descending | Select-Object -First 1
# Setup the full path
Restore-SqlDatabase -ServerInstance $serverInstance -Database $database -BackupFile $latestbackup.FullName -ReplaceDatabase
#Write-Error ("Restore of database failed." + $_)
#Write-Host -ForegroundColor Red "Directory couldn't be found!"
The catches are commented due the fact that the SQL Server job can’t cope with these kind of error messages. The script assumes that the database is already present. In a later version I will make it possible to adjust this accordingly but for now the functionality is just to execute a replace.
To make the job go to SQL Server and create a new job and job step and fill it in like below:
Make sure the field Type is set on “Operating system (CmdExec)” and that the command is set up like this:
powershell "[folder to script]\RestoreDatabase.ps1 -d '[folder]' -s '[instance]' -db '[database]' -f '*.bak'"
- -d: Directory to search
- -s: SQL Server instance
- -db: Database to replace
- -f: Filter
I used the “*.bak” filter to get all the bak-files but you could use a different extension if needed as long as it is for a full backup.
Replace the values between the brackets and give the job a spin.