Using PowerShell to Restore a Database

SmileyLuigi

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 [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.

petey

Try this:

$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.

$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.

jhboricua

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