Sql SMO: How to get path of database physical file

问题:

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?

回答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

回答2:

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;
}

回答3:

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

回答4:

I think the easiest approach would be to run sql script on your sql server instance which will always return you correct data and log file paths. The following sql will do the trick

SELECT
    db.name AS DBName,
    (select mf.Physical_Name FROM sys.master_files mf where mf.type_desc = 'ROWS' and db.database_id = mf.database_id ) as DataFile,
    (select mf.Physical_Name FROM sys.master_files mf where mf.type_desc = 'LOG' and db.database_id = mf.database_id ) as LogFile
FROM sys.databases db
order by DBName

You can still execute this sql using SMO if you want to, which will return you a dataset and then you can extract that information.

var result = new List();
            var server = new Server( serverInstanceName );
            var data = server.Databases[ "master" ].ExecuteWithResults(sql);

            foreach ( DataRow row in data.Tables[ 0 ].Rows )
                result.Add( new DatabaseInfo( row[ "DBName" ].ToString(), row[ "DataFile" ].ToString(), row[ "LogFile" ].ToString() ) );

            return result;

If you will use this snippet then make sure to create a DatabaseInfo class which will store the information returned from Sql server instance.

回答5:

using Smo = Microsoft.SqlServer.Management.Smo;

public string GetDataBasePath(string strDatabaseName)
{
    ServerConnection srvConn = new ServerConnection();
    srvConn.ConnectionString = "<your connection string goes here>";
    Server srv = new Server(srvConn);

    foreach (Smo.Database db in srv.Databases)
    {
        if (string.Compare(strDatabaseName, db.Name, true) == 0)
        {
            return db.PrimaryFilePath;
        }
    }

    return string.Empty;
}