SQL server backup retentions

pma111
pma111 used Ask the Experts™
on
what kind of formulas determine how long you should retain your SQL Server database backups for? Our admins seem to be using a variety of different retention schedules for backups of different critical databases, some databases there are 1 weeks worth of SQL backups, others there are 6 weeks worth. I am trying to determine how exactly you would determine how long to keep database backups for, and for example have you ever restored data from say a 6 week old backup, which would surely incur huge data loss? Or under what circumstances would you need to keep backups for longer than say 7 days? I guess the same principle applies regardless of database platform hence adding it to other DB topic areas.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Shaun KlineLead Software Engineer

Commented:
IMO, backup retention is based on the industry you are in, whether your company is public or private, or whether your company is highly regulated.

At my current employer (private), we keep daily full backups of our transactional databases for 60 days. I have restored a database that was older than 30 days, as I needed to see how a customer processed a transaction to determine if they caused the issue or the application did.


At a previous employer (public), they kept a year's worth of monthly full backups, weekly backups for a month, and daily backups for a week. They were highly regulated.
Éric MoreauSenior .Net Consultant
Top Expert 2016

Commented:
there is a big "that depend" disclosure, there is no unique answer here.

you may have some laws or compliance that will dictate for how long you need to keep your data/copies

I am a big fan of keeping:
-daily backups for 40 days
-monthly backups for 15 months
-annual backups for 5 years

I have already restored a 2 years old database because the auditors needed to see some data (changes of addresses, ...).
Fractional CTO
Distinguished Expert 2018
Commented:
This is a complex question.

The current crop of Malware is very smart. Attempts are made to quietly pollute files + database rows with Malware payloads, then wait weeks to months before the Malware fires.

For this reason, I now keep one year's worth of backups, so I can go back through time, if required, to determine exactly what has been injected into database records at any given point over a year.

This might seem like overkill... until you require data many months old...

Disks are super cheap now (16TB for $500 USD), so building a large RAID array is trivial.

Better to have the data + never need it... then need it + not have it...

Author

Commented:
>The current crop of Malware is very smart. Attempts are made to quietly pollute files + database rows with Malware payloads, then wait weeks to months before the Malware fires.

that is interesting, have you any articles (or details of the malware in question) on such malware that specifically targets databases and waits in situ before executing. It would be an interesting read.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial