SQL Powershell Script To Restore multiple databases at One Go

• Raushan Srivastava

  Old Hand

  Points: 391

  More actions

  September 6, 2013 at 3:43 am

  #395430

  Comments posted to this topic are about the item SQL Powershell Script To Restore multiple databases at One Go

• thirunamsbi

  Newbie

  Points: 9

  More actions

  February 9, 2014 at 11:45 pm

  #1687433

  This is not workin in the restore part.please share your views

  $DBDropFolder = "\\share\BAK\"

  $destinationFolder = "E:\temp\"

  $destinationServerInstance = "."

  $destinationDbfileFolder = "E:\Data\"

  function CopyBakFileFromServer($sourceFolder, $destinationFolder, $patternFull, $patternDiff) {

  if (-not (test-Path $destinationFolder)) { New-Item $destinationFolder -type directory }

  write-host "Copy file from $sourceFolder to $destinationFolder with pattern: full - $patternFull; diff - $patternDiff"

  $files = @(gci $DBDropFolder -Filter $patternFull | Sort LastWriteTime -Descending)

  if (-not ($files -eq $null) -and $files.Count -gt 0) {

  $file = $files[0]

  $fileName = $file.Name

  $datetimeFull = $file.LastWriteTime

  write-host "file found: $fileName created $datetimeFull, copying ..."

  Copy-Item -path $file.FullName -destination "$destinationFolder" -force

  write-host "file found: $fileName created $datetimeFull, copied ..."

  $filesDiff = @(gci $DBDropFolder -Filter $patternDiff | Sort LastWriteTime -Descending)

  if (-not ($filesDiff -eq $null) -and $filesDiff.Count -gt 0) {

  $fileDiff = $filesDiff[0]

  $fileNameDiff = $fileDiff.Name

  $datetimeDiff = $fileDiff.LastWriteTime

  if ($datetimeDiff -gt $datetimeFull) {

  write-host "file found: $fileNameDiff created $datetimeDiff, copying ..."

  Copy-Item -path $fileDiff.FullName -destination "$destinationFolder" -force

  write-host "file found: $fileNameDiff created $datetimeDiff, copied ..."

  } else {

  write-Host "file found: $fileNameDiff created $datetimeDiff, which is older than the full bak, skipped copying"

  }

  }

  }

  }

  function RestoreDatabaseFromFile ($serverInstance, $bakFile, $dbFileFolder, $dbName) {

  if (-not (test-Path $dbFileFolder)) { New-Item $dbFileFolder -type directory }

  if (test-Path $bakFile) {

  $fileMode = "1"

  if ($bakFile.Contains("_DIFF.bak")) { $fileMode = "2" }

  write-host "restoring $dbName ==> $serverInstance, mode $fileMode ..."

  $sqlRestoreDB = "use Master;ALTER DATABASE $dbName SET SINGLE_USER WITH ROLLBACK IMMEDIATE; RESTORE DATABASE [$dbName] FILE = N'$dbName' FROM DISK = N'$bakFile' WITH FILE = $fileMode, $SQLConn = New-Object System.Data.SQLClient.SQLConnection

  $SQLConn.ConnectionString = “Server=$SQLServer; Trusted_Connection=True”

  $SQLConn.Open()

  $SQLCmd = New-Object System.Data.SQLClient.SQLCommand

  $SQLcmd = $SQLconn.CreateCommand()

  $sqlcmd.commandtimeout=0

  $SQLcmd.CommandText=“Restore filelistonly from disk='$dbFileFolder\$bakfile'"

  $rdr=$SQLcmd.ExecuteReader()

  $files = $null

  while($rdr.Read())

  {

  $test = $rdr["Type"].ToString()

  if($test -eq 'D')

  {

  $files = $files + "MOVE N'$dbName' TO N'$dbFileFolder\$dbName.mdf"

  }

  if($test -eq 'L')

  {

  $files = $files + "MOVE N'$dbName'"+"_log"+" TO N'$dbFileFolder\$dbName.ldf"

  }

  }, NOUNLOAD, REPLACE, STATS = 10; ALTER DATABASE $dbName SET MULTI_USER;"

  Invoke-Sqlcmd -Query $sqlRestoreDB -ServerInstance $serverInstance

  } else {

  write-host "$bakFile was not found"

  }

  }

  CopyBakFileFromServer $DBDropFolder $destinationFolder DW.bak DW_DIFF.bak

  CopyBakFileFromServer $DBDropFolder $destinationFolder Staging.bak Staging_DIFF.bak

  CopyBakFileFromServer $DBDropFolder $destinationFolder Data.bak Data_DIFF.bak

  RestoreDatabaseFromFile $destinationServerInstance DW.bak $destinationDbfileFolder DW

  RestoreDatabaseFromFile $destinationServerInstance DW_DIFF.bak $destinationDbfileFolder DW

  RestoreDatabaseFromFile $destinationServerInstance Staging.bak $destinationDbfileFolder Staging

  RestoreDatabaseFromFile $destinationServerInstance Staging_DIFF.bak $destinationDbfileFolder Staging

  RestoreDatabaseFromFile $destinationServerInstance Data.bak $destinationDbfileFolder Data

  RestoreDatabaseFromFile $destinationServerInstance Data_DIFF.bak $destinationDbfileFolder Data

• Raushan Srivastava

  Old Hand

  Points: 391

  More actions

  February 10, 2014 at 10:13 am

  #1687612

  can you please share the error..

• SQLRNNR

  SSC Guru

  Points: 281334

  More actions

  February 14, 2014 at 11:49 am

  #1689163

  Thanks for sharing this script

  Jason...AKA CirqueDeSQLeil
  _______________________________________________
  I have given a name to my pain...MCM SQL Server, MVP
  SQL RNNR
  Posting Performance Based Questions - Gail Shaw[/url]
  Learn Extended Events