November 19, 2010 at 1:36 pm
The DBA before me had a way of performing maintenance that I am not sure is completely necessary. Currently we take hourly log backups and once 12 AM hits we take a full DB backup. Once the full backup has completed we reindex/reorg and then take another full backup overwriting the original full backup taken before the index maintenance.
My question is, are the 2 full backups really necessary? If an alter index rebuild statement fails, the database will not be corrupt (I assume this is what they were trying to protect against with the backup before and after). So is it "safe" to get rid of the first full backup?
How does everyone handle backups and "maintenance"?
Thanks!!
November 19, 2010 at 2:43 pm
I would have to say that is a bit much - and you don't need to backup the database twice.
Now, as to whether you do the backup before or after the index rebuilds and other maintenance is up for debate. Some will say they want the backup done before anything else - so they can make sure the backup is done. Others will say they want the maintenance done first...
And of course it depends on your maintenance window.
I generally start with the following and adjust as needed for each system:
1) Integrity Check(s) - if this fails, nothing else gets done and alarms go off
2) Rebuild/Reorganize Indexes - if this fails, email notification goes out - continue
3) Update Statistics - if this fails, email notification goes out - continue
4) Backup Database(s) - if this fails, send alarm - stop
5) Remove Old Backup Files - if this fails, email notification - last step...
Where I don't have a large enough maintenance window - or integrity checks and backups take too long I will move the rebuilds and updates to a weekly process.
Jeffrey Williams
Problems are opportunities brilliantly disguised as insurmountable obstacles.
How to post questions to get better answers faster
Managing Transaction Logs
November 19, 2010 at 3:12 pm
I agree with Jeffrey except that if you have very large databases and not exactly a lot of disk space, you may want to do the backups before just in case the maintenance blows the database files out to a very large size and then you need to do a restore later. You may not have enough space to do the restore.
John
November 19, 2010 at 3:52 pm
IMHO: You can never have to many backups when you do major maintenance on your database. As long as you know those backups are tested and ready to restore from.
Shawn Melton
Twitter: @wsmelton
Blog: wsmelton.github.com
Github: wsmelton
November 19, 2010 at 4:17 pm
Shawn Melton (11/19/2010)
IMHO: You can never have to many backups when you do major maintenance on your database. As long as you know those backups are tested and ready to restore from.
I agree - when performing major maintenance. I do not consider rebuilding indexes and updating statistics as major maintenance.
If I am performing and upgrade on the application, service pack to SQL Server, service pack for the OS - yeah, backup before and after as these are major changes.
Jeffrey Williams
Problems are opportunities brilliantly disguised as insurmountable obstacles.
How to post questions to get better answers faster
Managing Transaction Logs
November 22, 2010 at 8:53 am
Thanks for the input. You all confirmed my thoughts, but I just wanted to see what everyone else was doing.
Thanks again!
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply