SQL database is crashed

[Former Member]

Hello,

Our server was hit by some unknown virus which seems to have corrupted header information for many of our files including an important databases and some of its backups. However the log file (.LDF) for the live DB was not affected. We also have a relatively old .BAK backup file.

Is it possible to bring the DB (.MDF) in the old backup to Live standard using the new/latest Log file? If so, how? Any help is truly appreciated.

[Former Member · Accepted solution]

Follow the steps mentioned below:

* Take a tail log backup of your database after your full backup
* Restore your Full backup available with you using WITH NORECOVERY option.
* Restore Differential backups ( if you have in order) WITH NORECOVERY option
* Restore Transactional log backup in order WITH NORECOVERY and the last tail backup taken using WITH RECOVERY option..
Also have a look this Recommended actions

https://community.office365.com/en-us/f/172/t/266070

[Former Member · Accepted solution]

But how do I "Take a tail log backup of your database after your full backup", since I don't have access to the original DB anymore.

[Former Member · Accepted solution]

Ok, If your database is not functional now, then you can't take tail log backup.
In that case, try the steps given below:

* Detach your database
* Copy your LDF File to a new location
* Attach MDF File without providing LDF file..

Test whether this works or not. If not then can try the tool SQL Server Recovery Toolbox. It is possible you will be able to help. http://www.oemailrecovery.com/sql_recovery.html

[Former Member]

Thank you guys for your feedback and help. Sorry, that long time did not respond. This is because the issue was solved and I forgot to tell.) Thank you!

[luisdarui]

Hi Michael,

This is terrible news. Unfortunate you cannot use this T-Log file with any other database files.

In addition, the T-Log only contain transaction information between the last T-Log backup and the current point - assuming yet the T-Log file is not corrupted yet.

1) Can you bring the database to a ONLINE state at least? If yes, run a DBCC CHECKDB with no_infomsgs against the database. Check the results, depending you may be able to recover the database from the corrupted state.

2) If you can't bring the database ONLINE or the database corruption is so severe (like system tables are corrupted), then your best option would restore the database. For this there are several options to follow. Data loss or not, depends on how good is your backup strategy.

e.g.

Having a very old FULL DATABASE BACKUP, does not mean that you will face data loss. In case you have all T-Log backups stored as well, you can stat your recovery from a DATABASE BACKUP taken from 3 years ago (like I said, you just have a start point, and if you have all the log chain backups since then you can until the last time your database was in a good state, you can still recovery the database.

Yuksel gave you some of resources about backup/restore. I would go further and suggest you to read on the following Blogs:

Corruption Handling in SAP Databases | Running SAP Applications on the Microsoft Platform

and also Mark Weber's blog:

General SQL Database Repair for Enterprise Applications | Mark Weber's blog

You may also contact Microsoft support. They are best skilled for handling database corruption, they offer 24x7 support (follow-the-sun) and if you don't hold contract with their support, you can still use an option for "pay-per-incident". I can say that for a database corruption no SAP skills are required for handling it.

Since the root cause of this (as you explained) is a security issue, not much I can suggest you to avoid this in the future other than improve Database Server security as well isolate the access to it. Dedicated database servers have no need to have end users accessing their file systems or network shares.

For the future, consider an efficient backup strategy along with some HA/DR solutions that would have help you to avoid all other corruption possibilities.

Best regards,

Luis Darui

[yakcinar]

Hello Michael,

First of all backup you DB + Log for the last moment. You can need it later.

If you have Full backup at some time and log backups from full backup time till now, you can restore your DB without data loss.

You can restore your DB either to your system (after backed up last situation) or to another server with SQL Server Instance with the same version of your original system.

Restore Full backup first then apply all log files.

You can use below links.

Restore a Database Backup (SQL Server Management Studio)

Backup and Recovery - SAP on SQL Server - SCN Wiki

Regarrds,

Yuksel AKCINAR