mlcktmguy
asked on
SQL Scheduled Backup not working, Ola Hallengren solution
I recently posted a questions about scheduled backups for a SQL Server DB. This is the questions
https://www.experts-exchange.com/questions/28921770/SQL-DB-Backup-to-External-Drive.html
Based on the answers I downloaded and installed the Ola.Helgerson solution. I setup the backup schedule to backup the DB each night at 3:30am. The following day I verified that the DB had been backed up at the scheduled time, 3:30am. At that point I awarded the points for my original question.
Unfortunately, the only day the backup ran was the first day. It has not run as scheduled any day since. I'm pretty sure the schedule is set up correctly.
Any ideas why my backup only ran the first day?
https://www.experts-exchange.com/questions/28921770/SQL-DB-Backup-to-External-Drive.html
Based on the answers I downloaded and installed the Ola.Helgerson solution. I setup the backup schedule to backup the DB each night at 3:30am. The following day I verified that the DB had been backed up at the scheduled time, 3:30am. At that point I awarded the points for my original question.
Unfortunately, the only day the backup ran was the first day. It has not run as scheduled any day since. I'm pretty sure the schedule is set up correctly.
Any ideas why my backup only ran the first day?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Forgot to mention that I am a SQL NUB.
How do I check if the SQL Agent is up and running? I
If it's not up and running, how do I make that happen?
Here is my Job Activity log: The job at the very top 'Backup JTS Full 3_00am Daily' is the one that ran successfully the first day I set up the schedule but has not run since.
Here is my Error log:
How do I check if the SQL Agent is up and running? I
If it's not up and running, how do I make that happen?
Here is my Job Activity log: The job at the very top 'Backup JTS Full 3_00am Daily' is the one that ran successfully the first day I set up the schedule but has not run since.
Here is my Error log:
As you can see in the Next Run column it says "Not scheduled" for all jobs and by the error log it seems that SQL Agent service is down. Somehow the SQL Agent stopped and you need to restart it to have the jobs running.
ASKER
How do I get SQL Agent started and how can I check if it's running?
You can check it in Windows Service or by running the SQL Server Configuration Manager and then check in SQL Server Services.
You'll find the Start button there.
You'll find the Start button there.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
OK, I think I know what it is. The schedule you show in the first picture, which is a schedule for a different job than Olla's, the date in the Start Date parameter from the Duration section at the bottom shows April 2nd instead of Feb 4th as I assume you intended. Make sure that date is not in the future, choose today or yesterday date, and then you should be able to see your next schedule. That is to solve the "not scheduled" issue.
The fail of Olla's jobs I thing is in regards to that Output file I mentioned above. It is possible that the folder doesn't exists as specified in that text box but you should check the error message first. You can opt out that log file if you want but I recommend to leave it and use an existing location.
One more thing. If you will decide to use Ollas jobs then make sure you delete or at least disable other maintenance backup job set up differently. You don't want to do the backup twice.
The fail of Olla's jobs I thing is in regards to that Output file I mentioned above. It is possible that the folder doesn't exists as specified in that text box but you should check the error message first. You can opt out that log file if you want but I recommend to leave it and use an existing location.
One more thing. If you will decide to use Ollas jobs then make sure you delete or at least disable other maintenance backup job set up differently. You don't want to do the backup twice.
ASKER
Thanks for all of the help and input. With your help I managed to get SQL Agent started. Looks like the job is scheduled. I'll check tomorrow morning to make sure it ran and is scheduled again for the next evening.
I also changed the startup in services.msc from manual to automatic. Hopefully this will start it automatically whenever the computer is turned on in the morning.
I'll keep you posted on the result.
I also changed the startup in services.msc from manual to automatic. Hopefully this will start it automatically whenever the computer is turned on in the morning.
I'll keep you posted on the result.
I also changed the startup in services.msc from manual to automatic.Good move. By default SQL Agent has Manual startup set.
ASKER
It ran last night so I think everything is resolved. Thanks again for all of the help.
Did you notice any errors in job or error log?