User DB transaction device file deleted | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

User DB transaction device file deleted

Hi, Got a recovery question here. If my user db transaction device file is deleted, but my user db data device file is intact, a) How do i perform the recovery? Do I perform the same recovery as if when the data device file is missing? Therefore, do I proceed with ‘restore database .. with norecovery’, then ‘restore log … with norecovery’ then at my last transaction log backup, ‘restore log …. with recovery’? b) In this case, am I right to say that my recovered database will not contain all the records up to the point of failure? cheers,
http://www.sql-server-performance.com/forum/topic.asp?TOPIC_ID=8475 MeanOldDBA
[email protected] When life gives you a lemon, fire the DBA.
Hi MeanOldDBA, Seen those posts already. I suspect that the reason those dba are performing sp_detach_db, renaming .mdf files, modifying systables etc is because there was no backups. Am I right? In my environment, I’ve daily database backups & hourly transaction log backups. I’m just trying to recover my DB when the H/D containing the trans log device file crashed (but my DB .mdf in another H/D is still intact). In this case, do I really need to go thru all those steps? I’m guessing that when I ‘restore database …’, the missing trans log device file will be automatically created. I can then ‘restore log ….’ to rollforward my DB. Of course I’m going to lose some data, but losing the trans log device file would always mean losing data whichever way I recover. I’m just surprised that SQL Svr 2000 does not allow s/w mirroring of trans log when both Oracle & Sybase have this feature for ages. But that’s another matter. Time permitting, I’ll like to try this out myself & keep this forum posted.
Tested & proven. If I’ve a valid backup of DB & trans log files, there is no need to sp_detach_db, renaming .mdf files etc as ‘restore database …..’ will automatically create the missing trans log device file. But my recovery is not up-to-point of failure as it’s the trans log that was corrupted.
]]>