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
- If the database already exists, put it in emergency mode:
ALTER DATABASE maBase SET EMERGENCY;
- Stop the SQL Server service.
- Delete the
.ldffile. - Replace the
.mdffile with the one to recover (rename it if needed). - Restart the SQL Server service.
- Check the database (optional, before repair):
DBCC CHECKDB ( 'maBase' ) WITH NO_INFOMSGS;
- 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
EMERGENCYmakes the database read-only and accessible only tosysadminmembers.REPAIR_ALLOW_DATA_LOSScan delete data to restore consistency: it is a last resort, and transactional consistency is not guaranteed.- Work on a copy of the files.