Competition: What’s your favorite Redgate tool? Enter now.

Using PowerShell to Restore a Database

SmileyLuigiSmileyLuigi Posts: 2
edited October 25, 2012 11:01AM in SQL Backup Previous Versions
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.
$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.
EXECUTE master..sqlbackup 
	'-SQL "RESTORE DATABASE &#91;db&#93; 
       FROM DISK = ''I:\FULL_&#40;local&#41;_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.

Comments

  • peteypetey Posts: 2,358 New member
    Try this:
    $BackupFile = Get-ChildItem -Filter "*.sqb" | Sort -prop LastWriteTime | Select -last 1 
    $SQL = new-object System.Text.StringBuilder 
    
    &#91;void&#93;$SQL.Append&#40;"`"EXECUTE master..sqlbackup "&#41; 
    &#91;void&#93;$SQL.Append&#40;"  N'-SQL `"`"RESTORE DATABASE &#91;db_dr&#93; "&#41; 
    &#91;void&#93;$SQL.Append&#40;"  FROM DISK = &#91;$BackupDir$BackupFile&#93; WITH RECOVERY, DISCONNECT_EXISTING, "&#41; 
    &#91;void&#93;$SQL.Append&#40;"  MOVE &#91;db1&#93; TO &#91;H:\MSSQL\db1.mdf&#93;, "&#41; 
    &#91;void&#93;$SQL.Append&#40;"  MOVE &#91;db_cat&#93; TO &#91;H:\MSSQL\db_cat&#93;, "&#41; 
    &#91;void&#93;$SQL.Append&#40;"  MOVE &#91;index1&#93; TO &#91;I:\MSSQL\index1&#93;, "&#41; 
    &#91;void&#93;$SQL.Append&#40;"  MOVE &#91;index2&#93; TO &#91;I:\MSSQL\index2&#93;, "&#41; 
    &#91;void&#93;$SQL.Append&#40;"  MOVE &#91;index3&#93; TO &#91;I:\MSSQL\index3&#93;, "&#41; 
    &#91;void&#93;$SQL.Append&#40;"  MOVE &#91;index4&#93; TO &#91;I:\MSSQL\index4&#93;, "&#41; 
    &#91;void&#93;$SQL.Append&#40;"  MOVE &#91;index5&#93; TO &#91;I:\MSSQL\index5&#93;, "&#41; 
    &#91;void&#93;$SQL.Append&#40;"  MOVE &#91;index6&#93; TO &#91;I:\MSSQL\index6&#93;, "&#41; 
    &#91;void&#93;$SQL.Append&#40;"  MOVE &#91;db_log&#93; TO &#91;G:\MSSQL\db_log.ldf&#93;, "&#41; 
    &#91;void&#93;$SQL.Append&#40;"  REPLACE, ORPHAN_CHECK "&#41; 
    &#91;void&#93;$SQL.Append&#40;"  `"`"'`""&#41; 
    
    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.
    $SQL = new-object System.Text.StringBuilder 
    
    &#91;void&#93;$SQL.Append&#40;"`"EXECUTE master..sqlbackup "&#41; 
    &#91;void&#93;$SQL.Append&#40;"  N'-SQL `"`"RESTORE DATABASE &#91;db_dr&#93; "&#41; 
    &#91;void&#93;$SQL.Append&#40;"  FROM DISK = &#91;x:\backups\*.sqb&#93; LATEST_FULL WITH RECOVERY, DISCONNECT_EXISTING, "&#41; 
    &#91;void&#93;$SQL.Append&#40;"  MOVE &#91;db1&#93; TO &#91;H:\MSSQL\db1.mdf&#93;, "&#41; 
    &#91;void&#93;$SQL.Append&#40;"  MOVE &#91;db_cat&#93; TO &#91;H:\MSSQL\db_cat&#93;, "&#41; 
    &#91;void&#93;$SQL.Append&#40;"  MOVE &#91;index1&#93; TO &#91;I:\MSSQL\index1&#93;, "&#41; 
    &#91;void&#93;$SQL.Append&#40;"  MOVE &#91;index2&#93; TO &#91;I:\MSSQL\index2&#93;, "&#41; 
    &#91;void&#93;$SQL.Append&#40;"  MOVE &#91;index3&#93; TO &#91;I:\MSSQL\index3&#93;, "&#41; 
    &#91;void&#93;$SQL.Append&#40;"  MOVE &#91;index4&#93; TO &#91;I:\MSSQL\index4&#93;, "&#41; 
    &#91;void&#93;$SQL.Append&#40;"  MOVE &#91;index5&#93; TO &#91;I:\MSSQL\index5&#93;, "&#41; 
    &#91;void&#93;$SQL.Append&#40;"  MOVE &#91;index6&#93; TO &#91;I:\MSSQL\index6&#93;, "&#41; 
    &#91;void&#93;$SQL.Append&#40;"  MOVE &#91;db_log&#93; TO &#91;G:\MSSQL\db_log.ldf&#93;, "&#41; 
    &#91;void&#93;$SQL.Append&#40;"  REPLACE, ORPHAN_CHECK "&#41; 
    &#91;void&#93;$SQL.Append&#40;"  `"`"'`""&#41; 
    
    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 8
  • 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
Sign In or Register to comment.