December 3, 2004 at 9:39 am
I am setting up a Database Maintenance Plan. I have a question about the sequence in which I should set up the Optimization, Integrity and Backup Tasks to execute. I was thinking the following sequence 1) Backup 2) Integrity, 3) Optimize. The reason I was thinking backup first is, what if something happened in the Optimization Step (i.e. rebuilding the table indexes) to corrupt the Database? Or am I being to cautious here. Should I do the backup last?
The Network Group uses Veritas to backup the entire SQL Servers nightly. There are no separate SQL Backups. This is the way it was before I arrived on the scene. The Network Group tells me if I need a Database Backup File they can provide it from the Server Backups. (I am thinking about added a Weekly SQL Backup to the Weekly Maintenance Plans.) We are not concerned about T_Log Backups and Restores.
Thanks in advance, Kevin
December 3, 2004 at 9:50 am
For what it is worth, when an intergity check is performed, the database goes into single user mode. I personally would not include an integrity check in a backup maintenance plan for this reason. I don't know what kind of uptime you are required to have, but some of my stuff is 24/7, so single user mode is a big no no for me.
I use the philosophy of backing up with one plan (fulls and trans), intergity checks and optimizations in another.
As for the Veritas backups. Does the network group have the additional SQL license? If not, your databases are not being backed up. I have my backup team exclude *.mdf and *.ldf files on all SQL servers and have them pick up the files left by my SQL LiteSpeed backups.
Hope this helps
Cody Pack
MCSE Windows 2000
MCDBA SQL Server 2000
December 3, 2004 at 10:00 am
Cody,
"when an intergity check is performed, the database goes into single user mode."
Above is only true if the database integrity is checked with repair option. Otherwise, Users are still able to access the database during dbcc checkdb running.
I would suggest to go Integrity Check first, then database backup and optimization. Be aware of that optimization could change the indexes very much and if you have large database and you perform full backup weekly and differential daily, your differential backup could become very large. In this case, you may optimize the database after integrity check and perform full backup.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply