Comments
2 comments
-
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. -
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
Add comment
Please sign in to leave a comment.
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.
The error being thrown.
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.