Red Gate forums :: View topic - Using PowerShell to Restore a Database
Return to www.red-gate.com RSS Feed Available

Search  | Usergroups |  Profile |  Messages |  Log in  Register 
Go to product documentation
SQL Backup 7
SQL Backup 7 forum

Using PowerShell to Restore a Database

Search in SQL Backup 7 forum
Post new topic   Reply to topic
Jump to:  
Author Message
SmileyLuigi



Joined: 22 Aug 2012
Posts: 1

PostPosted: Wed Aug 22, 2012 5:33 pm    Post subject: Using PowerShell to Restore a Database Reply with quote

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
View user's profile Send private message
petey



Joined: 24 Apr 2005
Posts: 2301

PostPosted: Thu Aug 23, 2012 7:28 am    Post subject: Reply with quote

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
View user's profile Send private message Send e-mail
jhboricua



Joined: 15 Mar 2011
Posts: 28

PostPosted: Thu Oct 25, 2012 4:01 pm    Post subject: Reply with quote

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/12/multiple-sql-restores-with-powershell_23.html
Back to top
View user's profile Send private message
Display posts from previous:   
Reply to topic All times are GMT + 1 Hour
Page 1 of 1

 
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


Powered by phpBB © 2001, 2005 phpBB Group