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"

    $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

One thought on “Restore Database with Move Files

  1. Thanks for your post. It’s very helpful.
    I tried with my configurations. But i have this error:
    ” Microsoft.SqlServer.Management.Smo.SmoException: System.Data.SqlClient.SqlError: The file ‘C:\MSSQL\DATA\filename_archiv.mdf’ cannot be overwritten. It is being used by database ‘archi_1’.
    à Microsoft.SqlServer.Management.Smo.ExecutionManager.ExecuteNonQueryWithMessage(StringCollection queries, ServerMessageEventHandler dbccMessageHandler, Boolean errorsAsMessages, Boolean retry)
    à Microsoft.SqlServer.Management.Smo.BackupRestoreBase.ExecuteSql(Server server, StringCollection queries)
    à Microsoft.SqlServer.Management.Smo.Restore.SqlRestore(Server srv)”

    Can you help me, please?
    Thank you so much.

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 )

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

This site uses Akismet to reduce spam. Learn how your comment data is processed.