| Author |
Topic |
|
wkdelaney
Starting Member
17 Posts |
 Posted - 2006-05-03 : 16:46:22
|
Hello,Multiple schedule database backups are running on a single database. Every morning I look in the backup folder and there are several backups that have ran. one at 9:30pm another at 3:00 am etc...Looks to me someone has ran the database maintenance planner wizard serveral times without knowing they already had a scheduled backup plan going. I need to reduce this to one scheduled backup. Can someone tell me how to remove or stop the others using EM.Thank you,William  |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2006-05-03 : 16:56:03
|
| Check out all of the maintenance plans. Then check out all of the jobs. If you delete a maintenance plan, the associated job will get deleted too. But you don't have to use a maintenance plan to backup a database, so it could be a custom job as well.Tara Kizeraka tduggan |
 |
|
|
wkdelaney
Starting Member
17 Posts |
Posted - 2006-05-03 : 17:07:02
|
| Tara,I couldn't find where to edit the maintenance plans and jobs. I found the menu for database maintenance planner under tools. I looked all over the menus for a way to see them listed. I must be blind! Where in EM can they be edited?Thanks,William |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2006-05-03 : 18:07:47
|
| You just double click on them. Do you have anything list in the right pane after you click on Management..Database Maintenance Plans? If not, then your backups aren't being created from the plans. You'd have to check the jobs under Management..SQL Server Agent..Jobs to see if any backups are being created from there. If they aren't, then you most likely have a third party tool doing it.Tara Kizeraka tduggan |
|
|
|
wkdelaney
Starting Member
17 Posts |
Posted - 2006-05-03 : 18:58:08
|
| Tara,Thank you! There are two plans listed in the right pane. one for 9:30pm and the other for 3:00am. I checked them out and they were identical on the way they were setup. I removed the 3:00am and kept the 9:30pm. I need to ask you another question? These are the settings for the backup. Are these going to save me if I have to do a restore?Under the General Tab: (Checked)All users database (not master, Model and msdb)Under Optimizations Tab: (checked)Reorganize data and index pages -Change free space per page: 10%Remove unused space from database files - 50mb - 10% of data spaceSchedule every (1) day at 8:30pmUnder Integrity Tab: (checked)Check Database IntegritySchedule every (1) day at 8:45pmUnder Complete Backup Tab: (Checked)Backup database as part of Maintenance PlanVerify integrity of backupSchedule every (1) day at 9:30 pmUnder Transaction log backup: (nothing is checked)Thanks for any help,William |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2006-05-03 : 19:05:34
|
| It depends. Do you require point in time recovery? Meaning if your backup was at 6am and you had a major failure at midnight, can you afford to lose 18 hours of data?Tara Kizeraka tduggan |
|
|
|
wkdelaney
Starting Member
17 Posts |
Posted - 2006-05-03 : 19:48:45
|
| I see what you mean!No, I can't afford to loose 18hrs of data. Looking at at the backup in place. What would be the quickest and easiest way to make sure I can restore from the point of failure? Shouldn't Transaction logs be backed up also? Any Ideas?Thanks,William |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2006-05-03 : 20:32:44
|
| You need to backup your transaction logs for the user databases. We backup ours every 15 minutes. You'll need to switch to FULL recovery model to start this process. This will allow you to have point in time recovery. You can read about this in SQL Server Books Online. You also need a new maintenance plan that backs up the system databases. They should be backed up daily. What is the size of your database? If it's small (less than 10GB or so), then you've got some reading to do. Your reading might lead you to not running the optimizations part. Here's my weblog on it. Make sure check to the MS article.http://weblogs.sqlteam.com/tarad/archive/2006/04/11/9585.aspxSomeone needs to be checking the status of the jobs daily. If ever the integrity job fails, you should run DBCC CHECKDB in Query Analyzer and post the results in the Data Corruption forum here. MS engineers frequently check that forum to help users out with corrupt data.Tara Kizeraka tduggan |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2006-05-04 : 03:56:19
|
| "All users database (not master, Model and msdb)"Good! Any new database you create will automatically be included in the plan"Under Optimizations Tab: (checked)Reorganize data and index pages-Change free space per page: 10%"Not so good! This will already have converted any indexes you have which were set to 100% fill to 90%. On large tables with clustered index on an identity table this will be causing significant amounts of effort during the index rebuild, and will be leaving 10% of the index empty ... Change it to the other setting (something like "Leave the index at the original setting"). The existing indexes will have to be reset manually I'm afraid - don't fret on that unless you discover that there is a genuine problem."Remove unused space from database files- 50mb- 10% of data space"Also bad I reckon! This looks & smells like SHRINK to me, and I don't reckon you need that - it will cause file fragmentation and probably slow the system down reacquiring space that it acquired yesterday but was released during last nights reorganisation."Schedule every (1) day at 8:30pm"Everyone finished work then? If not move it to a "quiet time""Under Integrity Tab: (checked)Check Database IntegritySchedule every (1) day at 8:45pm"Ditto about the time"Under Complete Backup Tab: (Checked)Backup database as part of Maintenance PlanVerify integrity of backupSchedule every (1) day at 9:30 pm"Ditto about the time"Under Transaction log backup: (nothing is checked)"As Tara said you should add this one - either hourly or every 15 minutes are popular choices. It will use the same amount of disk space whichever you choose, but you stand a better chance of restoring closer to a fail-point with the 15 minute option. Only downside is you have 4 times as many files to restore in the event that you need to restore - minor price to pay IMHO!Pay attention to how long the files are retained. its probably only worth keeping the Transaction Logs for a couple of days. The Full backups you may want to keep for longer - e.g. a week. That way you can restore (e.g. to a temporary database) and "have a look" without having to first get the backup off tape.Make sure the folder where your backups are made is backed up to tape, and that he files are getting there - a Fire Drill to recover a backup from tape is a Good Thing! (and you probably want to EXCLUDE the folder(s) where the SQL Server DATA files are kept - they will be "in use" when backed up, and useless if you attempted to restore them, so just a waste of time trying to back them up).Kristen |
|
|
|
wkdelaney
Starting Member
17 Posts |
Posted - 2006-05-04 : 22:25:57
|
| Tara and Kristen,Thats exactly the info I was looking for. What a great feeling to know that such smart people are willing to help all of us who just don't know what we don't know.Thanks to both of you for being kind enough to answer such a dull question.William |
|
|
|
|
I need to stop multiple scheduled Backups