Scheduled backup jobs not running

mrichter

Hi, I have SQLBackup 6 installed on one of our sql clusters (2 machines) and am trying to schedule jobs for full and log backups. I can run manual jobs fine, but my scheduled jobs to run, for instance every 4 hours, are not running. The strange part is that absolutely nothing is reported in the Activity History field for these missed jobs. The "Last Run" under the Jobs tab shows "Failed" but I can't seem to get to any logging info on this.

Would be curious if anybody could provide troubleshooting steps for this behavior..

Thanks!
Matt

petey

Scheduled jobs are managed by the SQL Server Agent. Have you tried using SQL Server Management Studio to check that the schedule is correct, and if running the scheduled job from within Management Studio works?

Thanks.

mrichter

Hi Peter, thanks for the reply! On your advice I checked SQL Managament. It told me to refer to the event viewer/applicaiton log. And the log implied an AD credentials error with the job back in SQL Manager. I fixed this and it seems to be working.. will find out for certain when the next job schedule kicks off at noon.

Thanks again!

michaelmm

I'm trying to use scheduled backups for the first time. The schedule, the backup location, etc are valid. I can run the exact same job manually.

My scheduled jobs show that they have never run. There is nothing in the logs related to the backups.

The SQL Agent service is set to Automatic.

I am running SQL Server Express 2008. Does this work with express?

petey

SQL Backup should work with SQL Server Express 2008, if you were able to run the backup by starting the job manually.

It then looks like a scheduling issue. Have you checked the backup schedule from within SSMS, to see if they are consistent with what you've set up using the SQL Backup GUI?

Thanks.

Eddie D

Hi michaelmm22

When you create a scheduled backup job, using SQL Backup a new job is created in the SQL Server Agent ->Jobs. The job created calls the SQL Backup Extended Stored Procedure. This works with SQL Server Workgroup Editions are above.

However, Microsoft have removed the SQL Server Agent from Express Editions of SQL Server 2005 / 2008 /2008 R2. So you cannot create a SQL Backup scheduled job in this manner. Unfortunately. the scheduled backup job wizard does detect you using SQL Server Express Edition. So this leads you to believe that you are creating a scheduled backup job. There is a Bug Report submitted to address this problem, whose reference is SB-2493.

The workaround to this issue is to use the SQL Backup Command Line executable, SQLBackupC.exe and create a batch file with the necessary backup commands. You can then schedule the batch file using the Windows Scheduler.

The code below is an example batch file that you can use:

@echo off
"C:\Program Files\Red Gate\SQL Backup 6\SQLEXP\SQLBackupC.exe" -E -I SQLEXP -SQL "BACKUP DATABASE [AdventureWorks] TO DISK = 'C:\Program Files\Microsoft SQL Server\MSSQL.3\MSSQL\Backup\<AUTO>.sqb' WITH DISKRETRYINTERVAL = 30, DISKRETRYCOUNT = 10, THREADCOUNT = 2"

Further examples are available in the SQL Backup Help information.

Alternatively, you can still run the scheduled backup wizard or backup wizard. On the final Summary step, select the Script Tab ->Select Command Line from the drop down menu ->The syntax for the Command Line Interface will appear that you can then copy and use in your batch file.

Many Thanks
Eddie

michaelmm

After a bit of fiddling it is scheduled and working using bat files. Just a note: Being able to run the wizard helped me to get my settings straight. So I hope the 'bug fix' doesn't include disabling the wizard for SQL Express installs.

Thanks,