| Celia A. 2006-03-30, 8:23 pm |
| I've just had the same problem and was able to recover. I only had an mdf
data file copied just before a complete hard drive failure, while the
database was still online. The previously posted responses do not work with
SQL Server 2005, though they provided good hints.
Solution:
- Create a new database with the same name and logical file names
- Put this database in emergency mode: alter database dbname set emergency
- Stop SQL Server
- Delete the new database’s mdf and ldf files, replace with a copy of the
rescued mdf (no log)
- Restart SQL Server
- Objects and data are now accessible to one administrator, though this
database still cannot be backed up or brought online
- Use the Generate Scripts wizard to script all database objects (include
scripting object permissions, dependent objects, indexes; output to a file)
- Create a second new database
- Run the object creation script in the new database two or three times
until all objects are created, making adjustments if necessary
- Use the Import Data wizard to copy data from all rescued tables into the
new database
- Backup the new database and restore it in place of the emergency db
It would sure be nice if Microsoft improved RESTORE options for orphaned mdf
files... And restored functionality that was lost between Enterprise Manager
2000 and Management Studio 2005 regarding scripting (e.g. no scripting only
constraints or relationships) and data import/export (no schema information
can be transferred such as primary keys, defaults, relationships).
|