Maintenance Plan versus Scheduled Backup Job 1

[mikelawrence]

I've been running a maintenenace plan backup and individual jobs backing up individual databases and want to know if this is necessary. The way i'm doing it now is partly down to ignorance and partly wanting to cover all bases with the downside of using up a lot of diskspace.

The downside to the maintenance plan is that it puts all backups into 1 massive file and so i can't keep say the last 5 backups and then one a week for the previous couple of months. Reading the FAQs here however say that if you backup individual dbs it will not save user defined procedures and other useful things in times of complete server loss.

I'd appreciate any advice on the best way of doing this,

Thanks

Mike

 

[mgale1]

Mike,

You should definetely be able to do what you want via the Maintenance Plan Wizard. As far as I am aware the backups generated by maintenance plans are 100% complete; that is, they do contain user defined stored procedures and all other database objects. Any backup, whether maintenance plan driven or not, will not backup scheduled jobs that may reference the database as they are server level objects not database ones.

You say that the maintenance plan driven backups go into one large file? I am not sure what you mean here but I am certain that by following the Maintenance Plan wizard you can backup the databases into individual time&date stamped files. You can choose to keep the backups for X days so that will allow you to keep the last 5 backups. You're requirement to keep one a week for 2 months may not be achievable via the Maintenance Plan wizard. Instead you may have to setup some sort of scheduled batch job to copy the backups (say every friday night) to a separate location that the maintenance plan doesnt write to or delete from.

I hope that helps