February 6, 2003 at 7:02 am
I need some help here. I have backups that run each night to tape. The last 2 nights the jobs have failed and I have been given errors like the one in the subject line as well as this, which is what the logs are defining the problem as:
BackupTapeFile::SkipFileMark: SetTapePosition failure on backup device '\\.\Tape0'. Operating system error 23(Data error (cyclic redundancy check).).
Can anyone help me with what exactly is going on here. This is a production database and although I have other means to get me by for now, I can not have this job failing on a nightly basis.
Thanks for any IMMEDIATE help anyone can give me.
February 6, 2003 at 11:06 am
Possible tape issue. Have you changed tapes? Can you manually run a backup? Perhaps out of space on the tape?
This is the OS reporting and error. I never backup straight to tape because tapes is much less reliable.
Steve Jones
May 11, 2009 at 5:33 pm
I have an issue while i take backup of database...whether I take it on one drive, another drive or another server or another Tape drive...I am getting same error...Any insight on this????
Here's the error message-
BackupIoRequest::WaitForIoCompletion: read failure on backup device 'E:\Database files\abc.mdf'. Operating system error 23(Data error (cyclic redundancy check).).
Error: 18210, Severity: 16, State: 1.
It's really urgent, if anyone have some insight on this then please reply back...
Thanks in advance...
May 1, 2015 at 10:11 am
Hello Steve and Experts,
Occassional failure of my backup job:
I have a custom SQL Bkp job, which fails with the below error msg while it runs by schdule:
Error 3013, Severity 16, State 1, Procedure -, Line 1 [SQLSTATE 01000]
BACKUP DATABASE is terminating abnormally. [SQLSTATE 01000]
I find the above error msg in the Output file. The Eventviewer/SQL Error log just say this :-
Error: 3041, Severity: 16, State: 1.
BACKUP failed to complete the command BACKUP DATABASE . Check the backup application log for detailed messages.
When I run my procedure, manually, it always works good.
I have attached my script.
Please suggest if I need to modify my script or if that's an OS issue or any other trouble??
As if I say its with Wintel/Storage (Note: bkp goes to drive only, no tape here), I need a solid justification & prooof.
IF Object_id('USP_FullBACKUPDATABASES', 'P') IS NOT NULL
DROP PROC [dbo].[USP_FullBACKUPDATABASES ]
go
CREATE PROCEDURE [dbo].[USP_FullBACKUPDATABASES]
AS
SET NOCOUNT ON
BEGIN
BEGIN TRY
DECLARE @backup_path nvarchar(4000);
DECLARE @dbname nvarchar(max);
--DECLARE @recovery_model varchar(30);
DECLARE @sql nvarchar(4000);
DECLARE @backupfile nvarchar(4000);
DECLARE @BackupDirectory NVARCHAR(4000);
DECLARE @SERVERNAME SYSNAME;
-- Reading the default backup location from registry.
EXEC master..xp_instance_regread @rootkey = 'HKEY_LOCAL_MACHINE',
@key = 'Software\Microsoft\MSSQLServer\MSSQLServer',
@value_name = 'BackupDirectory', @BackupDirectory = @BackupDirectory OUTPUT ;
SET @backup_path = (SELECT @BackupDirectory AS [SQL Server default backup Value]) + '\'
--select @backup_path
Declare @len INT, @i INT, @Left varchar(max), @right varchar(max)
SET @SERVERNAME = (SELECT @@servername);
--select @backup_path AS [TLOG Backup PATH]
--PRINT @backup_path
IF @SERVERNAME like '%\%'
BEGIN
SET @Left= (select Left(@SERVERNAME, charindex('\', @SERVERNAME) - 1))
--SELECT @Left AS [DEFAULT SERVER NAME]
SET @len = (SELECT LEN(@SERVERNAME))
--SELECT @LEN
SET @right = (SELECT SUBSTRING(@SERVERNAME,Charindex ('\', @SERVERNAME)+1,@len))
--SELECT @right AS [SQL INSTANCE NAME]
END
ELSE
BEGIN
SELECT @left = @SERVERNAME
--SELECT @Left AS [DEFAULT SERVER NAME]
SELECT @right = 'default'
--SELECT @right AS [SQL INSTANCE NAME]
END
-- Determine if BACKUP COMPESSION is available based on SQL Server Version and Edition
DECLARE @CompressYN CHAR(1);
DECLARE @verinfoTbl TABLE
(
Verinfo sql_variant,
SPinfo sql_variant,
EditionInfo sql_variant
)
INSERT INTO @verinfoTbl (Verinfo, SPinfo, EditionInfo)
SELECT SERVERPROPERTY('productversion'), SERVERPROPERTY ('productlevel'), SERVERPROPERTY ('edition');
IF (
-- Check for SQL 2014
-- Editions that support backup compression:
-- Enterprise, Business Intelligence, Standard
((SELECT CONVERT(VARCHAR(16), Verinfo) FROM @verinfoTbl) LIKE '12.%') AND
(
((SELECT CONVERT(VARCHAR(16), EditionInfo) FROM @verinfoTbl) LIKE 'Enterprise%') OR
((SELECT CONVERT(VARCHAR(16), EditionInfo) FROM @verinfoTbl) LIKE 'Business%') OR
((SELECT CONVERT(VARCHAR(16), EditionInfo) FROM @verinfoTbl) LIKE 'Developer%') OR
((SELECT CONVERT(VARCHAR(16), EditionInfo) FROM @verinfoTbl) LIKE 'Standard%') OR
((SELECT CONVERT(VARCHAR(16), EditionInfo) FROM @verinfoTbl) LIKE 'Express%')
)
)
OR
(
-- Check for SQL 2012
-- Editions that support backup compression:
-- Enterprise, Business Intelligence, Standard
((SELECT CONVERT(VARCHAR(16), Verinfo) FROM @verinfoTbl) LIKE '11.%') AND
(
((SELECT CONVERT(VARCHAR(16), EditionInfo) FROM @verinfoTbl) LIKE 'Enterprise%') OR
((SELECT CONVERT(VARCHAR(16), EditionInfo) FROM @verinfoTbl) LIKE 'Business%') OR
((SELECT CONVERT(VARCHAR(16), EditionInfo) FROM @verinfoTbl) LIKE 'Developer%') OR
((SELECT CONVERT(VARCHAR(16), EditionInfo) FROM @verinfoTbl) LIKE 'Standard%') OR
((SELECT CONVERT(VARCHAR(16), EditionInfo) FROM @verinfoTbl) LIKE 'Express%')
)
)
OR
(
-- Check for SQL 2008 R2
-- Editions that support backup compression:
-- DataCenter, Enterprise, Standard
((SELECT CONVERT(VARCHAR(16), Verinfo) FROM @verinfoTbl) LIKE '10.50%') AND
(
((SELECT CONVERT(VARCHAR(16), EditionInfo) FROM @verinfoTbl) LIKE 'Enterprise%') OR
((SELECT CONVERT(VARCHAR(16), EditionInfo) FROM @verinfoTbl) LIKE 'Business%') OR
((SELECT CONVERT(VARCHAR(16), EditionInfo) FROM @verinfoTbl) LIKE 'Developer%') OR
((SELECT CONVERT(VARCHAR(16), EditionInfo) FROM @verinfoTbl) LIKE 'Standard%') OR
((SELECT CONVERT(VARCHAR(16), EditionInfo) FROM @verinfoTbl) LIKE 'Express%')
)
)
OR
(
-- Check for SQL 2008
-- Editions that support backup compression:
-- Enterprise
((SELECT CONVERT(VARCHAR(16), Verinfo) FROM @verinfoTbl) LIKE '10.00%') AND
(
((SELECT CONVERT(VARCHAR(16), EditionInfo) FROM @verinfoTbl) LIKE 'Enterprise%') OR
((SELECT CONVERT(VARCHAR(16), EditionInfo) FROM @verinfoTbl) LIKE 'Business%') OR
((SELECT CONVERT(VARCHAR(16), EditionInfo) FROM @verinfoTbl) LIKE 'Developer%') OR
((SELECT CONVERT(VARCHAR(16), EditionInfo) FROM @verinfoTbl) LIKE 'Standard%') OR
((SELECT CONVERT(VARCHAR(16), EditionInfo) FROM @verinfoTbl) LIKE 'Express%')
)
)
OR
(
-- Check for SQL 2005
-- No SQL Edition supports compression
((SELECT CONVERT(VARCHAR(16), Verinfo) FROM @verinfoTbl) LIKE '9.00%') AND
(
((SELECT CONVERT(VARCHAR(16), EditionInfo) FROM @verinfoTbl) LIKE 'Enterprise%') OR
((SELECT CONVERT(VARCHAR(16), EditionInfo) FROM @verinfoTbl) LIKE 'Business%') OR
((SELECT CONVERT(VARCHAR(16), EditionInfo) FROM @verinfoTbl) LIKE 'Developer%') OR
((SELECT CONVERT(VARCHAR(16), EditionInfo) FROM @verinfoTbl) LIKE 'Standard%') OR
((SELECT CONVERT(VARCHAR(16), EditionInfo) FROM @verinfoTbl) LIKE 'Express%')
)
)
BEGIN
SET @CompressYN = 'Y'
END
ELSE
---- BACKUP COMPRESSION not available
BEGIN
SET @CompressYN = 'N'
END
-- Close all the opened Cursors.
IF (SELECT Cursor_status('global', 'c')) >= -1
BEGIN
IF (SELECT Cursor_status('global', 'c')) >
-1
BEGIN
CLOSE c
END
DEALLOCATE c
END
-- Get the names of all qualifying databases
DECLARE c CURSOR fast_forward
FOR
SELECT NAME
--AS dbname
FROM sys.databases
WHERE database_id IN (SELECT dbid
FROM sys.sysdatabases d
EXCEPT
SELECT database_id
FROM msdb..suspect_pages)
AND NAME NOT IN ('tempdb','distribution' )
--AND compatibility_level > 80
AND state_desc = 'ONLINE'
AND source_database_id IS NULL -- Excludes snapshot
--ORDER BY [seq]
OPTION (FAST 10)
OPEN c
FETCH NEXT FROM c INTO @dbname
-- Backup each database
WHILE (@@FETCH_STATUS <> -1)
BEGIN
-- Create the backup output file name
SET @backupfile =
-- Backup path
@backup_path
-- Add the db name
+ @dbname
+ '_'
+ '[' + @left +']'
+ '_'
+ '[' + @right +']'
+ '_'
+'_'
-- Add the date and time to the file name
+ CONVERT (varchar, GETDATE(), 112) + '_' -- date
+ REPLACE (LEFT (CONVERT (varchar, GETDATE(), 108), 5), ':', '_') -- time
-- SELECT @backupfile
BEGIN
IF @CompressYN = 'Y'
BEGIN
SET @sql = 'BACKUP DATABASE ' + quotename(@dbname) +'TO DISK = ''' + @backupfile + '.BAK'' WITH INIT, COMPRESSION;'
-- select @sql
--PRINT @sql
EXEC (@sql)
END
ELSE
BEGIN
SET @sql = 'BACKUP DATABASE ' + quotename(@dbname) + 'TO DISK = ''' + @backupfile + '.BAK'' WITH INIT;'
--select @sql
--PRINT @sql
EXEC (@sql)
END
END
-- Move on to the next database
FETCH NEXT FROM c INTO @dbname
END
CLOSE c
DEALLOCATE c
END TRY
BEGIN catch
DECLARE @ErrorNumber INT;
DECLARE @ErrorSeverity INT;
DECLARE @ErrorState INT;
DECLARE @ErrorLine INT;
DECLARE @ErrorProcedure NVARCHAR(4000);
DECLARE @ErrorMessage NVARCHAR(4000);
SELECT @ErrorNumber = Error_number(),
@ErrorSeverity = Error_severity(),
@ErrorState = Error_state(),
@ErrorLine = Error_line(),
@ErrorProcedure = Error_procedure();
SELECT @ErrorMessage =
N'Error %d, Level %d, State %d, Procedure %s, Line %d, '
+ 'Message: ' + Error_message();
SELECT @ErrorMessage AS [Error_Message];
SELECT @ErrorProcedure AS [Error_Procedure];
PRINT 'Error '
+ CONVERT(VARCHAR(50), Error_number())
+ ', Severity '
+ CONVERT(VARCHAR(5), Error_severity())
+ ', State '
+ CONVERT(VARCHAR(5), Error_state())
+ ', Procedure '
+ Isnull(Error_procedure(), '-') + ', Line '
+ CONVERT(VARCHAR(5), Error_line());
PRINT Error_message();
END catch
SET NOCOUNT OFF
END
Thanks.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply