Sql SMO: How to get path of database physical file name?

Question

I am trying to return the physical file path of a database's mdf/ldf files.

I have tried using the following code:

Server srv = new Server(connection);
Database database = new Database(srv, dbName);

string filePath = database.PrimaryFilePath;

However this throws an exception "'database.PrimaryFilePath' threw an exception of type 'Microsoft.SqlServer.Management.Smo.PropertyNotSetException' - even though the database I'm running this against exists, and its mdf file is located in c:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL

What am I doing wrong?

Answer 1

Usually the problem is with the DefaultFile property being null. The default data file is where the data files are stored on the instance of SQL Server unless otherwise specified in the FileName property. If no other default location has been specified the property will return an empty string.

So, this property brings back nothing (empty string) if you didn't set the default location.

A workaround is to check the DefaultFile property, if it returns an empty string use SMO to get the master database then use the Database.PrimaryFilePath property to retrieve the Default Data File Location (since it hasn't changed)

Since you say the problem is with your PrimaryFilePath:

• Confirm that your connection is open
• Confirm that other properties are available

Answer 2

Server srv = new Server(connection); DatabaseCollection dbc = svr.Databases; Database database = dbc["dbName"]; string filePath = database.PrimaryFilePath;

Answer 3

This is how I do it, prepared for multiple file names. Access database.LogFiles to get the same list of log file names:

private static IList<string> _GetAttachedFileNames(Database database)
{
    var fileNames = new List<string>();

    foreach (FileGroup group in database.FileGroups)
        foreach (DataFile file in group.Files)
            fileNames.Add(file.FileName);

    return fileNames;
}