Disaster Recovery - Restoring SQL Server database without MDF

Question

Given the following (hypothetical) scenario, how would one best backup/restore the database.

1. Daily doing full backups @ 12 am.
2. Hourly doing differentials 1 am, 2am etc
3. Transaction log backups on the half hours, 130am, 230am etc
4. I am also storing the active .ldf file on drive X and the .mdf on drive Y.

Also important the master db is on Y.

Lets say hypothetically the Y drive fails at 245am.

I have the full, diffs and transaction logs up until 230am. BUT I also have the .ldf.

In theory I would have to probably reinstall SQL Server. Then I would want to recover that database up until 245am.

I have heard of doing a tail-log backup on a restore operation BUT I don't have the .mdf anymore. So, I would need to create a new database from my full/diff/log backups. After that I'm not sure how to proceed to get that last 15 minutes of transactions.

I hope this is making sense...

Thanks!

Steve.

Answer 1

You are asking,how to take TailLog Backup when you don't have access to MDF files..

This works only if your database is not in BulkLoggedRecovery model or your log doesn't have Bulk logged transactions..This has been covered in depth here: Disaster recovery 101: backing up the tail of the log

Here are the steps in order

1. Create a dummy database with same names
2. Delete all files of this dummy database,by bringing it offline
3. Copy the original database LDF
4. Bring this database online which will fail..

Now you can take TailLog Backup using below command..

BACKUP LOG dummydb 
TO DISK = N'D:\SQLskills\DemoBackups\DBMaint_Log_Tail.bck' WITH INIT, NO_TRUNCATE;
GO

Now since you have all the backups,you can restore to point in time of Failure