Restore Database with Move Files

More for me than you.  Something I type up all the time and forget where the heck I put it.  Not tested, but should give you the general idea.

Import-Module SqlPS -DisableNameChecking

$destServers = @('Server1', 'Server2', 'Server3')
$srcPath = "\\sharepath\Backups"

try{
    
    $destServers | %{
        $serverName = $_ 

        $srv = New-Object Microsoft.SqlServer.Management.Smo.Server "$serverName,1433"

        gci -Path $srcPath | ?{$_.Extension -eq '.bak'} | %{
            $dbName = $_.BaseName 
            $backupFile = $_.FullName

            $backupDevice = New-Object Microsoft.SqlServer.Management.Smo.BackupDeviceItem ($backupFile, [Microsoft.SqlServer.Management.Smo.DeviceType]::File)
        
            $restore = New-Object Microsoft.SqlServer.Management.Smo.Restore
            $restore.Devices.Add($backupDevice);
            $restore.NoRecovery = $true;
            $restore.ReplaceDatabase = $true
            $restore.Database = $dbName

            $backup = New-Object Microsoft.SqlServer.Management.Smo.BackupDeviceItem ($backupFile, [Microsoft.SqlServer.Management.Smo.DeviceType]::File)
            $restore = New-Object Microsoft.SqlServer.Management.Smo.Restore
            $restore.Devices.Add($backup);
            $dbFiles = $restore.ReadFileList($srv);
            $moveFiles = @();
            foreach($dbFile in $dbFiles){
                $fileName = [System.IO.Path]::GetFileName($dbFile.PhysicalName);
                switch($dbFile.Type){
                    'D'{
                        $moveFiles += New-Object Microsoft.SqlServer.Management.Smo.RelocateFile($dbFile.LogicalName, "E:\MSSQL\Data\$fileName")
                    }
                    'L'{
                        $moveFiles += New-Object Microsoft.SqlServer.Management.Smo.RelocateFile($dbFile.LogicalName, "G:\MSSQL\TranLog\$fileName")
                    }
                }
            }

            $srv.ConnectionContext.StatementTimeout = 0;
            restore-sqldatabase -InputObject $srv -Database $dbName -BackupFile $backupFile -RelocateFile $moveFiles -ReplaceDatabase
        }
    }

}
catch{
    $_ | fl -Force
}
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: