| Author |
Topic |
|
lenovo27
Starting Member
15 Posts |
 Posted - 2010-05-19 : 11:43:04
|
| I have reviewed seemingly every setting for the maintenance plan to automate/schedule the backup process of my database. It just doesn't work!Any one have any information on this? Is this another Microsoft bug? |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
lenovo27
Starting Member
15 Posts |
Posted - 2010-05-19 : 12:15:11
|
| Yes, I have, probably 20 times in the past two days. I just found out that the SQL Server Agent isn't running, which is apparently what is causing this problem. Trouble is, the SQL Server Agent won't freaking start! Argh!!! I've looked all over the internet and have yet to figure out why it won't start, manually or automatically it just won't start. |
 |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
lenovo27
Starting Member
15 Posts |
Posted - 2010-05-19 : 12:55:21
|
| I checked that as well, there is no error in the application log. No errors in the SQL server agent log either. I switched from Network logon to Local logon and that allowed me to start the service. I ran the backup but it failed again, HOWEVER, this time it was due to a different error, no mention of the SQL Server Agent not running. SO I deleted the maintenance plan and created a new one. I'm waiting for it to execute right now....And it failed again! No mention of an issue with the SQL Server Agent at least. Let me check the application log files and see if there is a new error there. |
|
|
|
lenovo27
Starting Member
15 Posts |
Posted - 2010-05-19 : 13:45:39
|
| No errors in any log files. WTF? At least the agent is now running, but the backups are not working, not from executing it as a job or as a maintenance plan. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
lenovo27
Starting Member
15 Posts |
Posted - 2010-05-19 : 17:55:00
|
| I'll give your script a shot on my server at home. I finally got this to work with the help of a couple of hard core database experts here. SQL Server agent was the first problem, which I had solved earlier. The other problem turned out to be that the Integrated Service Utility was not installed during our database upgrade earlier this year. Once that was installed, the backups worked fine.One thing I have noticed is that I cannot backup to a network share. That sucks, is there a service pack that fixes this? Some of this stuff is really disappointing. I spent a long time configuring Windows 2008 Server at home only to find out that you cannot do a SCHEDULED backup to a share. You can run a manual backup to a share, just not a scheduled one. I was even more disappointed to find out that there is not upgrade path from my 32 bit standard edition to 2008 R2.WTF? |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
jeffw8713
Aged Yak Warrior
819 Posts |
Posted - 2010-05-19 : 21:01:00
|
| With all due respect Tara - maintenance plans work just fine. Yes, there have been issues with them on 2005 pre-SP2 systems, but they work just fine.And no, backing up to a network share does not work fine if the SQL Server service account is running under the local system account. To backup to a network share, you have to be running using a domain user that has privileges to the share. Whether or not you use a script or maintenance plan.Now I will say that the way some of the tasks have been designed is just messed up. I don't use the reindex or rebuild tasks - I use an Execute SQL Task and call out to custom procedures. I also do not use the Update Statistics task - instead I use an Execute SQL Task and execute sp_updatestats using the resample option.Could I do this all with agent jobs, yes - but the maintenance plans work just fine. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
Kristen
Test
22859 Posts |
Posted - 2010-05-20 : 15:15:59
|
| Well I agree with both of you!I don't use the maintenance plans, but I think they are a better bet for someone inexperienced trying to look after a small database who doesn't have any SQL/DBA skills and is going to be more comfortable - and likely to succeed! - with a GUI interface.But, Jeffrey, the point I would make about maintenance plans for backup is that when something goes wrong the user is completely stuck - the messages are not designed to point an accurate finger at the cause, its left to the user to try to reverse engineer the problem!What is needed, particularly for a numpty user, is a completely bullet-proof backup tool where all they have to do is describe the path where they want the backup files stored, the retention period, and frequency of backups from a simple list that guides the user to a sensible choiceFor SIMPLE Recovery Model:Once a dayMore often ...or Once a day + some DIFFs during the dayFULL Recover Model:Full backup once a day for and TLogs every 15 minutesFull backup once a week, daily DIFFs and and TLogs every 15 minutesFull backup once a week, two DIFFs during the day and and TLogs every 15 minutesthen choose a slack time (e.g. 10pm to 5am) for all the housekeeping to happen then (and I expect it to work out for itself what indexes are most fragmented etc. and do them first, and then stop if the end of maintenance window comes round)Ability to say if there is a longer maintenance window at the weekend.That's it. In fact if it worked like that we'd all(ish!) be using it instead of having to spend time writing our own ...Given the number of SQL installations, and the development power of MS, I'm surprised it hasn't happened yet ... |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
Kristen
Test
22859 Posts |
Posted - 2010-05-20 : 15:50:52
|
| I'm not succinct like you! I tend to waffle on and on and ...... I'll get my coat now! |
|
|
|
|
Scheduled backup not working
Tara Kizer