SQL Server 2008: Self-Destructing Databases

Using SQL Server 2008?  There’s a little something you should know about to avoid losing data.  Earlier, I wrote:

Certain previously-innocent actions create new (often empty) database files, without warning, and (by default) in the Program Files folder.  If you lose these files, your database goes straight to the bit bucket.  So, if you attach your 15GB database by copying the .MDF and .LDF files, enable full-text indexing, detach your database, and take your 15GB home with you, you’re now the proud owner of 15 GB of uselessness.  Your database is gone forever because you didn’t also grab that 2 KB file SQL Server generated automatically and placed in your Program Files folder.  Whoops.

Well, here’s a bit more detail.

When working with databases in SQL Server 2008, it’s very easy to unknowingly create additional database files.  A great example of this is working with full-text indexing.  In SQL Server 2005, full-text indexes were saved outside of the standard database files, in a separate directory.  Losing this directory wasn’t too big a deal, because re-indexing a database is usually a trivial process.  Even if the full-text files were lost, it was easy to restore or attach the database itself and then recreate the full-text index.

In SQL Server 2008, full-text indexes are now stored in the database itself.  This is a great improvement for managing databases; it means the full-text index is part of the actual database itself, and is included in backups.  Wonderful!  The only problem is that the index is actually placed in a separate data file.  SQL Server experts fully understand that one SQL Server database can contain several database files; there are lots of great reasons for doing this.  In fact, putting the full-text data in a separate file is probably a good architectural decision.  But people working with more simple SQL Server deployments might not realize this.  To these people, a SQL Server database consists of an .MDF file (the data), an .LDF file (the log), and perhaps an extra folder of full-text data.  The only file that was really needed to save or copy the database was the .MDF file; the log and full-text index could easily be recreated.  Now, though, the database file containing the full-text index will be in a separate .NDF file, placed by default in the Program Files folder, and the primary .MDF file is entirely useless without this new .NDF file.

So, what’s the point of all this?  Know the following:

• Before copying a SQL Server 2008 database’s files, make sure you know exactly which files are used to store the database, and make sure you copy all of them.
• Making an SQL Server backup, copying the backup file, and restoring from this backup is a much safer way of saving and moving databases.
• Now, more than ever, it’s important to specify locations for all database files.  Program Files is not a good place to be saving data.

Overall, this is a good change, but you really have to know what’s going on.  There’s nothing complicated here, but if you’re not aware of these changes, they can easily catch you by surprise.