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()



Advertisements
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:

WordPress.com Logo

You are commenting using your WordPress.com 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