0

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?

Improve this question
1
  • Why would you want to restore from an older backup--or at all? What's your RTO and RPO? What caused the backup to be corrupt... or have follow on backups (copy only for example that you can run manually) also resulted in corruption? Is the DB itself corrupt? Have you ran DBCC CHECKDB? I ask these questions to prevent you from doing something that isn't reversible, and to make sure you don't have an XY Problem.
    – S3S
    Commented May 14, 2018 at 17:55

2 Answers 2

Reset to default
1

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.

Improve this answer
0

As long as no other backups are corrupted also then the restore process is

1. Restore last full backup before corruption
2. Restore most recent differential backup following (1)
3. Restore log backups in order after the differential backup
Improve this answer
3
  • Just for clarity, it would be the last differential backup that occurred before the corrupt full backup.
    – MrTCS
    Commented May 14, 2018 at 18:08
  • @Mazhar, the most recent differential would contain the corruption too. Need the diff from before the corruption and then the entire log chain.
    – Tara Kizer
    Commented May 14, 2018 at 18:37
  • before attempting to restore, it will be beneficial to run a DBCC CHECKDB on the database to determine corruption level. You would be better off identifying the root cause of corruption first. This could be disk subsystem issue which could still corrupt the restore database. Start of with database integrity check and see what options you have available. Remember you can repair the corrupt database and lose lesser data than doing a restore from an older backup.
    – samosql
    Commented May 14, 2018 at 18:49

Your Answer

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