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.

Answer 1

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