Sql-server – How to restore a database if full database is corrupt

sql server

I have the following strategy for my SQL Server Database:

Full backup on Sunday at 1 PM
Differential backups daily from Monday to Saturday at 1 PM
Transaction log backups daily every 3 hours

Now my last full backup got corrupt. In this case how can I restore my database?

Can I use Full backup that was taken before the corrupt full backup i.e two weeks before?

Best Answer

Database corruption can exist in the database, FULL backups and DIFF backups. This is because they contain data pages. LOG backups are different and don't contain database corruption.

All of your backups are susceptible to file corruption, but that's different. We're talking database corruption here.

If you need to do a restore due to database corruption, do one last LOG backup so that you can restore without data loss, called the tail.

Now time to start the restores. Start with the FULL backup that is before the last successful DBCC CHECKDB. If you don't have a last successful DBCC CHECKDB, you'll need to run it after each restore to see what's good and what's not.

Then apply a DIFF backup if you have one that is relevant to the FULL you just restored.

Lastly, restore the entire LOG chain since that DIFF (or FULL if you didn't use a DIFF) backup and then apply that final LOG backup (the tail).

You didn't ask this question, but I'm including it anyway. It makes sense to have your FULL backup job occur before the DBCC CHECKDB job so that you know which backups are good if you ever encounter database corruption that requires you to do a restore.

Related Question