SQL SMO Objects SqlRestore Method (ReadFileList)

Question

Good afternoon all-

I've searched around quite a bit, and found a few good resources on how to dynamically determine the names of the logical data file names contained within an and SQL .bak file. The SMO method Im working with requires that I pass the ServerName, however my requirement calls for passing the actual file path to the backup. I can get what I need in T-SQL, but I'd really like to determine a way to do it leveraging SMO's. Below is the T-SQL which gets me the information I require:

RESTORE FILELISTONLY
FROM N'C:\Directory\File.bak'
WITH FILE = 1

Unfortunately SqlRestore.ReadFileList(ServerName) will not work, as the backup set has not been restored to a server yet. Essentially I need this information so I can pass it to Restore.RelocateFiles.Add. I'm actually a DBA just dabbling in C#, so if you need more information just let me know and I will try to fill in the gaps. Thanks for any assistance!

• mellamokb: I don't read that SqlRestore.ReadFileList requires the backup set to be restored to a server (msdn.microsoft.com/en-us/library/…). I would think it just needs reference to a server instance for the purpose of having the SQL engine that can read the backup-set filelist from a backup file. What happens when you call that method? Do you get some sort of error message?
• user1253174: In the link you provided it shows the backupset being read from the server level, I need to be able to pass a backup device (path to the bak) in order to read the file names in order for me to pass them to Restore.Relocate.

Answer 1

The Powershell script below shows how you can read a backup file based on a file path:

$ServerName="SERVER\MYSQLSERVER"
$svrConn = new-object Microsoft.SqlServer.Management.Common.ServerConnection
$svrConn.ServerInstance=$secondaryServerName
$svrConn.LoginSecure = $true
$svr = new-object Microsoft.SqlServer.Management.Smo.Server ($svrConn)
$fullResotrePath = "\\Path\MyDatabase.bak"
$res = new-object Microsoft.SqlServer.Management.Smo.Restore
$res.Devices.AddDevice($fullRestorePath, [Microsoft.SqlServer.Management.Smo.DeviceType]::File)
$dt = $res.ReadFileList($svr)
foreach($r in $dt.Rows)
{
 foreach ($c in $dt.Columns)
 {
 Write-Host $c "=" $r[$c]
 }
}