I'm trying to recover a database that has one master data file (.mdf) and one log data file (.ldf). I got the .mdf file from a standard OS backup tape. But sp_detach_db wasn't run on the database before the .mdf backup, so I don't have the .ldf file. I know that the stored procedure sp_attach_single_file_db can recreate the log file in some cases, and I've tried to use it to simply reattach the database, but I get the following error:
Server: Msg 1813, Level 16, State 2, Line 1 Could not open new database 'db'. CREATE DATABASE is aborted. Device activation error. The physical file name 'C:\Program Files\Microsoft SQL ServerMSSQL\datadb_log.LDF' may be incorrect.
Can I Recover My Database?
SQL Server Books Online (BOL) clearly documents that you must run sp_detach_db on a database to let the database reattach with sp_attach_db or sp_attach_single_file_db. Using sp_detach_db ensures transactional consistency within the database and ensures data integrity. However, if complete data integrity isn't important or you know that no data has changed recently, you might be able to use the undocumented Database Consistency Checker (DBCC) REBUILD_LOG command that Listing 1 shows to attach the database. REBUILD_LOG will recreate a new log file and let you reattach a database even if a good log file doesn't exist. However, the data might not be transactionally consistent because you might have thrown away active and uncommitted transactions. Use this command only for emergency recovery when you move data to a new database.
Related: Recovering a database with a missing transaction log
Use caution when you apply any undocumented technique in a production environment. I strongly encourage you to contact Microsoft Product Support Services (PSS) for recovery of production data rather than use undocumented recovery techniques. But sometimes, tips such as this one are good to have in your bag of tricks.
