Please Help!! Database backup Job Fails

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Please Help!! Database backup Job Fails

Author

Topic

chriztoph
Posting Yak Master

184 Posts

Posted - 2009-06-15 : 00:32:58

Hi eveyone..

I have this stored Proc that calls in a Job for the database backup..everytime i try to run an error occur

Execution of job 'Backup GP full backup12AM' failed. see the history log for details.


CREATE PROCEDURE [dbo].[prcBackupGreatPlainsFull12AM]
AS
BACKUP DATABASE [CFSCM] TO  DISK = N'E:\GPBak\FullBackup\CFSCM_12AM.BAK' WITH  INIT ,  NOUNLOAD ,  NAME = N'GreatPlains Backup',  NOSKIP ,  STATS = 10,  NOFORMAT
BACKUP DATABASE [CFSHI] TO  DISK = N'E:\GPBak\FullBackup\CFSHI_12AM.BAK' WITH  INIT ,  NOUNLOAD ,  NAME = N'GreatPlains Backup',  NOSKIP ,  STATS = 10,  NOFORMAT
BACKUP DATABASE [CFSI] TO  DISK = N'E:\GPBak\FullBackup\CFSI_12AM.BAK' WITH  INIT ,  NOUNLOAD ,  NAME = N'GreatPlains Backup',  NOSKIP ,  STATS = 10,  NOFORMAT
BACKUP DATABASE [CFSSA] TO  DISK = N'E:\GPBak\FullBackup\CFSSA_12AM.BAK' WITH  INIT ,  NOUNLOAD ,  NAME = N'GreatPlains Backup',  NOSKIP ,  STATS = 10,  NOFORMAT
BACKUP DATABASE [CITS] TO  DISK = N'E:\GPBak\FullBackup\CITS_12AM.BAK' WITH  INIT ,  NOUNLOAD ,  NAME = N'GreatPlains Backup',  NOSKIP ,  STATS = 10,  NOFORMAT
BACKUP DATABASE [DYNAMICS] TO  DISK = N'E:\GPBak\FullBackup\DYNAMICS_12AM.BAK' WITH  INIT ,  NOUNLOAD ,  NAME = N'GreatPlains Backup',  NOSKIP ,  STATS = 10,  NOFORMAT
BACKUP DATABASE [RYC] TO  DISK = N'E:\GPBak\FullBackup\RYC_12AM.BAK' WITH  INIT ,  NOUNLOAD ,  NAME = N'GreatPlains Backup',  NOSKIP ,  STATS = 10,  NOFORMAT
BACKUP DATABASE [SCCC] TO  DISK = N'E:\GPBak\FullBackup\SCCC_12AM.BAK' WITH  INIT ,  NOUNLOAD ,  NAME = N'GreatPlains Backup',  NOSKIP ,  STATS = 10,  NOFORMAT
BACKUP DATABASE [SMSTS] TO  DISK = N'E:\GPBak\FullBackup\SMSTS_12AM.BAK' WITH  INIT ,  NOUNLOAD ,  NAME = N'GreatPlains Backup',  NOSKIP ,  STATS = 10,  NOFORMAT
GO

BUt i can't see any errors in SQL Server Logs

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-06-15 : 01:22:04

Have a right click on the job and see history there.
You will get more information about the error there.
Also you can try to exec the SP without running a job to see what happens.

Greetings
Webfred

No, you're never too old to Yak'n'Roll if you're too young to die.

chriztoph
Posting Yak Master

184 Posts

Posted - 2009-06-15 : 02:50:54

i tried to right click the job and star job but it fails..
i can't see any error in the History Log..
i tried it also the exec but again the error occur..

but if i run the query by for every database,it works..

i tried also creating another job and Maintenance Plan but ot does'nt work

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-06-15 : 03:25:02

what is the error message ?

KH
[spoiler]Time is always against us[/spoiler]

ahmad.osama
Posting Yak Master

183 Posts

Posted - 2009-06-15 : 03:42:33

quote:

Originally posted by chriztoph

Hi eveyone..

I have this stored Proc that calls in a Job for the database backup..everytime i try to run an error occur

Execution of job 'Backup GP full backup12AM' failed. see the history log for details.


