Pros & Cons of backups using Maint. Plans
-
Greetings. I am fairly new to SQL Server and I was shown how to do backups as part of a maintenance plan. Subsequently I took a SQL Server class in which the instructor recommended using the backup tool rather than maintenance plans to set up and schedule backups. I have also been reading other sources and the suggestions there don't use maintenance plans for backups. I am very interested in hearing others suggestions comparing using the backup tool as opposed to a maintenance plan.
Thanks.
-
Personally I have not seen a use for a backup tool since the maintenance plans have given me all I needed.You should be able to compare from your results
Mike
-
I can't compare the two and I don't use the maintenance plans. Why not? I like to control what is done and when. I recently had a vendor do an update that really messed things up. I could no longer put the database in FULL recovery mode. So my transaction log backups failed. Since I don't use a maint. plan, I just disabled my transaction log backup jobs and everything else kept working. I think a maint plan would have completely failed (but I'm not sure) and I would have had to recreate it without the t-log backups. I can also easily change the schedule of any of my jobs without affecting other jobs.
(BTW-I explained to the vendor why the change they made was a BAD idea and they fixed it. So I just turned my t-log backup jobs back on.)
-SQLBill
-
The maint plans tend to be difficult to fault find when they fail - the usual error reads " sysmaint.exe failed " Very useful!!
I'd advise not to use them - write it yourself as SQLBill suggests.
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/ -
I am all for the use of maintenance plans! They are a built in tool and are very easy to use. To find the history of the jobs when they fail, all you need to do is right click on the maintenance plan or you can have a report emailed to you with the details. In our environment, we are adding new databases all the time and having a maintenance plan set up to backup all user databases saves us the work of having to create a new job for each database and also protects us from having databases out there without any backups. Just my 2 cents...
-
Maintenance plans work well for most databases in SQL 2K. The exceptions are large multi-gb databases and databases with high-availability requirements.
If your database has to be available 24/7 and has a fairly consistent transaction volume, you'll want to roll-you-own index defrag. The standard reindexing done by sqlmaint will tend to lock the tables and cause application timeouts.
As for finding out what the error was when one of the tasks fail, just right-click on 'Database Maintenance Plans' and view the Maintenance Plan History. You'll get a dialog much like the SQL Agent Error log where you can filter for failed tasks, servers, databases, etc...
Hope this helps
Phill Carter
--------------------
Colt 45 - the original point and click interface
--------------------
Colt 45 - the original point and click interface
-
I'll chime in FOR the Maintenance Plans, but there are issue's.
The first, and most common is you have to understand what the wizard is doing under the covers. This is true of most EM based functions, you can get into trouble if you don't know what it is really doing.
You can always go into Management -> Agent -> Jobs and stop, disable or run any job individually as needed.
I also hate the way the wizard sets all of the jobs to run as me, not SA, so I go in and change them first thing.
There are some DBs I do full index reorgs via the Wiz, some I do manually(manually created jobs), and some I may use both.
The history functions are great, although even if you create a job manually you can see the history.
Yes the errors messages are at best a clue, but generally useless.
This only applies to the general population of DBs, those that have special needs, VLDB, 24x7, hi performance, ... will need special care and feeding.
One personal note, if you use the Wiz, work your schedule to backup first. I would rather backup a corrupt DB then try to reorg the indexes or shrink or ??. At least I have a backup.
Tools like this are what set SQL Server ahead of other DBMSs. Today (don't flame me) any DBMS can handle almost any load. Particularly if the app and DB designs are half way decent. And base costs are comparable, well Oracle costs an extra arm. But with built in tools, like maintenance plans, I can manage many more DBs than with the others.
KlK, MCSE
Edited by - kknudson on 12/15/2003 08:06:29 AM
KlK
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply