Getting the Logical File Name using RESTORE FILELISTONLY from a DB backup and storing it into a variable

Question

I am using a fairly simple PowerShell script to automate the process of restoring databases:

    #Script to restore database.
    $serverInstance = $args[0]
    $dbName = $args[1]
    $file = $args[2]
    $dataDestination = $args[3]
    $logDestination = $args[4]

    Import-Module sqlps

    $relocateData = New-Object Microsoft.SqlServer.Management.Smo.RelocateFile(###LOGICALFILENAME###, $dataDestination)
    $relocateLog = New-Object Microsoft.SqlServer.Management.Smo.RelocateFile(###LOGICALFILENAME###, $logDestination)

    Restore-SqlDatabase -ServerInstance $serverInstance -Database $dbName -BackupFile $file -RelocateFile @($relocateData,$relocateLog)

I am looking for a way to dynamically fetch the logical file names of the files contained in a database backup ($file) and store them into a variable so the can be renamed accordingly.

Anyone have any ideas? I've been banging my head against this for way too long! :)

Thanks for your help!

Answer 1

As always with Powershell and SQL Server Invoke-Sqlcmd is your friend. It returns a DataTable that's simple to navigate. EG

$dt = Invoke-Sqlcmd "restore filelistonly from disk='c:\temp\aw.bak'"

$dataFileLogicalName = ""
$logFileLogicalName = ""
foreach ($r in $dt)
{
  if ($r.Type -eq "L")
  {
    $logFileLogicalName = $r.LogicalName
  }
  if ($r.Type -eq "D")
  {
    $dataFileLogicalName = $r.LogicalName
  }
  write-host "$($r.Type) $($r.LogicalName)  $($r.PhysicalName)"
}

write-host "data=$dataFileLogicalName  log=$logFileLogicalName"

Answer 2

Ok, I was getting a similar error. But in my case I was not using PowerShell, but running it in SSMS.

So the steps to the issue and also how to fix are as follows.

First I downloaded the WideWorldImporters-Standard.bak file from releases here.

Now I run the following command in SSMS in an attemp to restore it.

RESTORE DATABASE WideWorldImporters FROM DISK = N'C:\Users\msi\Downloads\WideWorldImporters-Standard.bak'

This gives the following errors.

Msg 5133, Level 16, State 1, Line 2
Directory lookup for the file "D:\Data\WideWorldImporters.mdf" failed with the operating system error 3(The system cannot find the path specified.).
Msg 3156, Level 16, State 3, Line 2
File 'WWI_Primary' cannot be restored to 'D:\Data\WideWorldImporters.mdf'. Use WITH MOVE to identify a valid location for the file.
Msg 5133, Level 16, State 1, Line 2
Directory lookup for the file "D:\Data\WideWorldImporters_UserData.ndf" failed with the operating system error 3(The system cannot find the path specified.).
Msg 3156, Level 16, State 3, Line 2
File 'WWI_UserData' cannot be restored to 'D:\Data\WideWorldImporters_UserData.ndf'. Use WITH MOVE to identify a valid location for the file.
Msg 5133, Level 16, State 1, Line 2
Directory lookup for the file "E:\Log\WideWorldImporters.ldf" failed with the operating system error 3(The system cannot find the path specified.).
Msg 3156, Level 16, State 3, Line 2
File 'WWI_Log' cannot be restored to 'E:\Log\WideWorldImporters.ldf'. Use WITH MOVE to identify a valid location for the file.
Msg 3119, Level 16, State 1, Line 2
Problems were identified while planning for the RESTORE statement. Previous messages provide details.
Msg 3013, Level 16, State 1, Line 2
RESTORE DATABASE is terminating abnormally.

So now I change the command to include the mdf and ldf file paths as follows.

RESTORE DATABASE WideWorldImporters FROM DISK = N'C:\Users\msi\Downloads\WideWorldImporters-Standard.bak' WITH 
    MOVE 'WideWorldImporters' TO 'C:\Users\msi\AppData\Local\Microsoft\Microsoft SQL Server Local DB\Instances\MSSQLLocalDB\WideWorldImporters.mdf'
    , 
    MOVE 'WideWorldImporters_Log' TO 'C:\Users\msi\AppData\Local\Microsoft\Microsoft SQL Server Local DB\Instances\MSSQLLocalDB\WideWorldImporters_Log.ldf'

Now this gives me the error in question as follows.

Msg 3234, Level 16, State 2, Line 5
Logical file 'WideWorldImporters' is not part of database 'WideWorldImporters'. Use RESTORE FILELISTONLY to list the logical file names.
Msg 3013, Level 16, State 1, Line 5
RESTORE DATABASE is terminating abnormally.

Now I run the RESTORE FILELISTONLY command as suggested to get the list as follows.

This shows me the files along with path as follows.

LogicalName                                                                                                                      PhysicalName                                                                                                                                                                                                                                                     Type FileGroupName                                                                                                                    Size                 MaxSize              FileId               CreateLSN                               DropLSN                                 UniqueId                             ReadOnlyLSN                             ReadWriteLSN                            BackupSizeInBytes    SourceBlockSize FileGroupId LogGroupGUID                         DifferentialBaseLSN                     DifferentialBaseGUID                 IsReadOnly IsPresent TDEThumbprint                              SnapshotUrl
-------------------------------------------------------------------------------------------------------------------------------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ---- -------------------------------------------------------------------------------------------------------------------------------- -------------------- -------------------- -------------------- --------------------------------------- --------------------------------------- ------------------------------------ --------------------------------------- --------------------------------------- -------------------- --------------- ----------- ------------------------------------ --------------------------------------- ------------------------------------ ---------- --------- ------------------------------------------ ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
WWI_Primary                                                                                                                      D:\Data\WideWorldImporters.mdf                                                                                                                                                                                                                                   D    PRIMARY                                                                                                                          1073741824           35184372080640       1                    0                                       0                                       8D30F4F9-A463-404F-805A-9BD1C634B66B 0                                       0                                       11730944             512             1           NULL                                 623000001720300037                      C6D12B8E-E389-4961-ADD4-FE6E468F4E44 0          1         NULL                                       NULL
WWI_UserData                                                                                                                     D:\Data\WideWorldImporters_UserData.ndf                                                                                                                                                                                                                          D    USERDATA                                                                                                                         2147483648           35184372080640       3                    37000000095200001                       0                                       28D406E0-78FF-4400-9A4B-3A05D136B1F3 0                                       0                                       580124672            512             2           NULL                                 623000001720300037                      C6D12B8E-E389-4961-ADD4-FE6E468F4E44 0          1         NULL                                       NULL
WWI_Log                                                                                                                          E:\Log\WideWorldImporters.ldf                                                                                                                                                                                                                                    L    NULL                                                                                                                             104857600            2199023255552        2                    0                                       0                                       6AC6807E-8774-415B-8EFC-E8C569B0855E 0                                       0                                       0                    512             0           NULL                                 0                                       00000000-0000-0000-0000-000000000000 0          1         NULL                                       NULL

(3 rows affected)

Completion time: 2022-08-28T15:36:18.5341906+05:30

Now after taking note of the output, I modify the RESTORE Command to include all of the required files and paths as follows. This finally works.

RESTORE DATABASE WideWorldImporters FROM DISK = N'C:\Users\msi\Downloads\WideWorldImporters-Standard.bak' WITH 
MOVE 'WWI_Primary' TO 'C:\Users\msi\AppData\Local\Microsoft\Microsoft SQL Server Local DB\Instances\MSSQLLocalDB\WideWorldImporters.mdf'
, MOVE 'WWI_Log' TO 'C:\Users\msi\AppData\Local\Microsoft\Microsoft SQL Server Local DB\Instances\MSSQLLocalDB\WideWorldImporters_Log.ldf'
, MOVE 'WWI_UserData'  TO 'C:\Users\msi\AppData\Local\Microsoft\Microsoft SQL Server Local DB\Instances\MSSQLLocalDB\WideWorldImporters_UserData.ndf'