backup verify ok on one instance but gives error on another

ogrishmanogrishman Posts: 81 Bronze 4
edited March 7, 2011 6:38AM in SQL Backup Previous Versions
Hi,

I'm using different versions of SQL BACK on different instances. I backup a database on instance0. Then verified it on instance0, everything is good. Then I copy the backup to instance1 and instance2 to restore the database. On instance1, everything goes in the right way. But on instance2, i encountered an error. I can assure the backup files itself is ok (i checked the md5 on all the 3 servers, they are same).
What's the problem of this issue?

I've attached some information(instance version, sql backup version, and the verify result) below. Thanks.
/*
#####################################################################
(instance 0) backup take from this instance
#####################################################################
*/
SELECT CAST(LEFT(CAST(SERVERPROPERTY('ProductVersion') AS nvarchar(max)),CHARINDEX('.',CAST(SERVERPROPERTY('ProductVersion') AS nvarchar(max))) - 1) + '.' + REPLACE(RIGHT(CAST(SERVERPROPERTY('ProductVersion') AS nvarchar(max)), LEN(CAST(SERVERPROPERTY('ProductVersion') AS nvarchar(max))) - CHARINDEX('.',CAST(SERVERPROPERTY('ProductVersion') AS nvarchar(max)))),'.','') AS numeric(18,10)) AS version;
/*
version
---------------------------------------
10.0253100000

(1 row(s) affected)
*/
DECLARE @CurrentSQBVersion NVARCHAR(50)
CREATE TABLE #temp ( trash CHAR(1) )
INSERT  #temp
        EXEC master..sqbutility 1030, @CurrentSQBVersion OUTPUT
SELECT  @CurrentSQBVersion AS [SQB Version]
DROP TABLE #temp
/*
5.4.0.35
*/ 
    

EXECUTE master..sqlbackup N'-SQL "RESTORE VERIFYONLY
FROM DISK = ''c:\a.sqb''
WITH CHECKSUM'
/*
SQL Backup v5.4.0.35
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Verifying file:
  c:\a.sqb
 
The backup set on file 1 is valid.
SQL Backup process ended.
 
 

(7 row(s) affected)

name                                                             value
---------------------------------------------------------------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
exitcode                                                         0
sqlerrorcode                                                     0
filename001                                                      c:\a.sqb

(3 row(s) affected)

*/




/*
#####################################################################
instance 1 (backup verify success)
#####################################################################
*/
SELECT CAST(LEFT(CAST(SERVERPROPERTY('ProductVersion') AS nvarchar(max)),CHARINDEX('.',CAST(SERVERPROPERTY('ProductVersion') AS nvarchar(max))) - 1) + '.' + REPLACE(RIGHT(CAST(SERVERPROPERTY('ProductVersion') AS nvarchar(max)), LEN(CAST(SERVERPROPERTY('ProductVersion') AS nvarchar(max))) - CHARINDEX('.',CAST(SERVERPROPERTY('ProductVersion') AS nvarchar(max)))),'.','') AS numeric(18,10)) AS version;
/*
version
---------------------------------------
10.5016001000

(1 row(s) affected)
*/
DECLARE @CurrentSQBVersion NVARCHAR(50)
CREATE TABLE #temp ( trash CHAR(1) )
INSERT  #temp
        EXEC master..sqbutility 1030, @CurrentSQBVersion OUTPUT
SELECT  @CurrentSQBVersion AS [SQB Version]
DROP TABLE #temp
/*
6.4.0.56
*/ 
    

EXECUTE master..sqlbackup N'-SQL "RESTORE VERIFYONLY
FROM DISK = ''c:\a.sqb''
WITH CHECKSUM'
/*
SQL Backup v6.4.0.56
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Verifying file:
  c:\a.sqb
 
Attempting to restore this backup may encounter storage space problems. Subsequent messages will provide details.
The path specified by "H:\MSSQL\Data\ods.mdf" is not in a valid directory.
Directory lookup for the file "E:\MSSQL\Data\ods_log.ldf" failed with the operating system error 3(failed to retrieve text for this error. Reason: 1815).
The backup set on file 1 is valid.
SQL Backup process ended.
 
 

(10 row(s) affected)

name                                                             value
---------------------------------------------------------------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
exitcode                                                         0
sqlerrorcode                                                     0
filename001                                                      c:\a.sqb

(3 row(s) affected)

*/





