Restoring the database with Just Log File is not possible. If you are looking for the answer to this question, you have bad news. I am writing this blog post for a simple reason, when I get an email asking for the same question again, I answer the question quickly with a link to the blog post.
Here are the top 5 questions I have received in the last 3 months about restoring a database with just a log file.
Q1: Is it possible to recreate the database with just a log file, I have do not have a data file?
Q2: How to generate mdf file from the ldf file?
Q3: I have multiple data file in my database and I have all the datafiles except one file and all the log file, can I restore my database?
Q4: I have all my log file backup and not datafile backup, is there any way to stitch the log files to generate data files?
Q5: I have no backup and ransomware is encrypted all my data and log file and I have no backup, what is the workaround?
Unfortunately, the answer to all the questions is – No, it is not possible.
If you do not have your data file or its backup, only having a log file or log file’s backup is not good at all. If you only have a log file (and no data files), you can’t generate your database from log files even if you have a third party tool that claims that they will regenerate the database for you. At the most third party tool can read the log file and give you some idea about the recent data which was modified there is no way, it can help you to regenerate the database at all.
Let me re-iterate if there is no good backup with you, in most cases when the accident happens, the situation is extremely unfortunate. Always take your backup and test it regularly. Here is my most popular blog post about the backup timeline: SQL SERVER – Backup Timeline and Understanding of Database Restore Process in Full Recovery Model.
Here are blog posts which you may find relevant to this topic:
- SQL SERVER – Restore Database Backup using SQL Script (T-SQL)
- SQL SERVER – Fastest Way to Restore Database
- SQL SERVER – Restore Database Without or With Backup – Everything About Restore and Backup
- SQL SERVER – Backup and Restore Database Using Command Prompt – SQLCMD
- SQL SERVER – Database Backup and Restore Job Management
- SQL SERVER – Restore SQL Database using SSMS – SQL in Sixty Seconds #044 – Video
- SQL SERVER – Restore Database Wizard in SSMS is Very Slow to Open
- SQL SERVER – How to Restore Corrupted Model Database from Backup?
- SQL SERVER – A Quick Script for Point in Time Recovery – Back Up and Restore
- SQL SERVER – Database Stuck in Restoring State
Reference: Pinal Dave (https://blog.sqlauthority.com)
You can restore the database using the LDF file only if you have Full backups and Log backups until now with no gap between them (corrupt log backup). The current log file can be backed up. It is called tail-of-log backup that can be used at the final of the restoration process. You will restore this backup with Recovery option at last after restoring FULL + ALL LOG backups
BACKUP LOG your_db_name TO DISK = ‘disk:location’ WITH INIT, NO_TRUNCATE;