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
$SQLDatabase = "DBNAME"
$Dir = "\\REMOTESERVER\Backup\CMDB\"

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

$FullPath = "\\REMOTESERVER\Backup\CMDB\" + $RestoreFile

= New-Object System.Data.SQLClient.SQLConnection
= "Server=$SQLServer; Trusted_Connection=True"
$SQLCmd = New-Object System.Data.SQLClient.SQLCommand   
= $SQLconn.CreateCommand()   
="ALTER DATABASE $SQLDatabase                       
RESTORE DATABASE $SQLDatabase                       
FROM DISK = '$FullPath'                       
    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


This entry was posted in ConfigMgr, PowerShell, SQL Server. Bookmark the permalink.

One Response to Using a PowerShell Script to automate SQL Server database restore

  1. Pingback: Running Powershell as an SQL Server Agent task | Steve Thompson [MVP]

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s