Create new database from .BAK file using PowerShell and SMO library

Question

I'm trying to create a new SQL Server database from a .bak file using powershell.

Here is my script:

TRY
{
    [System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO') | out-null 
    [System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SmoExtended') | out-null
    $servername = "MyServer\MyServerInstance"
    $datapath= "E:\SQLData\MyNewDataBase"
    $logpath= "E:\SQLLogs\MyNewDataBase"
    $path= "\\MyServer\BKPFolder\"
    $server = new-object("Microsoft.SqlServer.Management.Smo.Server") $servername
    $folderitem=Get-ChildItem $path -filter *.bak -rec

    foreach($bkfiles in $folderitem)
    {
        $dbRestore = new-object("Microsoft.SqlServer.Management.Smo.Restore")
        $files = $path+$bkfiles.Name
        $backupFile = $files
        $dbRestore.Devices.AddDevice($backupFile, [Microsoft.SqlServer.Management.Smo.DeviceType]::File)
        $dbRestoreDetails = $dbRestore.ReadBackupHeader($server)
        $dbFileList= $dbRestore.ReadFileList($server)

        foreach ($row in $dbFileList)
        {
             $FileType = $row["Type"].ToUpper()

             If ($FileType.Equals("D")) 
             {
                 $DBLogicalName = $Row["LogicalName"]
             }

             ELSEIf ($FileType.Equals("L")) 
             {
                 $LogLogicalName = $Row["LogicalName"]
             }
        }

        $dbRestoreFile = new-object("Microsoft.SqlServer.Management.Smo.RelocateFile") 
        $dbRestoreLog = new-object("Microsoft.SqlServer.Management.Smo.RelocateFile")

        $dbRestoreFile.LogicalFileName = $DBLogicalName 
        $dbRestoreFile.PhysicalFileName =$datapath +  "\" + $DBLogicalName + ".mdf" 

        $dbRestoreLog.LogicalFileName =   $LogLogicalName 
        $dbRestoreLog.PhysicalFileName =  $logpath +  "\" + $LogLogicalName + ".ldf" 

        $dbRestore.RelocateFiles.Add($dbRestoreFile) 
        $dbRestore.RelocateFiles.Add($dbRestoreLog)                

        $dbRestore.Database = "MyNewDataBase"
        $dbRestore.NoRecovery = $false
        $dbRestore.Action = "DataBase"
        $dbRestore.FileNumber = 1;
        $dbRestore.ReplaceDatabase = $false;

        $dbRestore.SqlRestore($server)  
   }
} catch     {
    "Database restore failed:`n`n " + _.Exception.GetBaseException().Message
}

When I run this code, I get this error:

Directory lookup for the file "E:\SQLData\MyNewDataBase\OLDDataBase_Data.mdf" failed with the operating system error 2(The system cannot find the file specified.).

File 'OLDDataBase_Data' cannot be restored to 'E:\SQLData\MyNewDataBase\OLDDataBase_Data.mdf'. Use WITH MOVE to identify a valid location for the file.

Problems were identified while planning for the RESTORE statement. Previous messages provide details.
RESTORE DATABASE is terminating abnormally.

It looks like my script is trying to find the .mdf and .ldf files from the old database. But I just have a .bak file from the old database.

What am I missing?

Thanks.

Perhaps this might aid your efforts: https://learn.microsoft.com/en-us/powershell/module/sqlps/restore-sqldatabase?view=sqlserver-ps — Hames
– Hames, Commented Dec 1, 2017 at 16:13
Please try: https://learn.microsoft.com/en-us/powershell/module/sqlps/ and on the left side navigation, locate "Restore-SqlDatabase" — Hames
– Hames, Commented Dec 1, 2017 at 18:53

MD29 · Accepted Answer · 2017-12-19 13:28:55Z

After a lot of research and attempts, i finally got restore my data base. This worked for me:

[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMO") | Out-Null
    [System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SmoExtended") | Out-Null
    [Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.ConnectionInfo") | Out-Null
    [Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SmoEnum") | Out-Null                              

    #Define o novo local dos arquivos de mdf e ldf
    $RelocateData = New-Object Microsoft.SqlServer.Management.Smo.RelocateFile("OldDataBase_Data", "E:\SQLData\MyNewDataBase_DATA.mdf")
    $RelocateLog = New-Object Microsoft.SqlServer.Management.Smo.RelocateFile("OldDataBase_Log", "E:\SQLLogs\MyNewDataBase_LOG.ldf")

    #Executa a restauração
    Restore-SqlDatabase -ServerInstance "MyServer\MyServerInstance" -Database "MyNewDataBase" -BackupFile "\\BackupFolder\OldDataBase.bak" -RelocateFile @($RelocateData,$RelocateLog) -NoRecovery
    Restore-SqlDatabase -ServerInstance "MyServer\MyServerInstance" -Database "MyNewDataBase" -BackupFile "\\BackupFolder\OldDataBase.trn" -RestoreAction Log -NoRecovery

Hames · Accepted Answer · 2017-12-10 17:24:32Z

hope this is what you needed:

function Restore-MsSqlDatabase {
<#
.Synopsis
  Restore MSSQL database using Microsoft.SQLServer.Management.Smo.{BackupDeviceItem,Restore,RelocateFile}
.Description
  Restore MSSQL database using Microsoft.SQLServer.Management.Smo.{BackupDeviceItem,Restore,RelocateFile}
.Example
  Restore-MsSqlDatabase -BackupFile 'D:\backups\aateam_20491005.bak' -Verbose
.Example
  Restore-MsSqlDatabase -BackupFile 'D:\backups\aateam_20491005.bak' -SqlHost SQL-SERVER001 -Verbose
#>
  [CmdletBinding()]
  Param(
    [Parameter(Position=0)] [ValidateNotNullOrEmpty()]
    [string]$BackupFile,
    [Parameter(Position=1)] [ValidateNotNullOrEmpty()]
    [string]$SqlHost="localhost"
  )
  Begin{
    Write-Verbose ("$(Get-Date) - INFO - Load assembly for Microsoft.SqlServer.SMO")
    $null=[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMO")
    Write-Verbose ("$(Get-Date) - INFO - Load assembly for Microsoft.SqlServer.SMOExtended")
    $null=[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMOExtended")
    Write-Verbose ("$(Get-Date) - INFO - New connection object")
    $MsSqlConnection=New-Object Microsoft.SqlServer.Management.Smo.Server $SqlHost
    Write-Verbose ("$(Get-Date) - INFO - Initialize connection")
    $null=$MsSqlConnection.Initialize()
  }
  Process{
    Try{
      Write-Verbose ("$(Get-Date) - INFO - New backup device item")
      $BackupDeviceItem=New-Object Microsoft.SQLServer.Management.Smo.BackupDeviceItem
      $BackupDeviceItem.Name=$backupFile
      $BackupDeviceItem.DeviceType="File"
      Write-Verbose ("$(Get-Date) - INFO - New restore device")
      $RestoreDevice=New-Object Microsoft.SQLServer.Management.Smo.Restore
      $RestoreDevice.Action="Database"
      $RestoreDevice.ReplaceDatabase=$true
      $RestoreDevice.NoRecovery=$false
      Write-Verbose ("$(Get-Date) - INFO - Add backup device item to restore device")
      $RestoreDevice.Devices.Add($BackupDeviceItem)
      Write-Verbose ("$(Get-Date) - INFO - Read backup header")
      $BackupHeader=$RestoreDevice.ReadBackupHeader($MsSqlConnection)
      Write-Verbose ("$(Get-Date) - INFO - Set databse name in restore device")
      $RestoreName=$BackupHeader.Rows[0].DatabaseName
      $RestoreDevice.Database=$RestoreName
      Write-Verbose ("$(Get-Date) - INFO - Read backup file list")
      $FileList=$RestoreDevice.ReadFileList($MsSqlConnection)
      Write-Verbose ("$(Get-Date) - INFO - Relocate mdf,ldf,ndf files")
      ForEach ($File in $FileList) {
        Write-Verbose ("$(Get-Date) - INFO - New relocate device")
        $RelocateFile=New-Object Microsoft.SqlServer.Management.Smo.RelocateFile
        Switch ($File.FileId) {
          1 {
            Write-Verbose ("$(Get-Date) - INFO - New physical path for mdf file")
            $NewPhysicalPath="{0}\{1}.mdf" -f $MsSqlConnection.DefaultFile,$RestoreName
          }
          2 {
            Write-Verbose ("$(Get-Date) - INFO - New physical path for ldf file")
            $NewPhysicalPath="{0}\{1}.ldf" -f $MsSqlConnection.DefaultFile,$RestoreName
          }
          Default {
            Continue
          }
        }
        Write-Verbose ("$(Get-Date) - INFO - Relocate files")
        $RelocateFile.LogicalFileName=$File.LogicalName
        $RelocateFile.PhysicalFileName=$NewPhysicalPath
        $null=$RestoreDevice.RelocateFiles.Add($RelocateFile)
      }
      Write-Verbose ("$(Get-Date) - INFO - Test if database already exists")
      If($MsSqlConnection.Databases[$RestoreName]){
        Write-Verbose ("$(Get-Date) - INFO - Kill all processes connected to database")
        $MsSqlConnection.KillAllProcesses($RestoreName)
        Write-Verbose ("$(Get-Date) - INFO - Set database offline")
        $MsSqlConnection.Databases[$RestoreName].SetOffline()
      }
      Else{
        Write-Verbose ("$(Get-Date) - INFO - Databse currently not present in SQL instance")
      }
      Write-Verbose ("$(Get-Date) - INFO - Restore device")
      $RestoreDevice.SQLRestore($MsSqlConnection)
      $MsSqlConnection.Databases.Refresh()
      Write-Verbose ("$(Get-Date) - INFO - Set database online")
      $MsSqlConnection.Databases[$RestoreName].SetOnline()
    }
    Catch{
      Write-Verbose ("$(Get-Date) - ERROR - {0}" -f $_.Exception.Message)
    }
    Finally{}
  }
  End{}
}

Then do:

Get-Help -Name Restore-MsSqlDatabase -Examples

Collectives™ on Stack Overflow

Create new database from .BAK file using PowerShell and SMO library

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related