Backup Advice

Question

Backup Advice

Ronnie Jones

Hall of Fame

Points: 3377
More actions
May 25, 2006 at 3:59 pm

#113140

Hello All,

I’m currently using Database Maintenance Plans (DMP) to backup my databases (Master, Model, MSDB/User databases) and Transaction logs. I do daily full database backups and hourly or every three hours (depending on the database activity) of the logs. I use to do weekly full and daily incremental backups and hourly logs (self written scripts) but came across someone else that was using the DMP and decided to change.

Are there pros and cons to using either solution (DMP or home grown scripts)? What are you guys doing for backups?

Thanks in advance for your help.

Ronnie

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Aaron Ingold SSCrazy Eights Points: 9070 More actions · Answer 1

It depends really... if I don't have very complex needs for maintenance on the server, I'll use a maintenance plan. If I want something a little more fine-tuned with specific needs or what have you I'll use scripted jobs.

I touched a little bit on this technique in my article:

http://www.sqlservercentral.com/columnists/aingold/workingaround2005maintenanceplans.asp

tosscrosby Hall of Fame Points: 3140 More actions · Answer 2

Also, depending on the number of databases, you're potentially going to have a lot of home grown scripts. I have one server with around 60 databases. I run maintenance plans stating "All User databases" for full backups and for hourly log backups. As new databases are added, they're automatically included in the maintenance plan. I don't have to create another set of homegrown scripts to handle the new DB.

Terry

Greg Charles SSC-Forever Points: 45403 More actions · Answer 3

We use maintenance plans for re-indexing, etc. but backups are done with homegrown jobs. We wanted to use differential backups, which aren't an option in maintenance plans. We use a combination of full, differential, and log backups in a rotating set of backup files to allow quick point-in-time recovery. We keep backups on disk for 5 days and on tape for about a year.

We wrote a script to add new databases to the backup jobs so we only have to remember to run it when we add a database.

Greg

Ronnie Jones Hall of Fame Points: 3377 More actions · Answer 4

Thanks Guys!

I guess it also depends on the size of the database as well; huh? I have a server running about 6 different server instances and there is an average of 1 database per SQL instance. Most of the databases are small averaging about a half gig to a gig.

But i have a couple instances that have DB's around 12 to 15 GB in size. I haven't changed those backups to DMP yet. I was a little nervous to convert, because I knew my homegrown scripts were working and I once had to restore and that went well too. And the data (not that anybody's data is less important than the next) is critical. I do weekly full and daily incr with the larger DB's.

I guess I really wanted to know if there were any benefits to using DMP over scripts. I know Terry had a valid point of once you configure to backup all user DB's, you don't have to worry about added a new DB to the job if you should add a new one to the mix.

So i guess in theroy, it all depends on the environment. If you have a good script that works, then it's probably no better or worse than DMP's. You just have to ensure that your logs are backing up regularly between backups, which is nothing out of the ordinary.

thanks again,

Ronnie