a colleague deleted accidentally all transaction logs (SQLServer/Logs/*.ldf files) of our SQL Server 2016 instance. Now we cannot start SQL Server service anymore. We tried to use a recovery data tool (https://www.cgsecurity.org/wiki/Undelete_files_from_NTFS_with_TestDisk) but not all files were there.

Here is the error that is shown in the Windows Event Logs:

enter image description here

Is there any way to recover/rebuild the logs or any other procedure to make it work again?


  • I've edited the post with more info. We do not have backups Dec 19, 2018 at 11:42

2 Answers 2


Firstly, I am wondering how a colleague was able to delete the log files. The SQL instance must not have been in a running state when the delete happened. There is no way you can delete the file when the instance is running. You will get the error:

enter image description here

Luckily, if it is your master database there is a chance that there is not a whole lot of information in the transaction log. Most logins, metadata do not change that often and most likely already written to the.mdf on disk.

Another thing is you are running SQL Server 2016, which has the system database templates if you were not aware. These are template blank databases created during your first installation. You can just replace them and you will be up and running. The only caveat is you will not have any user metadata.

The templates are found in your BINN folder, for example:

C:\Program Files\Microsoft SQL Server\MSSQL13.SQL2K16\MSSQL\Binn\Templates

You will then have your instance up and running in no time. You can then repair your master database if needed with no rush. You could also use a SQL database recovery tool to recover your system database or user databases.


Is there any way to recover/rebuild the logs or any other procedure to make it work again? We do not have backups.

That depends on how you define "work again" as the log holds information to make the database consistent. This means even if you did get the database back up and running there is no guarantee that the data in it is correct. So maybe that "works" for you but if it's something like accounts payable, that might not be a solution.

You can give this a shot for user databases, but again YMMV and the data shouldn't be trusted. If there is a way to re-create the database and import the data, that would be best.

With system databases the easiest method is to rebuild them from the media. Since master is an issue in this instance, you'll lose information such as logins (if they are SQL logins their associated passwords), linked servers, any instance wide certificates, etc.

If you get this back up and running, I'd implement backups and lock down servers and permissions.


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.