SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


How Far Back?


How Far Back?

Author
Message
Steve Jones
Steve Jones
SSC Guru
SSC Guru (610K reputation)SSC Guru (610K reputation)SSC Guru (610K reputation)SSC Guru (610K reputation)SSC Guru (610K reputation)SSC Guru (610K reputation)SSC Guru (610K reputation)SSC Guru (610K reputation)

Group: Administrators
Points: 610948 Visits: 21175
Comments posted to this topic are about the item How Far Back?

Follow me on Twitter: @way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
Eric M Russell
Eric M Russell
SSC Guru
SSC Guru (110K reputation)SSC Guru (110K reputation)SSC Guru (110K reputation)SSC Guru (110K reputation)SSC Guru (110K reputation)SSC Guru (110K reputation)SSC Guru (110K reputation)SSC Guru (110K reputation)

Group: General Forum Members
Points: 110032 Visits: 14724
In past jobs, there were many situations where the business wanted to know the data behind a report that was produced on a certain day in the past, maybe even a year or more in the past, and the database they were using for reporting purposes simply didn't have any data change capture, versioning, or online archiving capability. Sometimes this request was the result of an internal or external audit. For this reason, they would request a restore to a staging environment that was as close as possible to the time the report in question was produced. This scenario was far more common than restoring for the purpose of disaster recovery, and every DBA needs to have a plan in place to facilitate it.

If an organization has something like a month end or other periodic cycle, then for backups older than three years, they should at least retain a backup that correlates to each reporting period. Also, if they are in the business of publishing reports for external consumption, then they need to archive the data used to produce those reports.


"The universe is complicated and for the most part beyond your control, but your life is only as complicated as you choose it to be."
Rod
Rod
One Orange Chip
One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)

Group: General Forum Members
Points: 28716 Visits: 2742
In my current position I don't handle backups nor restores. That's others' job. In my previous job, until the university IT department took over those responsibilities, we would hold onto backups for about 3 months. We'd rotate through the backup tapes, placing some of them off-site. Because I've been a member of SQL Server Central for several years, I learned the importance of testing restores. I can remember doing two such tests. (They worked fine.) I probably should have done more, not because we had a problem, but more just for the practice so that in an emergency I wouldn't be flustered.

However, if I were to do it to the data we have in my current job, WOW, that's hard to estimate. Working for a large state department there is so much data of various types, I don't believe you can say something like, "All backup data must be retained for 3 years." I'm guessing that, due to some regulations on certain data, some would have to be retained for longer periods of time than others. I've never asked the DBA's about the backup schedules they follow, but I bet they're complicated.

Kindest Regards,Rod
Connect with me on LinkedIn.
ZZartin
ZZartin
One Orange Chip
One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)

Group: General Forum Members
Points: 26102 Visits: 17450
Depending on the application it can be very common to find a problem that happened months ago and has been persisted in the data for a long time. At some point the business might just have to eat an error since infinite backups really isn't possible but particularly if you are dealing with any kind of persistent processing a month is a very short window to maintain.
Steve Jones
Steve Jones
SSC Guru
SSC Guru (610K reputation)SSC Guru (610K reputation)SSC Guru (610K reputation)SSC Guru (610K reputation)SSC Guru (610K reputation)SSC Guru (610K reputation)SSC Guru (610K reputation)SSC Guru (610K reputation)

Group: Administrators
Points: 610948 Visits: 21175
I think errors will occur, and often I'm not sure of the point of tracing back through restores to find out. It's likely not worth the effort. Even if you have reports for the past, I might learn towards some sort of temporal table type structure rather than restores, though even then I'd trim off older data.

Maybe it's me, but I think often someone wants a CYA without any organizational value.

Follow me on Twitter: @way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
Jeff Mlakar
Jeff Mlakar
SSCrazy
SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)

Group: General Forum Members
Points: 2153 Visits: 639
Outside of rare legal reasons I think most database restores will be somewhat contemporary - hopefully close to the present as we can.

The right to be forgotten is interesting when considering backups because the inclination of organizations is to keep the data forever. Collect it all - keep it all. The regulation or market condition would have to be quite serious to change that mindset. Especially if your business is to analyze old data sets.
Eric M Russell
Eric M Russell
SSC Guru
SSC Guru (110K reputation)SSC Guru (110K reputation)SSC Guru (110K reputation)SSC Guru (110K reputation)SSC Guru (110K reputation)SSC Guru (110K reputation)SSC Guru (110K reputation)SSC Guru (110K reputation)

Group: General Forum Members
Points: 110032 Visits: 14724
Restoring a database in production, even just a point in time restore from log backups, can potentially cause more harm than good. It's always seemed to me that the vast majority of requests to restore a database involve recovering data that's limited in scope, like a specific table.


"The universe is complicated and for the most part beyond your control, but your life is only as complicated as you choose it to be."
Tom Uellner
Tom Uellner
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1586 Visits: 2039
I've only had to restore twice at my current job. Once when a developer dropped a table. That was reference data and restored from the previous nights backup. The other was a table was corrupt in production and I again had to get the data from the night before.

Our current backups are copied from the network to an off-site location. I believe they keep 7 days, end of month and end of year. At the end of the year all the monthly backups for that year are deleted. At least that is the last I was told. I'm not given any insight into backup retention. That's all the networking guys domain.
Sean Redmond
Sean Redmond
SSCarpal Tunnel
SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)

Group: General Forum Members
Points: 4623 Visits: 1052
Our IT department handles the storing of backup files once they are made. We take log backups every 15 minutes and full backups daily. The backups from the last 3 days are easily available to the DBAs and after that they are stored on tape. I also make year-end backups and I keep these for DBA-use on a share.

On occasion I've had to do restore of the year-end backups from many years' previous. A client was sure that he had made an application in 2012 and it was no longer in the database. We rarely delete productive data, rather it is marked as deleted. The backup from 2012 showed other applications that he had made but not the one in question. It was an answer, if not the one he wanted.
Gerald Britton
Gerald Britton
SSChampion
SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)

Group: General Forum Members
Points: 12175 Visits: 1828
Steve Jones - SSC Editor - Tuesday, May 29, 2018 8:05 PM
Comments posted to this topic are about the item How Far Back?

At my place (large financial business), we're required to keep monthly backups for 7 years, have a machine ready to restore any given backup to and test them regularly. We archive backups to tape (Tivoli) so the process can be a little slow. It works though.


Gerald Britton, MCSE-DP, MVPToronto PASS Chapter
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum








































































































































































SQLServerCentral


Search