December 11, 2003 at 7:21 am
In the never-ending search for best practices, I am interested in hearing other people’s practices regarding maintenance plans (MP) and backups.
Our databases tend to be static. There are not many that have large online transactions. Databases that have increased volatility have the backups schedule increased. The default is complete backup weekly and log backups daily, as Microsoft gives as the defaults in the wizard. Databases that are changing online have complete backups daily and log backups hourly.
We currently create a maintenance plan for every database on every server. This includes a backup that goes to a separate directory structure for each database. I have created a stored procedure that creates the Maintenance Plan and the associated jobs so that every thing is created the same way each time and follows our naming standards. The SP creates a weekly reorganization, a weekly integrity check, a complete backup and an optional log backup (if the DB is not in Simple recovery mode). The directory structure lives in the BACKUP directory and is named for with the same name of the database. Under the database name there are directories for DATA, LOG and OUTPUT.
Would it be better to create less MPs by combining multiple databases within the MP? We have one database one MP policy right now. Which works pretty well until the DBA has to change all of them.
Any thoughts? How do you handle MPs and backups?
December 11, 2003 at 9:29 am
This could be an interesting thread. I'm curious how others handle this as well.
For myself I don't use maintenance plans at all. I hace a stored proc that accepts the following parameters:
@type varchar(4)='FULL', -- full, log or diff
@path VARCHAR(36)='D:\SQLBackups\DB\', -- default path
@exclude varchar(500)='' -- database(s) to exclude
I then created a job that calls this stored proc, which cycles through all databases and does either a full, log of differential backup. Log backups are not done if the database recovery model is set to 'Simple'. No backup is taken if the database is mentioned in the @exclude parameter. Most of my databases are backed up on the same schedule so this works well. Any exceptions I exclude. I also wrote a step in my job to delete old backups from the backup directory. We run ARCSERV every night to copy the backup directory to tape. I use this method so I don't have to worry about backups when new databases are added (or removed) I only have to set the Recovery model appropriately.
Francis
December 11, 2003 at 9:50 am
I'm kind of split. I like the maintenance plans for the integrity and optimization checks. Easy to schedule and setup and document. We go with 2 plans, one for system dbs and one for all others.
As far as backups. I try to keep that as simple as possible. We run lightspeed, so we have 3 procs we add to every server that do full, diff, and log backups. Each proc is the same and runs the backup named for the time and date and places each database's backups in a separate folder as in the top item above. We basically schedule a full once a week, diffs every other night and logs every 15 minutes.
That's standard. The only exceptions are dbs that do lots of bulk inserts (and tlogs fail) or a few systems that have millions of transactions that aren't important. Our virus deployment server is like that. The info is basically rebuilt every 15 minutes, so we don't need to recover to a point in time. For exceptions, we add the db name to the exception list in the procedure (where model, master, pubs, etc are listed). The same procs for full and diff handle master and msdb.
Steve Jones
http://www.sqlservercentral.com/columnists/sjones
The Best of SQL Server Central.com 2002 - http://www.sqlservercentral.com/bestof/
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply