How can I attach a database without an LDF file in SQL Server?
+5
A:
Hi Ricardo,
You can use sp_attach_single_file_db to attach a database which is missing it's log file.
Aaron Alton
2009-05-29 13:57:31
It gives this error:Could not open new database 'SharedCS'. CREATE DATABASE is aborted.Device activation error. The physical file name 'd:\Program Files\Microsoft SQL Server\MSSQL\data\dummy_Log.LDF' may be incorrect.
Ricardo
2009-05-29 14:11:36
Don't try to attach a dummy log. Can you post the sp_attach_single_file_db command you're running?
Aaron Alton
2009-05-29 14:41:37
+1
A:
You can "just do it" it'll throw a warning that it couldn't find the .ldf, but it will still attach the db.
Kyle West
2009-05-29 14:00:03
+1
A:
You can try what is posted here by MohammedU. Basically, what he uses the DBCC REBUILD_LOG command. It will work depending on the version of your server.
Here are the steps (without details):
- Rename existing .mdf file to .mdf_old
- Create a new database with same .mdf and .ldf file as old one.
- Stop the sql server
- Rename .mdf and .ldf files of the new db to .mdf_old and .ldf_old
- Rename .mdf_old to .mdf
- Start sql server
- You should see db in suspect mode
- Change the database context to Master and allow updates to system tables
- Set the database in Emergency (bypass recovery) mode.
- Stop and restart SQL server.
- Rebuild the log.
- Set the database in single-user mode and run DBCC CHECKDB to validate physical consistency.
- Turn off the updates to system tables.
eKek0
2009-05-29 14:31:20