CODEWITHSUNDEEP
c#sql-server
coder
coder

Reputation: 4313

how to read sql server mdf header for log file information programmatically in c#

I need to attach databases programmatically but the log file naming conventions don't appear to be same.

For example:
database1.mdf has database1.ldf, database2.mdf has database2_log.ldf and so on...

So, my guess was that the information about the log file would be in the header data of the mdf file, but I'm not sure how to read it.

I did a google search and got this code, but it's to read the version information.

using (FileStream fs = File.OpenRead(@"C:\database.mdf"))
{
    using (BinaryReader br = new BinaryReader(fs))
    {
        // Skip pages 0-8 (8 KB each) of the .mdf file,
        // plus the 96 byte header of page 9 and the
        // first 4 bytes of the body of page 9,
        // then read the next 2 bytes

        int position = 9 * 8192 + 96 + 4;

        br.ReadBytes(position);

        byte[] buffer = br.ReadBytes(2);

        dbiVersion = buffer[0] + 256 * buffer[1];
    }
}

======================================================

question updated:

  1. My app installs sql express
  2. Have many .mdf and .ldf files on a disk
  3. app copies all the databases and log files to sql data directory
  4. app attempts to attach the databases programmatically.

FileInfo mdf = new FileInfo(dbfile);

databasename = mdf.Name.ToLower().Replace(@".mdf", @"");
StringCollection databasefiles = new StringCollection();
databasefiles.Add(mdf.FullName);
databasefiles.Add(mdf.FullName.ToLower().Replace(@".ldf", @""));

//this is where I have issue. Obviously I can't assume that the log file name would be the same as mdf file name with ldf extension. Thats when I thought there would be a way to read the header information from mdf file, and that will have ldf information.

Server sqlServer = new Server(textServer.Text);
sqlServer.AttachDatabase(databasename, databasefiles);

Upvotes: 3

Views: 2540

Answers (1)

JonH
JonH

Reputation: 33163

You shouldn't have to know the log file name in order to attach a database and a log file to that database. That would mean a lot of hard coding of data. Use SMO objects:

 Microsoft.SqlServer.Management.Smo.Server server = new ServerConnection("enter server name");
 Microsoft.SqlServer.Management.Smo.Database db = server.Databases("enter db name");
 Console.WriteLine(db.FileGroups[0].Files[0].FileName); 'the mdf file
 Console.WriteLine(db.LogFiles[0].FileName); 'the log file

By using SMO you will have not only a handle to your sql server instance as well as every single database on the instance. But the nice thing is the handle you have to the database instance, contains pointers to both the mdf file and the log file. It avoids having to hard code file names.

Here's the MSDN on SMO

Upvotes: 3

Related Questions