December 6, 2010 at 12:15 pm
When Maintenance Plans are created, corresponding jobs are also created which will have the naming convention like 'MaintenancePlan.SubPlanName'. The changes that you are proposing should be made on the Maintenance Plan and not in the jobs. Because when you edit the Maintenance Plan the next time, all changes that you had done by Editing the job will be wiped out.
On a different note, please apply the latest service pack on your instance because lot of bugs of SP2 in Maintenance Plans are fixed in the later SP/hotfixes.
Pradeep Adiga
Blog: sqldbadiaries.com
Twitter: @pradeepadiga
December 6, 2010 at 12:39 pm
My recommendation is to run a daily full backup and a transaction log backup every 15 minutes, 24x7, to have the highest level of security for user data. With a full backup and transaction log backups, you can recover a database to any point in time.
You will have to set the databases to full recovery if they are not already to enable transaction log backups.
The differential backups would probably not be necessary.
December 6, 2010 at 1:07 pm
I tend to side with Michael, though if you don't want to touch what the old DBA did, you could add in transaction log backups every 15 minutes and then delete those after a couple days.
If you have the disk space, and time, here's what I would do.
1. full backup daily, say 1am. Keep 2 days worth, and if you can script this, move the old backups to another server after they are complete. There are scripts here on the site to help with that.
2. log backups every 15 minutes, copying them to another server when they are complete. You can probably delete them after a day, or 2 if you are paranoid.
3. If full's daily are too large, do differentials (incrementals) every day, and do the full once a week. Make sure you have 3 copies of this as it's important for recovery.
If people are deleting data often, you can think about automating the restore of your logs (And fulls) to another server on a delay. This is log shipping, and I'd be tempted to restore things with a 1 hour delay. That way if someone deletes something, you can go grab it within an hour from another server. This doesn't have to be a big machine, it could be an old desktop with lots of disk.
December 7, 2010 at 8:19 am
I would. It should patch things up. SP2 had a couple maintenance plan bugs that were fixed in a CU, but if you're going to patch, I'd just go and apply SP3 (after testing).
December 7, 2010 at 11:45 am
As suggested apply sp3 and then work on maint plan. In sql 2005 u can have all the maint tasks in a single plan. But as best practices it would be beneficial to have 3 plans:
1. Transaction log backup+cleanup task+cleanup history
2. Differential db backup+clean up task+cleanup history
3. Full backup's +clean up task+cleanup history
Though u mentioned the company has a large HD but more the space available better performance of HD. Proposed backup strategy:
Hourly transaction log backup (Can be hourly or multiple hours once based on your need)
Daily Differential backup - Once daily
Full backup - weekly once
Above is similar to what u'r DBA had setup. But u can add the cleanup task right after the backup step.
For full db backup - Add Intergrity check and Reindex prior to the full backup in the same job. I am sure most would agree as this the standard practice apart from customized solutions based on each companies needs.
December 8, 2010 at 8:05 am
I can see a potential issue here. You say users have lost data--but what if other users have updated the same database after that data loss? If you roll it back to retrieve the original users' data you'll lose the later modifications!
December 9, 2010 at 8:57 am
paul.knibbs (12/8/2010)
I can see a potential issue here. You say users have lost data--but what if other users have updated the same database after that data loss? If you roll it back to retrieve the original users' data you'll lose the later modifications!
My intention would be to restore the data to a separate database, and then move only those data items (rows) that were lost in the original database, to the original database. And once I've verified that everything is copacetic, I'll drop the temporary database.
Kindest Regards, Rod Connect with me on LinkedIn.
December 9, 2010 at 10:41 am
Rod,
That makes sense.
If you have something like Red Gate's Data Compare (my employer), this is very easy to do with a backup. No need to restore.
Viewing 9 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply