Attach SQL Server database in C# and automatically

Question

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

My code is:

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

How do I pick the correct .ldf file. For eg in c:\temp I have db1.mdf, db2.mdf and db1.ldf, db_1.ldf, db1_log.ldf. How would I know which is the right .ldf file for db1.mdf

Answer 1

No, as far as I know, there's no real "link" between the MDF and LDF file in the files per se. There is a link - in the database metadata inside SQL Server.

My approach would probably be:

• check if mydatabase.ldf exists -> if yes, use that
• check if mydatabase_log.ldf exists -> if yes, use that
• check if mydatabase_1.ldf exists -> if yes, use that

and if you still haven't found your LDF file - you could probably do a search for mydatabase*.ldf in the directory where your MDF is located. That would fail miserably on our test servers - our sysadmins always installed the .MDF/.NDF data files into a SQL2008-DATA and the .LDF log files into a SQL2008-LOG directory - totally separate.

In that case, you would either need to configure your mapping .MDF -> .LDF in e.g. a config file or something, or just pop up a "Find the .LDF file" dialog to the user....