Scott on Writing

Generating a List of Unattached Databases

SQL Server databases are implemented as physical files with the MDF extension. They can be attached and detached from the running SQL Server instance through Enterprise Manager/Management Studio or via the sp_attach_db and sp_detach_db system stored procedures. Alex B. recently asked a question on the sql_server_maint listserv I'm on about how to list the unattached databases:

Is there a way to find out what db's are unattached on your server?

In short, Alex was interested in having a list of those MDF files on his file system that were not registered with the SQL Server instance. Michael Campbell chimed in:

You won't be able to get a list of unattached dbs. SQL Server will keep track of which files it currently has its hooks into, but once you detach a DB, SQL Server no longer tracks it - it's like it no longer exists as far as SQL Server is concerned. (Which makes perfect sense - as the alternative would be something lame like "SQL Server can't start 'cuz it can't find a file that you detached 3 months ago, and just now deleted...")

Michael suggested trying to move a file to determine whether or not it was attached. If attempting to move the MDF file gave you a locked file error, it was (probably) because it was attached to the SQL Server instance.

Here's a more thorough solution, one that uses a bit of SQL, a bit of command-line goodness, and a little program called Excel. (Excel, believe it or not, has helped out with a sundry of development tasks, such as auto-generating code, turning a list of statements into a series of INSERT statements (to ease porting data from a text file into SQL Server), and so on.) Here's what to do:

1. Open Excel
2. Get a list of the attached databases for the SQL Server instance by running the following query in SQL Query Analyzer:
   
   select [filename] from master.dbo.sysdatabases
   
3. Copy the results from the results grid directly into a column in Excel. (I started it at cell A1.)
4. Next, we need a list of all of the MDF files in the file system. Drop to the command line and run:
   
   dir /s /b C:\*.MDF > dblist.txt
   
   This will recurse through all of the directories on C: looking for any file with the extension MDF, and will save the results in a file named dblist.txt.
5. Open dblist.txt in Notepad, copy its contents, and paste them into Excel. (I started this at cell C1.)
6. Using the tip discussed at Comparing Two Lists with Conditional Formatting, with a few clicks of the mouse you can name the two list ranges and then apply conditional formatting so that the two lists will be color-coded such to show:
   
   -- What files are similar between the two lists (these are the attached databases)
   -- What files are attached, but not in the file system (in which case you probably have the MDF files on another drive)
   -- What files are in the file system, but not attached, which was what Alex was after
   
   The screen shot below shows the attached DBs listed on the left and the MDF files in the file system on the right. Those entries shaded green are those that are found in the list on the right, but not in the list on the left; namely, those database files on my file system that are not attached.

posted on Wednesday, April 26, 2006 6:37 PM