SQL Server backup job fails....

  • I am using SQL Server 2005. i have 3 jobs that runs full bak, differential baks, and a transactional backups.

    now as of now i need to show the script for only differential backups that i use. when i ran all the scripts according to the scheduled time they were running fine and creating backups as desired. bu since many days i am seeinng some errors. Please let me know what went wrong.

    so the script for the differential backup is below:------------

    ----------------------------------------------------------------------------

    -- Differential Backup script

    -- Should be scheduled to run hourly or accordingly

    ----------------------------------------------------------------------------

    DECLARE @BackupFolder AS varchar(250);

    DECLARE @CurDT AS datetime;

    DECLARE @DBName AS varchar(250);

    DECLARE crDatabases CURSOR

    FOR

    select name from master..sysdatabases where name in

    ('DPAC0123','DPAC408_OLD','DPAC2343'','DPAC1212','DPAC0012','DPAC8989')

    OPEN crDatabases

    FETCH NEXT FROM crDatabases INTO @DBName

    WHILE (@@FETCH_STATUS = 0)

    BEGIN

    SET @CurDT = getdate();

    IF NOT EXISTS(select * from master.dbo.sysprocesses where cmd = 'BACKUP DATABASE' and DB_NAME(dbid) = @DBName)

    BEGIN

    SET @BackupFolder = '\\IBM-SYM-0546\BACKUPS\IBM01\'; -----locationof the folder.......

    SET @BackupFolder = @BackupFolder + @DBName + '\';

    EXEC master..SMXP_CreateFolder @BackupFolder

    SET @BackupFolder = @BackupFolder + CONVERT(varchar(10), @CurDT, 110) + '\';

    EXEC master..SMXP_CreateFolder @BackupFolder

    SET @BackupFolder = @BackupFolder + @DBName+'_'+CAST(DATEPART(hour,@CurDT) AS varchar(2))+'.'+CAST(DATEPART(minute,@CurDT) AS varchar(2))+'.'+CAST(DATEPART(second,@CurDT) AS varchar(2))+'.dif';

    BACKUP DATABASE @DBName

    TO DISK = @BackupFolder

    WITH DIFFERENTIAL,

    NOUNLOAD, SKIP, NOFORMAT

    END

    ELSE

    print 'Database backup in process! Skiping...'

    FETCH NEXT FROM crDatabases INTO @DBName

    END

    CLOSE crDatabases

    DEALLOCATE crDatabases

    and the output of this is below:-

    Processed 7712 pages for database 'DPAC0123', file 'System_Data' on file 1.

    Processed 27056 pages for database 'DPAC0123', file 'Core_Data_1' on file 1.

    Processed 61200 pages for database 'DPAC0123', file 'NIndex_1' on file 1.

    Processed 138 pages for database 'DPAC0123', file 'sys_Log' on file 1.

    BACKUP DATABASE WITH DIFFERENTIAL successfully processed 96106 pages in 20.462 seconds (38.476 MB/sec).

    Processed 6560 pages for database 'DPAC408_OLD', file 'System_Data' on file 1.

    Processed 19768 pages for database 'DPAC408_OLD', file 'Core_Data_1' on file 1.

    Processed 29912 pages for database 'DPAC408_OLD', file 'Index_1' on file 1.

    Processed 2 pages for database 'DPAC408_OLD', file 'sys_Log' on file 1.

    BACKUP DATABASE WITH DIFFERENTIAL successfully processed 56242 pages in 14.446 seconds (31.893 MB/sec).

    Msg 3201, Level 16, State 1, Line 36

    Cannot open backup device 'IBM-SYM-0546\BACKUPS\IBM01\DPAC2343\09-21-2009\P50_14.7.50.dif'. Operating system error 3(The system cannot find the path specified.).

    Msg 3013, Level 16, State 1, Line 36

    BACKUP DATABASE is terminating abnormally.

    Msg 3201, Level 16, State 1, Line 36

    Cannot open backup device 'IBM-SYM-0546\BACKUPS\IBM01\DPAC1212\09-21-2009\P92_14.7.51.dif'. Operating system error 3(The system cannot find the path specified.).

    Msg 3013, Level 16, State 1, Line 36

    BACKUP DATABASE is terminating abnormally.

    Msg 3201, Level 16, State 1, Line 36

    Cannot open backup device 'IBM-SYM-0546\BACKUPS\IBM01\DPAC0012\09-21-2009\P53_14.7.51.dif'. Operating system error 3(The system cannot find the path specified.).

    Msg 3013, Level 16, State 1, Line 36

    BACKUP DATABASE is terminating abnormally.

    Msg 3201, Level 16, State 1, Line 36

    Cannot open backup device 'IBM-SYM-0546\BACKUPS\IBM01\DPAC8989\09-21-2009\P93_14.7.51.dif'. Operating system error 3(The system cannot find the path specified.).

    Msg 3013, Level 16, State 1, Line 36

    BACKUP DATABASE is terminating abnormally.

  • any help friends??????????

  • As you are backing up to a network drive make sure the user running the backup job has the required permissions on the network drive.. If this Job has been running for long without any problems make sure that the user permissions has not changed...

    Also check the errorlogs for any useful information on this..

    Sanz
  • espanolanthony (9/21/2009)


    I am using SQL Server 2005. i have 3 jobs that runs full bak, differential baks, and a transactional backups.

    now as of now i need to show the script for only differential backups that i use. when i ran all the scripts according to the scheduled time they were running fine and creating backups as desired. bu since many days i am seeinng some errors. Please let me know what went wrong.

    so the script for the differential backup is below:------------

    ----------------------------------------------------------------------------

    -- Differential Backup script

    -- Should be scheduled to run hourly or accordingly

    ----------------------------------------------------------------------------

    DECLARE @BackupFolder AS varchar(250);

    DECLARE @CurDT AS datetime;

    DECLARE @DBName AS varchar(250);

    DECLARE crDatabases CURSOR

    FOR

    select name from master..sysdatabases where name in

    ('DPAC0123','DPAC408_OLD','DPAC2343'','DPAC1212','DPAC0012','DPAC8989')

    OPEN crDatabases

    FETCH NEXT FROM crDatabases INTO @DBName

    WHILE (@@FETCH_STATUS = 0)

    BEGIN

    SET @CurDT = getdate();

    IF NOT EXISTS(select * from master.dbo.sysprocesses where cmd = 'BACKUP DATABASE' and DB_NAME(dbid) = @DBName)

    BEGIN

    SET @BackupFolder = '\\IBM-SYM-0546\BACKUPS\IBM01\'; -----locationof the folder.......

    SET @BackupFolder = @BackupFolder + @DBName + '\';

    EXEC master..SMXP_CreateFolder @BackupFolder

    SET @BackupFolder = @BackupFolder + CONVERT(varchar(10), @CurDT, 110) + '\';

    EXEC master..SMXP_CreateFolder @BackupFolder

    SET @BackupFolder = @BackupFolder + @DBName+'_'+CAST(DATEPART(hour,@CurDT) AS varchar(2))+'.'+CAST(DATEPART(minute,@CurDT) AS varchar(2))+'.'+CAST(DATEPART(second,@CurDT) AS varchar(2))+'.dif';

    BACKUP DATABASE @DBName

    TO DISK = @BackupFolder

    WITH DIFFERENTIAL,

    NOUNLOAD, SKIP, NOFORMAT

    END

    ELSE

    print 'Database backup in process! Skiping...'

    FETCH NEXT FROM crDatabases INTO @DBName

    END

    CLOSE crDatabases

    DEALLOCATE crDatabases

    and the output of this is below:-

    Processed 7712 pages for database 'DPAC0123', file 'System_Data' on file 1.

    Processed 27056 pages for database 'DPAC0123', file 'Core_Data_1' on file 1.

    Processed 61200 pages for database 'DPAC0123', file 'NIndex_1' on file 1.

    Processed 138 pages for database 'DPAC0123', file 'sys_Log' on file 1.

    BACKUP DATABASE WITH DIFFERENTIAL successfully processed 96106 pages in 20.462 seconds (38.476 MB/sec).

    Processed 6560 pages for database 'DPAC408_OLD', file 'System_Data' on file 1.

    Processed 19768 pages for database 'DPAC408_OLD', file 'Core_Data_1' on file 1.

    Processed 29912 pages for database 'DPAC408_OLD', file 'Index_1' on file 1.

    Processed 2 pages for database 'DPAC408_OLD', file 'sys_Log' on file 1.

    BACKUP DATABASE WITH DIFFERENTIAL successfully processed 56242 pages in 14.446 seconds (31.893 MB/sec).

    Msg 3201, Level 16, State 1, Line 36

    Cannot open backup device 'IBM-SYM-0546\BACKUPS\IBM01\DPAC2343\09-21-2009\P50_14.7.50.dif'. Operating system error 3(The system cannot find the path specified.).

    Msg 3013, Level 16, State 1, Line 36

    BACKUP DATABASE is terminating abnormally.

    Msg 3201, Level 16, State 1, Line 36

    Cannot open backup device 'IBM-SYM-0546\BACKUPS\IBM01\DPAC1212\09-21-2009\P92_14.7.51.dif'. Operating system error 3(The system cannot find the path specified.).

    Msg 3013, Level 16, State 1, Line 36

    BACKUP DATABASE is terminating abnormally.

    Msg 3201, Level 16, State 1, Line 36

    Cannot open backup device 'IBM-SYM-0546\BACKUPS\IBM01\DPAC0012\09-21-2009\P53_14.7.51.dif'. Operating system error 3(The system cannot find the path specified.).

    Msg 3013, Level 16, State 1, Line 36

    BACKUP DATABASE is terminating abnormally.

    Msg 3201, Level 16, State 1, Line 36

    Cannot open backup device 'IBM-SYM-0546\BACKUPS\IBM01\DPAC8989\09-21-2009\P93_14.7.51.dif'. Operating system error 3(The system cannot find the path specified.).

    Msg 3013, Level 16, State 1, Line 36

    BACKUP DATABASE is terminating abnormally.

    At one place near the command line:

    select name from master..sysdatabases where name in

    ('DPAC0123','DPAC408_OLD','DPAC2343'','DPAC1212','DPAC0012','DPAC8989')

    you have one database name incorrectly closed with quote ('DPAC2343'',) is it natural?

    -Forum Etiquette: How to post Performance Problems[/url]

    -Forum Etiquette: How to post data/code to get the best help [/url]

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply