June 29, 2011 at 6:03 am
Ninja's_RGR'us (6/29/2011)
That would imply that you don't care about the data changes between the full backups. So in the event of a disaster you lose ALL the data since the last full backup. So if you backup only once a week then be prepared to lose 5 full days of work and make sure your boss signs on that.
No, we Backup every night through "Database Maintenance Plans" (which is set to delete any backup files that are older then 2 days). So if there should not be any issues or if there are any disasters that might occur we should fine.
Do you suggest other ways that I should change or do things please.
Thank you so much for all your help.
June 29, 2011 at 6:26 am
Here's what I have setup here which is about as safe as can be. Our DB is critical, but we can afford lose some data because it's an ERP and there's a big paper trail for everything. However it would be a freaking pain in the a$$ if we lost even a couple hours of data.
ERP DB ±20 GB + 10 GB / year. 60+ full time users in the system + 15 users on SSRS server
Sql 2005 standard 32 bit on virtualized server 4GB ram, same setup for test and reporting server
SAN 1 : Windows, Prog files, Data and log files
SAN 2 : Full backups and log backups
Daily maintenance
Log backups 24 / 7 / 365 every 15 minutes with verify (only 99% safe which is why I also do a full restore / checkdb of those later in the sequence)
Midnight strikes >>
Reload [mini-DW] tables
Selective reindex based on fragmentation (http://sqlfool.com/2010/04/index-defrag-script-v4-0/) I have it set up to REBUILD à 10% frag (runs faster than reorg here). That's the only major setting I changed from the basic setup.
Update ALL stats with fullscan. This causes a 0.1 % overlap after the index rebuilds but I don't care about those 5 extra seconds.
For all our dbs >>
CheckDB() >> on any errors disable the delete old backups jobs
Full backups
Restore backup + checkDB >> any errors, disable delete old backups and warn IT
Delete full backups over 3 days old and ALL associated log backups (logs I couldn't use any a restore after the delete of the full)
// end all dbs mode
During lunch and after business close, restore last full + ALL logs. Check DB()...
>>> this script also gives me a change to script a PIT restore in 5 seconds flat! The script is based on the files available in the backups folder. There's something similar in SSMS but it's based on msdb backup history. So if I lose that DB I can still restore in no time flat without manually restoring 97 files. Also back in sql 2000 that table was slow as hell when the history was big. I don't know if this is fixed in 2k5.
Backup folder copied daily to a 3rd SAN (diff backup here. That gives us 7-8 days PIT recovery in the past).
Everytime we update something on the server we take a backup of the VM as well.
That full sequence runs in less than 2 hours on a 8-10 hours window... while the sans are busy doing other backups.
That's about as safe of a setup as I've ever seen anywhere.
June 29, 2011 at 1:10 pm
tt-615680 (6/29/2011)
No, we Backup every night through "Database Maintenance Plans" (which is set to delete any backup files that are older then 2 days). So if there should not be any issues or if there are any disasters that might occur we should fine.Do you suggest other ways that I should change or do things please.
So if the drive fails 5 min before the nightly backup, you lose an entire day of data and that's fine?
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
July 4, 2011 at 5:11 am
GilaMonster (6/29/2011)
tt-615680 (6/29/2011)
No, we Backup every night through "Database Maintenance Plans" (which is set to delete any backup files that are older then 2 days). So if there should not be any issues or if there are any disasters that might occur we should fine.Do you suggest other ways that I should change or do things please.
So if the drive fails 5 min before the nightly backup, you lose an entire day of data and that's fine?
No, losing entire day of work would be a disaster. What I meant was, because we keep 2 days of worth copies of backups and there are any disasters, we would be able to recover from them.
Thank you
July 4, 2011 at 5:16 am
but without the t-log backups you will lose a days worth of data if there is a disaster. since you do not have the ability to do PIT restores.
July 4, 2011 at 5:19 am
steveb. (7/4/2011)
but without the t-log backups you will lose a days worth of data if there is a disaster. since you do not have the ability to do PIT restores.
+1000
July 4, 2011 at 5:20 am
steveb. (7/4/2011)
but without the t-log backups you will lose a days worth of data if there is a disaster. since you do not have the ability to do PIT restores.
Sorry if I am being silly but even if the Database is set to Simple recovery that I would have to Backup Transaction logs?
Thank you
July 4, 2011 at 5:26 am
tt-615680 (7/4/2011)
steveb. (7/4/2011)
but without the t-log backups you will lose a days worth of data if there is a disaster. since you do not have the ability to do PIT restores.Sorry if I am being silly but even if the Database is set to Simple recovery that I would have to Backup Transaction logs?
Thank you
No it's impossible to take log backups in simple recovery.
Tho it's the same problem, you can lose up to 24 hours of data in between the backups. If that is considered a catastrophe to your company then you need to move to full and start taking log backups every x minutes ( I do then every 15 minutes here).
Make sure that the backups are on a different set of drives than the data and log so that if you lose 1, then you can recover.
And then backup the backup folder to a 3rd set to make extra sure.
July 4, 2011 at 6:11 am
Ninja's_RGR'us (7/4/2011)
tt-615680 (7/4/2011)
steveb. (7/4/2011)
but without the t-log backups you will lose a days worth of data if there is a disaster. since you do not have the ability to do PIT restores.Sorry if I am being silly but even if the Database is set to Simple recovery that I would have to Backup Transaction logs?
Thank you
No it's impossible to take log backups in simple recovery.
Tho it's the same problem, you can lose up to 24 hours of data in between the backups. If that is considered a catastrophe to your company then you need to move to full and start taking log backups every x minutes ( I do then every 15 minutes here).
Make sure that the backups are on a different set of drives than the data and log so that if you lose 1, then you can recover.
And then backup the backup folder to a 3rd set to make extra sure.
Thank you.
Viewing 9 posts - 16 through 23 (of 23 total)
You must be logged in to reply to this topic. Login to reply