Restore with relocate database files location in powershell

• rajadadi

  Old Hand

  Points: 336

  More actions

  August 4, 2014 at 8:52 pm

  #310540

  Please help me to modify this script as relocate database files location means like with move ... this will help me

  write-host "

  Restoring Databases"

  foreach ($database in $databases){

  write-host $database

  invoke-sqlcmd -Query "USE [master]

  ALTER DATABASE [$database] SET SINGLE_USER WITH ROLLBACK IMMEDIATE

  RESTORE DATABASE [$database] FROM DISK = N'H:\KMTemp\$database.bak' WITH FILE = 1, NOUNLOAD, REPLACE, STATS = 5

  ALTER DATABASE [$database] SET MULTI_USER

  GO

  " -ServerInstance $destinationserver -QueryTimeout 10000}

• Gary Varga

  SSC Guru

  Points: 82166

  More actions

  August 5, 2014 at 3:04 am

  #1735036

  I am unsure of what you are asking. What is your problem? Specifically?

  Gaz

  -- Stop your grinnin' and drop your linen...they're everywhere!!!

• Leonard Rutkowski

  SSCrazy

  Points: 2668

  More actions

  August 25, 2014 at 12:01 pm

  #1740781

  Not sure what you are looking for, but I use the below code to generate a SQL script. Could be modified to do interactive restore. We also use Litespeed, but modifying it to use native backup should be fairly straight forward. Most of our databases have one data and one log, so the code to generate multiple move statements is commented out.

  clear

  #set up for smo

  [System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO') | out-null

  $SaveDir = "C:\Temp\"

  $SaveFile = $SaveDir + "ScriptName_Restore.sql"

  $RestoreFromDir = "C:\MSSQL\Backups"

  $RestoreToDir = "C:\MSSQL\Data"

  $RestoreLogToDir = "C:\MSSQL\Logs"

  $SkipDBs = "master","msdb","model","tempdb","distribution"

  IF(Test-Path $SaveFile)

  {

  Remove-Item $SaveFile

  }

  #Generate scripts, from Instance

  $instance = "InstanceName"

  $srv = New-Object ('Microsoft.SqlServer.Management.Smo.Server') $instance

  # $srv.ConnectionContext.LoginSecure = $False # set to true for Windows Authentication

  # $srv.ConnectionContext.Login = "sa"

  # $srv.ConnectionContext.Password = "password"

  #Process Databases

  $dbs = $srv.Databases | Where-Object {$SkipDBs -notcontains $_.Name.ToString().ToLower() }

  $FirstDirSet = "Y"

  ForEach ($db in $dbs)

  {

  $dbName = $db.Name

  #if ($FirstDirSet -eq "Y")

  #{

  #$RestoreToDir = "F:\MSSQL\Data"

  #$RestoreLogToDir = "H:\MSSQL\Logs"

  #$FirstDirSet = "N"

  #}

  #else

  #{

  #$RestoreToDir = "G:\MSSQL\Data"

  #$RestoreLogToDir = "I:\MSSQL\Logs"

  #$FirstDirSet = "Y"

  #}

  " " | Out-File $SaveFile -Append

  "---- Kill all current connections" | Out-File $SaveFile -Append

  "DECLARE @cmdKill VARCHAR(50) ; " | Out-File $SaveFile -Append

  "DECLARE killCursor CURSOR FOR " | Out-File $SaveFile -Append

  "SELECT 'KILL ' + Convert(VARCHAR(5), p.spid) " | Out-File $SaveFile -Append

  "FROM master.dbo.sysprocesses AS p " | Out-File $SaveFile -Append

  "WHERE p.dbid IN(db_id('$dbName') ) ; " | Out-File $SaveFile -Append

  "OPEN killCursor ; " | Out-File $SaveFile -Append

  "FETCH killCursor INTO @cmdKill ; " | Out-File $SaveFile -Append

  "WHILE 0 = @@fetch_status " | Out-File $SaveFile -Append

  "BEGIN " | Out-File $SaveFile -Append

  "EXECUTE (@cmdKill) ; " | Out-File $SaveFile -Append

  "FETCH killCursor INTO @cmdKill ; " | Out-File $SaveFile -Append

  "END " | Out-File $SaveFile -Append

  "CLOSE killCursor ; " | Out-File $SaveFile -Append

  "DEALLOCATE killCursor ; " | Out-File $SaveFile -Append

  "-----------------------" | Out-File $SaveFile -Append

  " " | Out-File $SaveFile -Append

  "exec master.dbo.xp_restore_database @database = N'$dbName', " | Out-File $SaveFile -Append

  "@filename = N'$RestoreFromDir\" + $dbName + "_Backup.BAK', " | Out-File $SaveFile -Append

  "@filenumber = 1, " | Out-File $SaveFile -Append

  "@with = N'REPLACE', " | Out-File $SaveFile -Append

  "@with = N'STATS = 10', " | Out-File $SaveFile -Append

  $fgs = $db.FileGroups

  $FileCnt = 0

  foreach ($fg in $fgs)

  {

  $files = $fg.Files

  foreach($file in $files)

  {

  $FileCnt++

  $FileName = $file.Name

  if ($FileCnt -eq 1)

  {

  "@with = N'MOVE N''$FileName'' TO N''$RestoreToDir\$dbName.mdf''', " | Out-File $SaveFile -Append

  }

  else

  {

  "@with = N'MOVE N''$FileName'' TO N''$RestoreToDir\$dbName_$FileName.ndf''', " | Out-File $SaveFile -Append

  }

  }

  }

  $logfiles = $db.LogFiles

  $LogCnt = 0

  foreach ($LogFile in $logfiles)

  {

  $LogCnt++

  $LogName = $LogFile.Name

  if ($LogCnt -eq 1)

  {

  "@with = N'MOVE N''$LogName'' TO N''$RestoreLogToDir\" + $dbName + "_Log.ldf''', " | Out-File $SaveFile -Append

  }

  else

  {

  "@with = N'MOVE N''$LogName'' TO N''$RestoreLogToDir\" + $dbName + "_" + $LogName + ".ldf''', " | Out-File $SaveFile -Append

  }

  }

  "@affinity = 0, " | Out-File $SaveFile -Append

  "@logging = 0 " | Out-File $SaveFile -Append

  "GO " | Out-File $SaveFile -Append

  "" | Out-File $SaveFile -Append

  }