Skip to content

April 4, 2017

Restore Database with Move Files

by Scott Newman

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"

    $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.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
            $dbFiles = $restore.ReadFileList($srv);
            $moveFiles = @();
            foreach($dbFile in $dbFiles){
                $fileName = [System.IO.Path]::GetFileName($dbFile.PhysicalName);
                        $moveFiles += New-Object Microsoft.SqlServer.Management.Smo.RelocateFile($dbFile.LogicalName, "E:\MSSQL\Data\$fileName")
                        $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

    $_ | fl -Force
Read more from Powershell, SQL Server

Leave a Reply

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

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

Google+ photo

You are commenting using your Google+ 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 )

Connecting to %s

Note: HTML is allowed. Your email address will never be published.

Subscribe to comments

%d bloggers like this: