0

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?

Thanks

  • What files exactly were deleted? What errors do you see in the Event Log when opening SQL Server? Do you have backups? – George.Palacios Dec 19 '18 at 11:39
  • I've edited the post with more info. We do not have backups – peflorencio Dec 19 '18 at 11:42
2

Firstly, I am wondering how 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 .mdf on disk.

Another thing is you are running SQL 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:

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 can also use a SQL database recovery tool called Stellar SQL Database to recovery your system database or user databases in an easy and seamless manner.

|improve this answer|||||
3

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.

|improve this answer|||||
  • At the moment, it is not important that the data is inconsistent. I only want to put the instance running and maybe recover the data of some of the databases (consistent or not). The problem is that I cannot run any SQL commands since the service cannot be started, probably because mastlog.ldf was also deleted – peflorencio Dec 19 '18 at 11:55
  • @peflorencio You're going to lose all the metadata for the instance, but I've updated the answer to give you how to rebuild the system databases. – Sean Gallardy Dec 19 '18 at 12:03

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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