SQL Server Attach Database Doesn’t show MDB LDB files

In case any of you are running into this problem: If you go to attach a SQL Server database in SQL 2005 (and perhaps in 2000, not sure), and you browse to the folder or directory that has the .MDF and .LDF files, and you find that your database files (mdf and ldf) are not listed even though you know absolutely for certain that they are there…

It’s because SQL Server 2005 will not include them in the list of available database files to attach if the ARCHIVE BIT is not set.

You can set this bit by simply change the name of the mdf/ldf files and just change the case. (That is, MDF to mdf, or mdf to MDF) which will set the archive bit (or use File Properties and set the bit that way) and the files will show up when you browse for the database files to attach.

Occasionally, your MDF and LDF files will not show in the list even if the bit IS set.  In this case, open file properties for the MDF/LDF files, clear the bit, apply, then reset the bit, and apply.

Update: This seems to be true of SQL 2008 also!

Advertisements

About combatdba

I'm a production DBA at a terabyte-class SQL Server Shop
This entry was posted in S.W.A.T.. Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s