April 8, 2009 at 1:16 am
Michael Valentine Jones (4/7/2009)
Any backup strategy that does not include getting your database backups to tape is incomplete and a disaster waiting to happen.
Absolutely, 100% agree. Tape or somewhere else, isolated, off the server.
SAN replication is not a backup solution. RAID 5/10 is not a backup solution. Database mirroring is not a backup solution. The are all high availability options. There's a big difference.
I have seen the complete and total loss of a database (and I mean complete loss) that was on a SAN, configured with RAID 10 and SAN replication. A SAN controller glitched and spewed garbage across 2 LUNs of the SAN. Those two LUNs contained the following:
1) The primary data file of the database
2) The on disk backups
There were no offsite backups because they were waiting for space and hardware.
We could not recover that database. The damage was so sever that we couldn't even bring it online and none of the backups were restorable. Lots of people spent lots of weekends re-entering the data and re-scanning the documents that were inside that DB.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 8, 2009 at 8:29 am
Backups, Early backups, Frequent backups and verified backups
Out of interest sake, how do you go about verifying your backups?
I typically restore a backup and do a DBCC CHECKDB. My problem with this is I can only do it once a month as it is a time consuming excercise.
Does anyone have a more efficient way?
April 8, 2009 at 1:59 pm
Here is some more info and changed strategy -
I am taking SAN replication out of my discussion for now -
T- Log backup every 15 minutes (Retention period - 2 days)
Diff backup every 1 day (Retention Period - 2 days)
Full backup every 1 week (Retention Period - 2 weeks)
Drive on which backup is taken will be backed up every day. Am i still lacking something?
Thanks,
Sudhie.
April 8, 2009 at 2:52 pm
Where is that drive backed up to and how long are those backups kept for?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 9, 2009 at 12:10 pm
Grinja (4/8/2009)
Backups, Early backups, Frequent backups and verified backups
Out of interest sake, how do you go about verifying your backups?
I typically restore a backup and do a DBCC CHECKDB. My problem with this is I can only do it once a month as it is a time consuming excercise.
Does anyone have a more efficient way?
It should not be time consuming at all. It just needs to be automated. I recently wrote a blog that provides code to help you get it automated: http://weblogs.sqlteam.com/tarad/archive/2009/02/25/How-to-refresh-a-SQL-Server-database-automatically.aspx
Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
Ramblings of a DBA (My SQL Server Blog)[/url]
Subscribe to my blog
April 9, 2009 at 12:13 pm
Krishna Potlakayala (4/7/2009)
Michael Valentine Jones (4/7/2009)
Since I have a full backup every day, I usually do not do a differential backup.
Incorporating differential backups into the plan will increase the available recovery points between full backups, isn't it?
It doesn't increase the available recovery points as already mentioned, but it can help you recover faster. I recently had to restore about 200 transaction log backup files. I used a script to generate the RESTORE LOG statements for me, but a differential backup thrown in there would have allowed me to recover faster as there would have been less files to process. My backup plan now includes differential backups once a day, 12 hours after the full backup.
Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
Ramblings of a DBA (My SQL Server Blog)[/url]
Subscribe to my blog
April 9, 2009 at 3:01 pm
Tara Kizer (4/9/2009)
Krishna Potlakayala (4/7/2009)
Michael Valentine Jones (4/7/2009)
Since I have a full backup every day, I usually do not do a differential backup.
Incorporating differential backups into the plan will increase the available recovery points between full backups, isn't it?
It doesn't increase the available recovery points as already mentioned, but it can help you recover faster. I recently had to restore about 200 transaction log backup files. I used a script to generate the RESTORE LOG statements for me, but a differential backup thrown in there would have allowed me to recover faster as there would have been less files to process. My backup plan now includes differential backups once a day, 12 hours after the full backup.
hi tara,
True if you have log backups, but if you don't have log backups then it does increase the recovery points available.
Scenario1:-
Let' say you have just full backups at 1:00 AM everyday and no other backups, if a disaster occurs at 10:00 PM the only recent point of time you can go back is at 1:00 AM
Scenario2:-
You have full backups at 1:00 AM and differential backups at 12:00 midday, now if a disaster occurs at 10:00 PM the recent point of time you can go back is at 10:00PM.
so it does increase the recovery points available?
April 9, 2009 at 3:10 pm
Tara Kizer (4/9/2009)
Krishna Potlakayala (4/7/2009)
Michael Valentine Jones (4/7/2009)
Since I have a full backup every day, I usually do not do a differential backup.
Incorporating differential backups into the plan will increase the available recovery points between full backups, isn't it?
It doesn't increase the available recovery points as already mentioned, but it can help you recover faster.
Tara,
Achtang has already made my point. I was about to say the same thing. Differential backups will increase the recovery points when you are not having log backups, isn't it?
Thanks,
Krishna.
April 9, 2009 at 3:14 pm
Yes that is true, but the OP is mentioning transaction log backups every 15 minutes so that's why we are focusing on that strategy.
Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
Ramblings of a DBA (My SQL Server Blog)[/url]
Subscribe to my blog
April 9, 2009 at 3:22 pm
Tara Kizer (4/9/2009)
Yes that is true, but the OP is mentioning transaction log backups every 15 minutes so that's why we are focusing on that strategy.
Alright, true. Thanks for the clarification Tara.
April 10, 2009 at 12:17 pm
The backups are kept on tape for several weeks. I also know a yearly is taken and kept for an extended period of time.
The tapes are over written at some point and I am not sure what the exact retention is on the tapes.
I think it is at least a couple of weeks.
Viewing 11 posts - 16 through 25 (of 25 total)
You must be logged in to reply to this topic. Login to reply