Detaching a SQL Server 2005 database that has had it's MDF/LDF file moved or lost.

Question

I created a document management system for a client which uses SharePoint and SQL Server to store PDF documents. Due to some SAN misconfiguration 3 disks that were holding both MDF and LDF database files dissapeared from the OS one day. We are in the process of recovering the data of the SAN but my question is how do I detach an existing database when it's possible that it's MDF or LDF or both files are no longer where the database expects it to be. I've noticed that even when I try to look at properties SQL Server complains that it can't find one of the files.

Do I need to reestablish the disk with the folder structure and MDF/LDF file as it was originally configured for the database in question or can I just configure the database to point to the MDF/LDF in a new location?

Answer 1

As far as I know detaching and attaching is the only way to go when you need to point a db to different files (or same files but different location) BUT when the mdf and ldf files are lost the detach (or delete) will fail, as you are experiencing.

If you re-establish mdf and ldf files were they were before it will work - but I am not sure this is what you want.

If you don't have the files you could try Creating dummy mdf and ldf files where they used to be (name must be identical) and you might fool it into letting you detach.

OR

(if you have a backup - guess so!) try to overwrite your db with a restore and you can pick new file names/location.