Home Forums Programming Powershell Restore with relocate database files location in powershell RE: Restore with relocate database files location in powershell

  • 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

    }