LLI Wiki — My Digital Knowledge Base

SQL Server: recover a database without its LDF file

Emergency procedure to rebuild a SQL Server database from its MDF file only, using EMERGENCY mode and DBCC CHECKDB.

When the transaction log file (.ldf) of a SQL Server database is lost or unusable but the data file (.mdf) is available, this procedure lets you bring the database back and rebuild a log. It can cause data loss: use it only when no usable backup exists.

Procedure

  1. If the database already exists, put it in emergency mode:
ALTER DATABASE maBase SET EMERGENCY;
  1. Stop the SQL Server service.
  2. Delete the .ldf file.
  3. Replace the .mdf file with the one to recover (rename it if needed).
  4. Restart the SQL Server service.
  5. Check the database (optional, before repair):
DBCC CHECKDB ( 'maBase' ) WITH NO_INFOMSGS;
  1. Switch to single-user mode, repair, then reopen the database:
ALTER DATABASE maBase SET SINGLE_USER;

DBCC CHECKDB ( 'maBase' , REPAIR_ALLOW_DATA_LOSS) WITH NO_INFOMSGS;

ALTER DATABASE maBase SET MULTI_USER;

Notes

Mastodon LLIMastodon SocialFosstodon