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

Question

I am trying to return the physical path to the mdf / ldf database file.

I 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 like "Microsoft.SqlServer.Management.Smo.PropertyNotSetException" - even though the database I'm running with exists and its mdf file is located in c: \ Program Files \ Microsoft SQL Server \ MSSQL. 1 \ MSSQL

What am I doing wrong?

Answer 1

, DefaultFile null. - , SQL Server, FileName. , .

, ( ), .

DefaultFile, , SMO , Database.PrimaryFilePath ( )

, PrimaryFilePath:

• , .
• ,

Answer 2

, . .LogFiles :

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

Answer 3

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

Answer 4

, sql script sql, . sql

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

sql, SMO, , , .

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 fragment, then be sure to create the DatabaseInfo class, which will store the information returned from the Sql server instance.

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