/*
#####################################################################
instacne 2 (backup verify fail)
#####################################################################
*/
SELECT CAST(LEFT(CAST(SERVERPROPERTY('ProductVersion') AS nvarchar(max)),CHARINDEX('.',CAST(SERVERPROPERTY('ProductVersion') AS nvarchar(max))) - 1) + '.' + REPLACE(RIGHT(CAST(SERVERPROPERTY('ProductVersion') AS nvarchar(max)), LEN(CAST(SERVERPROPERTY('ProductVersion') AS nvarchar(max))) - CHARINDEX('.',CAST(SERVERPROPERTY('ProductVersion') AS nvarchar(max)))),'.','') AS numeric(18,10)) AS version;
/*
version
---------------------------------------
9.0040350000

(1 row(s) affected)
*/
DECLARE @CurrentSQBVersion NVARCHAR(50)
CREATE TABLE #temp ( trash CHAR(1) )
INSERT  #temp
        EXEC master..sqbutility 1030, @CurrentSQBVersion OUTPUT
SELECT  @CurrentSQBVersion AS [SQB Version]
DROP TABLE #temp
/*
5.4.0.55
*/ 
    

EXECUTE master..sqlbackup N'-SQL "RESTORE VERIFYONLY
FROM DISK = ''c:\a.sqb''
WITH CHECKSUM'
/*
SQL Backup v5.4.0.55
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Verifying file:
  e:\a.sqb
 
SQL Server error
 
SQL error 3013: SQL error 3013: VERIFY DATABASE is terminating abnormally.
SQL error 3241: SQL error 3241: The media family on device 'SQLBACKUP_60DDE0C2-97D2-4A45-94EB-98A5560E61BB' is incorrectly formed. SQL Server cannot process this media family.
 
SQL Backup exit code: 1100
SQL error code: 3241

(10 row(s) affected)

name                                                             value
---------------------------------------------------------------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
exitcode                                                         1100
sqlerrorcode                                                     3241
filename001                                                      e:\a.sqb

(3 row(s) affected)
*/
· Share on Twitter

Comments

  • peteypetey Posts: 2,358 New member
    Instance 1 is running SQL Server 2008. Instance 2 is running SQL Server 2008 R2 and instance 3 is running SQL Server 2005. You cannot restore a backup taken on a newer version of SQL Server, on to an older version of SQL Server i.e. in this case, restoring a backup taken on SQL Server 2008 onto a SQL Server 2005 instance.

    That restriction applies to RESTORE VERIFYONLY too.

    Thanks.
    Peter Yeoh
    SQL Backup Consultant Developer
    Associate, Yohz Software
    Beyond compression - SQL Backup goodies under the hood, updated for version 8
    · Share on Twitter
  • peteypetey Posts: 2,358 New member
    If you need to run RESTORE VERIFYONLY on instance 3, and you don't have a SQL Server 2008 license available, you can always install and use the Express Editions of SQL Server 2005/2008 instead. While there is a limitation on the size of the database that can be created (4 GB and 10 GB respectively), there isn't a restriction on RESTORE VERIFYONLY. This means you can run RESTORE VERIFYONLY on backup sets of databases larger than 4 or 10 GB without problems.

    Also, your SQL Backup license is per box, so you can easily install SQL Backup on that new instance without any restrictions.

    Having said that, you should know that verification performed using RESTORE VERIFYONLY isn't 100% reliable. Restoring from the backup sets is really the only option when you want 100% assurance that you can restore from the backup set.

    Thanks.
    Peter Yeoh
    SQL Backup Consultant Developer
    Associate, Yohz Software
    Beyond compression - SQL Backup goodies under the hood, updated for version 8
    · Share on Twitter
  • ogrishmanogrishman Posts: 81 Bronze 4
    Hi petey,

    Thank your for quick response.

    So, do you mean a backup file is ok if it can be restored without any error? To test the backup file, should i do something to further verify it after it is restored without any error ?

    Regards
    · Share on Twitter
  • peteypetey Posts: 2,358 New member
    So, do you mean a backup file is ok if it can be restored without any error?
    If by 'ok' you mean that the backup file can be restored, yes. If by 'ok' you mean that the restored database is free of errors, that depends.

    Generally, if your database page verification option is set to CHECKSUM, and your backup was also performed using the CHECKSUM option, then if you successfully restore from the backup file, you can be fairly certain that the backup file is recoverable, and the recovered database is free of errors.

    If any of the above 2 conditions are not present, then you should run a consistency check (e.g. DBCC CHECKDB) to ensure that the restored database is free of errors.

    Since you are already computing MD5 checksums for your backup files, you could first generate the checksum on instance 2 before restoring the backup file. If the backup restores successfully, and the restored database is free of errors, then on instance 3, you don't really need to restore and check the backup file again. You just need to generate the checkum for the copied file on instance 3 and compare its value against the checksum value you obtained on instance 2. If they match, it means that you have an exact copy of a valid backup file.
    Peter Yeoh
    SQL Backup Consultant Developer
    Associate, Yohz Software
    Beyond compression - SQL Backup goodies under the hood, updated for version 8
    · Share on Twitter
Sign In or Register to comment.