| Author |
Message |
SmileyLuigi
Joined: 22 Aug 2012
Posts: 1
|
 Posted: Wed Aug 22, 2012 5:33 pm Post subject: Using PowerShell to Restore a Database |
 |
|
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: |
$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.
| Quote: |
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: |
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. |
|
| Back to top |
|
 |
petey
Joined: 24 Apr 2005
Posts: 2215
|
| Posted: Thu Aug 23, 2012 7:28 am Post subject: |
|
|
Try this:
| Code: |
$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: |
$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 |
|
| Back to top |
|
|
jhboricua
Joined: 15 Mar 2011
Posts: 27
|
|
| Back to top |
|
|
|
