SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 SQL Server Administration (2008)
 Scheduled backup not working
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

lenovo27
Starting Member

15 Posts

Posted - 05/19/2010 :  11:43:04  Show Profile  Reply with Quote
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

USA
36841 Posts

Posted - 05/19/2010 :  12:12:03  Show Profile  Visit tkizer's Homepage  Reply with Quote
Have you tried to delete the plan and recreate it? That's something that I've seen people here to do to get around problems.

I don't ever use maintenance plans as they are buggy and harder to troubleshoot. If you are interested in using custom code instead, here's my backup script: http://weblogs.sqlteam.com/tarad/archive/2009/12/29/Backup-SQL-Server-2005-and-2008-DatabasesAgain.aspx

Please note that you don't need to review the code or understand what's it's doing. You just need to exec and pass it the appropriate parameters. Based upon email that I've received, it's being used around the world. I've got it setup on hundreds of servers where I work.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog
Go to Top of Page

lenovo27
Starting Member

15 Posts

Posted - 05/19/2010 :  12:15:11  Show Profile  Reply with Quote
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.
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
36841 Posts

Posted - 05/19/2010 :  12:23:25  Show Profile  Visit tkizer's Homepage  Reply with Quote
The Application Log in Event Viewer will have an error if the Agent won't start. Locate that error and post it in this thread.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog
Go to Top of Page

lenovo27
Starting Member

15 Posts

Posted - 05/19/2010 :  12:55:21  Show Profile  Reply with Quote
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.

Edited by - lenovo27 on 05/19/2010 12:57:57
Go to Top of Page

lenovo27
Starting Member

15 Posts

Posted - 05/19/2010 :  13:45:39  Show Profile  Reply with Quote
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.
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
36841 Posts

Posted - 05/19/2010 :  15:09:43  Show Profile  Visit tkizer's Homepage  Reply with Quote
Try doing the backups through my stored procedure or via BACKUP command.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog
Go to Top of Page

lenovo27
Starting Member

15 Posts

Posted - 05/19/2010 :  17:55:00  Show Profile  Reply with Quote
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?
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
36841 Posts

Posted - 05/19/2010 :  18:02:50  Show Profile  Visit tkizer's Homepage  Reply with Quote
Scheduled backups and manual backups to a network share work fine in my custom code. Maintenance plans suck, hence the reason for writing custom code.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog
Go to Top of Page

jeffw8713
Aged Yak Warrior

USA
794 Posts

Posted - 05/19/2010 :  21:01:00  Show Profile  Reply with Quote
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.
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
36841 Posts

Posted - 05/20/2010 :  13:12:01  Show Profile  Visit tkizer's Homepage  Reply with Quote
I see too many posts here on SQLTeam with people having issues with the maintenance plans, especially the backups. That's why my opinion is that they suck. Rebuilding the plan often fixes the problem for them, but that is just silly.

We're no longer using the resample option for sp_updatestats as it doesn't sample correctly on large databases.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog
Go to Top of Page

Kristen
Test

United Kingdom
22403 Posts

Posted - 05/20/2010 :  15:15:59  Show Profile  Reply with Quote
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 choice

For SIMPLE Recovery Model:
Once a day
More often ...
or Once a day + some DIFFs during the day

FULL Recover Model:
Full backup once a day for and TLogs every 15 minutes
Full backup once a week, daily DIFFs and and TLogs every 15 minutes
Full backup once a week, two DIFFs during the day and and TLogs every 15 minutes

then 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 ...
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
36841 Posts

Posted - 05/20/2010 :  15:22:56  Show Profile  Visit tkizer's Homepage  Reply with Quote
Kristen, I agree completely with you! How do you find the time to write such lengthy posts?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog
Go to Top of Page

Kristen
Test

United Kingdom
22403 Posts

Posted - 05/20/2010 :  15:50:52  Show Profile  Reply with Quote
I'm not succinct like you! I tend to waffle on and on and ...

... I'll get my coat now!
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.12 seconds. Powered By: Snitz Forums 2000