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
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