May 29, 2018 at 8:05 pm
Comments posted to this topic are about the item How Far Back?
May 30, 2018 at 8:13 am
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.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
May 30, 2018 at 8:45 am
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.
May 30, 2018 at 8:57 am
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.
May 30, 2018 at 9:05 am
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.
May 30, 2018 at 9:25 am
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.
May 30, 2018 at 11:22 am
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.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
May 30, 2018 at 1:23 pm
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.
May 30, 2018 at 11:36 pm
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.
June 1, 2018 at 9:00 am
Steve Jones - SSC Editor - Tuesday, May 29, 2018 8:05 PMComments 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, Pluralsight courses
June 1, 2018 at 9:16 am
Decades ago, as part of a murder investigation, I was asked to pull all production and sales data for a product we manufactured. For days, I was reading old weekly, monthly, quarterly, and yearly backups from reel to reel tapes. Some time later, I saw the case profiled on TV. It mentioned the importance of that production/sales data to the conviction. The recreations showed the detectives pecking at a keyboard retrieving the data; no mention the rookie programmer, sweating bullets trying to be thorough, with two detectives and a CIO standing behind him asking lame questions about everything that come up on the monitor.
June 1, 2018 at 11:25 am
Bert-701015 - Friday, June 1, 2018 9:16 AMDecades ago, as part of a murder investigation, I was asked to pull all production and sales data for a product we manufactured. For days, I was reading old weekly, monthly, quarterly, and yearly backups from reel to reel tapes. Some time later, I saw the case profiled on TV. It mentioned the importance of that production/sales data to the conviction. The recreations showed the detectives pecking at a keyboard retrieving the data; no mention the rookie programmer, sweating bullets trying to be thorough, with two detectives and a CIO standing behind him asking lame questions about everything that come up on the monitor.
Were you the one analyzing the data or just fetching and mounting backup tapes?
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
June 1, 2018 at 11:35 am
Eric M Russell - Friday, June 1, 2018 11:25 AMBert-701015 - Friday, June 1, 2018 9:16 AMDecades ago, as part of a murder investigation, I was asked to pull all production and sales data for a product we manufactured. For days, I was reading old weekly, monthly, quarterly, and yearly backups from reel to reel tapes. Some time later, I saw the case profiled on TV. It mentioned the importance of that production/sales data to the conviction. The recreations showed the detectives pecking at a keyboard retrieving the data; no mention the rookie programmer, sweating bullets trying to be thorough, with two detectives and a CIO standing behind him asking lame questions about everything that come up on the monitor.Were you the one analyzing the data or just fetching and mounting backup tapes?
Both
August 28, 2018 at 4:16 pm
Many years ago I had all Neos customers set up with database backups daily and log backups every 15 minutes. Where a customer used more than 1 server to run their Neos system, there was also transactional replication from the first server to the second . I would have been very annoyed if anything had crashed without being able to recover to a point in time very shortly before the crash - it could only happen if our local support people had broken the rules. We aimed never to have more than 20 minutes of down time after a crash for customers with multiple servers (although if there was a hardware problem the recovered system might show a slower reponse time to end users if it had to run on one less server). For single server systems which lost the server, I think we aimed at whatever time it took to get the hardware into good order plus 8 hours to rebuild, restore and validate; if the hardware was all OK the target was to have normal service within an hour. So generally speaking we had no intention of ever restoring to a time more that 15 minutes before the crash - but we did retain enough backups so that we could always recover to any time between the last but one weekly backup and 15 minutes before the crash if somehow bad data had been fed into the system. (Our system had to interact with customer's customer management systems, which were usually rather good at sending us incorrect data, or not sending us data that they was supposed to.)
I suspect that steve is absolutely right to say that recovering to a state that's more than a little bit old can have some real problems.
Tom
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply