Friday, February 27, 2009

Trying to attach an MDF file to SQL Server 2005?

While this is not recommended especially when you're a bit paranoid about disaster recovery and data integrity, I still find a lot of people doing it. I guess a lot of independent software vendors don't understand the underlying concepts behind the database engine. While it may have worked in SQL Server 2000 using Enterprise Manager, trying to attach an MDF file in SQL Server 2005 Management Studio will fail. To work around this, you can use the sp_attach_single_file_db system stored procedure. This will attach the MDF file and create an LDF file as well. This will only work if your database only has one datafile. Using this if your database has multipe datafiles won't work. You do have to run the DBCC CHECKDB command to validate your database

I'd still prefer the backup/restore approach. Nothing beats having a peace of mind
Google