December 6, 2010 at 10:45 am
Our DBA left us more than a year ago. However, before he left he did I think a good job in setting up the various maintenance tasks and jobs that keep our SQL Server server running. And fortunately, for me, we're a small shop and our database server needs are small. I work as a senior programmer analyst, so tat is my primary function, but I've become the default DBA, after the former DBA left. I worked with him the closest, so I knew more than the rest of my colleagues what was going on, etc.
In looking at our users' needs, it seems to me as though we would better serve them if our database backups were provided more frequently. For example, the former DBA set up a full backup job that runs once a week, and then incremental backup jobs that run daily. Honestly, this has been the way it has been here, since this business started almost 15 years ago. However, I've seen our users loose data, and if the incrementals were run more frequently, I think we could recover some of their lost data. I've proposed changing our backup strategy to a full backup each day, and incremental backups done hourly, at least during business hours. For our situation, and given the workload done by our users, I believe this change would significantly help in recovering data that may get lost.
The thing for me, though, is I'm not entirely sure how the former DBA set things up so they would run as they do. For example, he set up a Maintenance plan (something we'd never done before), with 6 sub-plans. Recently, I added a new Maintenance plan, to remove old backup files (.BAK and .TRN) from the server. He hadn't done this, and so we had backup files going back for a few years left on the server. (Fortunately, the server's HD is large and our databases are relatively small!) My new maintenance plan purges all .bak and .trn files older than 8 weeks, and I've checked it the last couple of weeks and it seems to be working fine.
Anyway, here's some of the relevant sub-plans in the former DBA's maintenance plan: weekly-full-backup, daily-incremental-backup, weekly-incremental-backups (this performs a differential backup of the databases and then a transaction log backup). (In all of this discussion I'm only talking about user databases, not system databases. The system databases are also being backed up on a weekly basis, and I think that will be fine for now.) My problem is I don't see how the jobs in SQL Jobs are related to the maintenance plans in Maintenance. I'm guessing that the change that I'm proposing to make would have to be done in the SQL Jobs area, not in the maintenance plans. (I should update the description of the maintenance plans, indicating the change, since the former DBA's choice of name of the maintenance plan and descriptions indicate that the maintenance plan/description is weekly and daily, whereas I'll want to make them daily and hourly.)
Anyway, how does one related to the other?
We're using SQL Server 2005 with SP2.
Kindest Regards, Rod Connect with me on LinkedIn.
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.
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
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).
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
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.
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply