Recovering DB with LDF only

Of late, I witnessed the loss of mdf (due to corrupted or accidentally removed/overwritten) is painful. It is easy to recover the data from the .bak (database backup) or mdf(database file), but how would it be done the same thing just using ldf (log file) ? Here are the 10 steps that needs to be followed in order you have data restored.

1. Find out your previous database .bak file (backup file).

2. Backup your updated .MDF file and the latest LDF file

3. Delete your current database
- If you cant access to SQL Queries Analyzer no worries just open with master
- type :

DROP DATABASE SAMPLE_DB
GO

p/s - Make sure your infected MDF and LDF fully deleted from C:\Program Files\Microsoft SQL Server\MSSQL\Data

4. Create a new database with FULL Mode
- Open SQL Analyzer type following codes:
- type :

USE master
GO

CREATE DATABASE myDB
ON(NAME='myDB',
FILENAME='C:\Program Files\Microsoft SQL Server\MSSQL\Data\myDB..mdf')
LOG ON(NAME='myDB',
FILENAME='C:\Program Files\Microsoft SQL Server\MSSQL\Data\myDB_Log')
GO

ALTER DATABASE myDB
SET RECOVERY FULL
GO

EXEC sp_dboption 'myDB', 'autoclose', true
GO

5. Delete existing data and log files
- type :

EXEC master..xp_cmdshell 'del C:\Program Files\Microsoft SQL Server\MSSQL\Data\myDB.mdf
EXEC master..xp_cmdshell 'del C:\Program Files\Microsoft SQL Server\MSSQL\Data\myDB_Log'
GO

- Make sure no more .MDF and .LDF in your C:\Program Files\Microsoft SQL Server\MSSQL\Data


6. Stop your whole database

7. Copy your previous infected .MDF and .LDF to C:\Program Files\Microsoft SQL Server\MSSQL\Data

8. Backup the log with NO_TRUNCATE
- Note that backup will error due to inaccessible data file, but log will still be backed up.
- type :

BACKUP LOG myDB
TO DISK='C:\Backups\myDB.bak'
WITH NO_TRUNCATE, INIT
GO

- Make sure backup success, check the .bak file if the file size too small if compare with your .LDF file size then might be error, maybe your infected .LDF file stored wrongly, but normally no problem.
- This is very important file, no error here then fine already.

9. Restore your database

- Restore your previous backup database. Doesn't matter if your data not up to date.
- type :

USE Master

RESTORE DATABASE DatabaseName
FROM DISK = 'c:\Backups\old_myDB.BAK
WITH NORECOVERY

- p/s Remember use WITH NORECOVERY



10. Restore your backup log

- type:

RESTORE LOG myDB
FROM DISK = 'C:\Backups\myDB.bak'
WITH RECOVERY

- p/s Remember use WITH RECOVERY

- Check your data again.
- Done !!!