September 21, 2009 at 12:18 pm
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.
September 21, 2009 at 12:59 pm
any help friends??????????
September 21, 2009 at 2:01 pm
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..
September 22, 2009 at 12:32 am
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?
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy