Using a PowerShell Script to automate SQL Server database restore

Recently we moved our remote ConfigMgr 2012 database to our site server. The plan was to leave the existing remote server in place to support the “ad hoc” users.

Now, how to refresh that database on a daily basis? Enter PowerShell, I’ve only recently started working with PS, so this was an interesting effort.

We are taking daily backups from our CAS and placing those on a remote server UNC. Now, it made sense that if I could automate the restore, simply grab the latest backup and restore it to the remote database server keeping the ad hoc users happy. Well maybe not happy, but at least they would leave us alone.

The script could use more error handling… basically there are 4 variables below in double quotes that need to be changed for your environment. And, in the restore database command you’ll need to change the file location if that varies from your source.

Note in testing this, I was surprised at how fast it was… the UI restore took approximately 2.5 hours to run, using the ADO connection, less than 1 hour. And, this is for a 600GB database!

I’ll write another blog on how to automate this as an SQL Server Agent task. That turned out to be very easy.

# Set variables
$SQLServer
= “SERVERNAME”
$SQLDatabase = “DBNAME”
$Dir = “\\REMOTESERVER\Backup\CMDB\”

$Latest = Get-ChildItem -Path $dir | Sort-Object LastWriteTime -Descending | Select-Object -First 1
$RestoreFile
= $Latest.Name

$FullPath = “\\REMOTESERVER\Backup\CMDB\” + $RestoreFile

$SQLConn
= New-Object System.Data.SQLClient.SQLConnection
$SQLConn.ConnectionString
= “Server=$SQLServer; Trusted_Connection=True”
$SQLConn.Open()
$SQLCmd = New-Object System.Data.SQLClient.SQLCommand
$SQLcmd
= $SQLconn.CreateCommand()
$sqlcmd.commandtimeout=0
$SQLcmd.CommandText
=“ALTER DATABASE $SQLDatabase
SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
RESTORE DATABASE $SQLDatabase
FROM DISK = ‘$FullPath’
WITH REPLACE,
MOVE ‘DBName’ TO ‘I:\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\DBame.mdf’,
MOVE ‘DBName_log’ TO ‘I:\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\DBName_log.ldf’”

$SQLcmd.Executenonquery() | out-Null

$SQLcmd.CommandText=“ALTER DATABASE $SQLDatabase SET MULTI_USER;”
$SQLcmd.Executenonquery() | out-Null

$SQLConn.Close()
Related: Gather SQL Server Instance Information with PowerShell

email

Written by , Posted .