Using PowerShell to Restore a Database

Compresses, encrypts, secures and monitors SQL Server backups.

Moderators: JonathanWatts, Chris Auckland, eddie davis, Colin Millerchip, Brian Harris, james.billings, RBA, petey

Using PowerShell to Restore a Database

Postby SmileyLuigi » Wed Aug 22, 2012 5:33 pm

Hi,

Has anybody attempted to create a PowerShell script to restore a RedGate backed up database to a different server? The backup file is always changing and requires the file name to be dynamic. I have built such PowerShell jobs in non-RedGate database with success. I am having issues with RedGate though. Maybe all of the quotes and escape quotes are giving me issues.

This is the code I am trying to get working.
Code: Select all
$BackupFile = Get-ChildItem -Filter "*.sqb" | Sort -prop LastWriteTime | Select -last 1
$SQL = new-object System.Text.StringBuilder

[void]$SQL.Append("`"EXECUTE master..sqlbackup ")
[void]$SQL.Append("  N'-SQL ```"RESTORE DATABASE [db_dr] ")
[void]$SQL.Append("  FROM DISK = ''$BackupDir$BackupFile'' WITH RECOVERY, DISCONNECT_EXISTING, ")
[void]$SQL.Append("  MOVE ''db1'' TO ''H:\\MSSQL\\db1.mdf'', ")
[void]$SQL.Append("  MOVE ''db_cat'' TO ''H:\\MSSQL\\db_cat'', ")
[void]$SQL.Append("  MOVE ''index1'' TO ''I:\\MSSQL\\index1'', ")
[void]$SQL.Append("  MOVE ''index2'' TO ''I:\\MSSQL\\index2'', ")
[void]$SQL.Append("  MOVE ''index3'' TO ''I:\\MSSQL\\index3'', ")
[void]$SQL.Append("  MOVE ''index4'' TO ''I:\\MSSQL\\index4'', ")
[void]$SQL.Append("  MOVE ''index5'' TO ''I:\\MSSQL\\index5'', ")
[void]$SQL.Append("  MOVE ''index6'' TO ''I:\\MSSQL\\index6'', ")
[void]$SQL.Append("  MOVE ''db_log'' TO ''G:\\MSSQL\\db_log.ldf'', ")
[void]$SQL.Append("  REPLACE, ORPHAN_CHECK ")
[void]$SQL.Append("  ```"' `"")

SQLCMD -S $Instance -d $Database -E -Q $SQL


The error being thrown.
Unexpected argument. Enter '-?' for help.
At F:\\Jobs\\Restore.ps1:46 char:7
+ SQLCMD <<<< -S $Instance -d $Database -E -Q $SQL
+ CategoryInfo : NotSpecified: (Sqlcmd: 'RESTOR... '-?' for help.:String) [], RemoteException
+ FullyQualifiedErrorId : NativeCommandError


I can run this within SQL and it works successfully.
Code: Select all
EXECUTE master..sqlbackup
   '-SQL "RESTORE DATABASE [db]
       FROM DISK = ''I:\\FULL_(local)_db_20120818_030000.sqb'' WITH RECOVERY, DISCONNECT_EXISTING,
       MOVE ''db1'' TO ''H:\\MSSQL\\db1.mdf'',
      MOVE ''db_cat'' TO ''H:\\MSSQL\\db_cat'',
       MOVE ''index1'' TO ''I:\\MSSQL\\index1'',
       MOVE ''index2'' TO ''I:\\MSSQL\\index2'',
       MOVE ''index3'' TO ''I:\\MSSQL\\index3'',
       MOVE ''index4'' TO ''I:\\MSSQL\\index4'',
       MOVE ''index5'' TO ''I:\\MSSQL\\index5'',
       MOVE ''index6'' TO ''I:\\MSSQL\\index6'',
       MOVE ''db_log'' TO ''G:\\MSSQL\\db_log.ldf'',
     REPLACE, ORPHAN_CHECK
   "'
 


Any help is appreciated.
SmileyLuigi
 
Posts: 1
Joined: Wed Aug 22, 2012 3:50 pm

Postby petey » Thu Aug 23, 2012 7:28 am

Try this:

Code: Select all
$BackupFile = Get-ChildItem -Filter "*.sqb" | Sort -prop LastWriteTime | Select -last 1
$SQL = new-object System.Text.StringBuilder

[void]$SQL.Append("`"EXECUTE master..sqlbackup ")
[void]$SQL.Append("  N'-SQL `"`"RESTORE DATABASE [db_dr] ")
[void]$SQL.Append("  FROM DISK = [$BackupDir$BackupFile] WITH RECOVERY, DISCONNECT_EXISTING, ")
[void]$SQL.Append("  MOVE [db1] TO [H:\\MSSQL\\db1.mdf], ")
[void]$SQL.Append("  MOVE [db_cat] TO [H:\\MSSQL\\db_cat], ")
[void]$SQL.Append("  MOVE [index1] TO [I:\\MSSQL\\index1], ")
[void]$SQL.Append("  MOVE [index2] TO [I:\\MSSQL\\index2], ")
[void]$SQL.Append("  MOVE [index3] TO [I:\\MSSQL\\index3], ")
[void]$SQL.Append("  MOVE [index4] TO [I:\\MSSQL\\index4], ")
[void]$SQL.Append("  MOVE [index5] TO [I:\\MSSQL\\index5], ")
[void]$SQL.Append("  MOVE [index6] TO [I:\\MSSQL\\index6], ")
[void]$SQL.Append("  MOVE [db_log] TO [G:\\MSSQL\\db_log.ldf], ")
[void]$SQL.Append("  REPLACE, ORPHAN_CHECK ")
[void]$SQL.Append("  `"`"'`"")

SQLCMD -S $Instance -d $Database -E -Q $SQL

You could use the LATEST_FULL option to have SQL Backup pick up the latest full backup set for you instead e.g.

Code: Select all
$SQL = new-object System.Text.StringBuilder

[void]$SQL.Append("`"EXECUTE master..sqlbackup ")
[void]$SQL.Append("  N'-SQL `"`"RESTORE DATABASE [db_dr] ")
[void]$SQL.Append("  FROM DISK = [x:\\backups\\*.sqb] LATEST_FULL WITH RECOVERY, DISCONNECT_EXISTING, ")
[void]$SQL.Append("  MOVE [db1] TO [H:\\MSSQL\\db1.mdf], ")
[void]$SQL.Append("  MOVE [db_cat] TO [H:\\MSSQL\\db_cat], ")
[void]$SQL.Append("  MOVE [index1] TO [I:\\MSSQL\\index1], ")
[void]$SQL.Append("  MOVE [index2] TO [I:\\MSSQL\\index2], ")
[void]$SQL.Append("  MOVE [index3] TO [I:\\MSSQL\\index3], ")
[void]$SQL.Append("  MOVE [index4] TO [I:\\MSSQL\\index4], ")
[void]$SQL.Append("  MOVE [index5] TO [I:\\MSSQL\\index5], ")
[void]$SQL.Append("  MOVE [index6] TO [I:\\MSSQL\\index6], ")
[void]$SQL.Append("  MOVE [db_log] TO [G:\\MSSQL\\db_log.ldf], ")
[void]$SQL.Append("  REPLACE, ORPHAN_CHECK ")
[void]$SQL.Append("  `"`"'`"")

SQLCMD -S $Instance -d $Database -E -Q $SQL

Replace 'x:\\backups\\*.sqb' with the appropriate search pattern for your files.
Peter Yeoh
SQL Backup Consultant Developer
Associate, Yohz Software
Beyond compression - SQL Backup goodies under the hood, updated for version 7
petey
 
Posts: 2226
Joined: Sun Apr 24, 2005 12:34 pm

Postby jhboricua » Thu Oct 25, 2012 4:01 pm

Hi Smiley,

I while back I had to write a script to restore a bunch of databases. The backups were SQL Backup files. I blogged about it, you could use/adapt it to your needs. Hope it helps:

http://sysadmingrunt.blogspot.com/2011/ ... ll_23.html
jhboricua
 
Posts: 28
Joined: Tue Mar 15, 2011 3:54 pm


Return to SQL Backup 7

Who is online

Users browsing this forum: No registered users and 0 guests