Assuming data files are on D: and Log files are on E:
Say the E drive crashes and the log files (.ldf) are lost. I attach a new empty disk to the E: and start the sql server.
Upon startup, SQL Server will realize that the .mdf files are present, but the log files are absent. Due to missing log files, the SQL server will be unable to do any of its undo/redo recovery steps and it will place the database as a suspect.
Note: I tried this and notice that database goes into suspect mode. The best practice here is to use the backups (full + tlog) to restore the database. In my situation I have the full backup from 12am but no tlog backups. If I restore from the 12am backup then I loose entire days data (assuming crash happened mid-day). I am trying to think how to proceed in such situation by utilizing the existing data file.
- How to recover the database in this scenario?
- Will it be correct to say that data loss will be only those transactions that were in the tlog file but not written to the disk?