Using PowerShell to Restore a Database

Comments

• petey Redgate › Posts: 2,358 New member

  August 23, 2012 2:28AM

  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.

  Peter Yeoh
  SQL Backup Consultant Developer
  Associate, Yohz Software
  Beyond compression - SQL Backup goodies under the hood, updated for version 8

  0 · Share on Twitter
• jhboricua Posts: 41

  October 25, 2012 11:01AM

  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

  0 · Share on Twitter