CREATE PROCEDURE [dbo].[prcBackupGreatPlainsFull12AM]
AS
BACKUP DATABASE [CFSCM] TO  DISK = N'E:\GPBak\FullBackup\CFSCM_12AM.BAK' WITH  INIT ,  NOUNLOAD ,  NAME = N'GreatPlains Backup',  NOSKIP ,  STATS = 10,  NOFORMAT
BACKUP DATABASE [CFSHI] TO  DISK = N'E:\GPBak\FullBackup\CFSHI_12AM.BAK' WITH  INIT ,  NOUNLOAD ,  NAME = N'GreatPlains Backup',  NOSKIP ,  STATS = 10,  NOFORMAT
BACKUP DATABASE [CFSI] TO  DISK = N'E:\GPBak\FullBackup\CFSI_12AM.BAK' WITH  INIT ,  NOUNLOAD ,  NAME = N'GreatPlains Backup',  NOSKIP ,  STATS = 10,  NOFORMAT
BACKUP DATABASE [CFSSA] TO  DISK = N'E:\GPBak\FullBackup\CFSSA_12AM.BAK' WITH  INIT ,  NOUNLOAD ,  NAME = N'GreatPlains Backup',  NOSKIP ,  STATS = 10,  NOFORMAT
BACKUP DATABASE [CITS] TO  DISK = N'E:\GPBak\FullBackup\CITS_12AM.BAK' WITH  INIT ,  NOUNLOAD ,  NAME = N'GreatPlains Backup',  NOSKIP ,  STATS = 10,  NOFORMAT
BACKUP DATABASE [DYNAMICS] TO  DISK = N'E:\GPBak\FullBackup\DYNAMICS_12AM.BAK' WITH  INIT ,  NOUNLOAD ,  NAME = N'GreatPlains Backup',  NOSKIP ,  STATS = 10,  NOFORMAT
BACKUP DATABASE [RYC] TO  DISK = N'E:\GPBak\FullBackup\RYC_12AM.BAK' WITH  INIT ,  NOUNLOAD ,  NAME = N'GreatPlains Backup',  NOSKIP ,  STATS = 10,  NOFORMAT
BACKUP DATABASE [SCCC] TO  DISK = N'E:\GPBak\FullBackup\SCCC_12AM.BAK' WITH  INIT ,  NOUNLOAD ,  NAME = N'GreatPlains Backup',  NOSKIP ,  STATS = 10,  NOFORMAT
BACKUP DATABASE [SMSTS] TO  DISK = N'E:\GPBak\FullBackup\SMSTS_12AM.BAK' WITH  INIT ,  NOUNLOAD ,  NAME = N'GreatPlains Backup',  NOSKIP ,  STATS = 10,  NOFORMAT
GO

BUt i can't see any errors in SQL Server Logs

Instead of backing up all databases in a procedure, backup each database as a separate job step, this will allow you to have more control over the execution; also right click on the job and select history to get the error message

Regards,
Ahmad Osama

chriztoph
Posting Yak Master

184 Posts

Posted - 2009-06-15 : 03:42:51

No error appeared but the Icon in the left side of the Job is RED 'X' and the 'Last Run Status' is Failed!

chriztoph
Posting Yak Master

184 Posts

Posted - 2009-06-15 : 03:51:23

Here are the the Message i i've got in the History log of the jod failed..

The job failed. The Job was invoked by Schedule 8 (Schedule). The last step to run was step 1 (Call CFSH Stored Procedure).

and

The job failed. The Job was invoked by User CFSHARP12\Administrator. The last step to run was step 1 (Call CFSH Stored Procedure).

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-06-15 : 03:52:03

quote:
Originally posted by chriztoph

No error appeared but the Icon in the left side of the Job is RED 'X' and the 'Last Run Status' is Failed!

I mean when you execute your stored procedure in the SSMS query window

KH
[spoiler]Time is always against us[/spoiler]

ahmad.osama
Posting Yak Master

183 Posts

Posted - 2009-06-15 : 03:54:12

quote:
Originally posted by chriztoph

Here are the the Message i i've got in the History log of the jod failed..

The job failed. The Job was invoked by Schedule 8 (Schedule). The last step to run was step 1 (Call CFSH Stored Procedure).

and

The job failed. The Job was invoked by User CFSHARP12\Administrator. The last step to run was step 1 (Call CFSH Stored Procedure).

then try what i suggested in my earlier post...

Regards,
Ahmad Osama

chriztoph
Posting Yak Master

184 Posts

Posted - 2009-06-15 : 03:57:09

it is fine when i run the query one by one like this in the SSMS query window..

BACKUP DATABASE [CFSCM] TO  DISK = N'E:\GPBak\FullBackup\CFSCM_12AM.BAK' WITH  INIT ,  NOUNLOAD ,  NAME = N'GreatPlains Backup',  NOSKIP ,  STATS = 10,  NOFORMAT

BACKUP DATABASE [CFSHI] TO  DISK = N'E:\GPBak\FullBackup\CFSHI_12AM.BAK' WITH  INIT ,  NOUNLOAD ,  NAME = N'GreatPlains Backup',  NOSKIP ,  STATS = 10,  NOFORMAT

chriztoph
Posting Yak Master

184 Posts

Posted - 2009-06-15 : 04:02:39

@ahmad.osama

it is working..thanks...

But is this ok?i mean is this doesn't effect the performance of the SQL server if all 9 jobs are running at the same time?

ahmad.osama
Posting Yak Master

183 Posts

Posted - 2009-06-15 : 04:34:58

quote:
Originally posted by chriztoph

@ahmad.osama

it is working..thanks...

But is this ok?i mean is this doesn't effect the performance of the SQL server if all 9 jobs are running at the same time?

There is only a single Job with 9 steps...each steps runs based on order that u specify, thus on step run at a time...

Regards,
Ahmad Osama

chriztoph
Posting Yak Master

184 Posts

Posted - 2009-06-15 : 04:45:20

ok thanks..got it..

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources