Problem: SQL server database refresh / restore running for a long time is suddenly fails every Friday night. The refresh job is running from agent script but the triggering and managing jobs are handled by third party tool.
Source server taking backup in a share path and the target server restoring it daily night. To find the issue we need to capture the header information of backup where is coming from and what it’s containing it. We can we can get all from the table – headeronly.
Script:
restore filelistonly from disk ='D:\BACKUP\DBA_TEST.BAK'
restore headeronly from disk ='D:\BACKUP\DBA_TEST.BAK'
--drop table tbl_headeronly
--truncate table tbl_headeronly
CREATE TABLE tbl_headeronly
(
BackupName NVARCHAR(128)
,BackupDescription NVARCHAR(255)
,BackupType SMALLINT
,ExpirationDate DATETIME
,Compressed TINYINT
,Position SMALLINT
,DeviceType TINYINT
,UserName NVARCHAR(128)
,ServerName NVARCHAR(128)
,DatabaseName NVARCHAR(128)
,DatabaseVersion INT
,DatabaseCreationDate DATETIME
,BackupSize NUMERIC(20, 0)
,FirstLSN NUMERIC(25, 0)
,LastLSN NUMERIC(25, 0)
,CheckpointLSN NUMERIC(25, 0)
,DatabaseBackupLSN NUMERIC(25, 0)
,BackupStartDate DATETIME
,BackupFinishDate DATETIME
,SortOrder SMALLINT
,CodePage SMALLINT
,UnicodeLocaleId INT
,UnicodeComparisonStyle INT
,CompatibilityLevel TINYINT
,SoftwareVendorId INT
,SoftwareVersionMajor INT
,SoftwareVersionMinor INT
,SoftwareVersionBuild INT
,MachineName NVARCHAR(128)
,Flags INT
,BindingID UNIQUEIDENTIFIER
,RecoveryForkID UNIQUEIDENTIFIER
,Collation NVARCHAR(128)
,FamilyGUID uniqueidentifier
,HasBulkLoggedData bit
,IsSnapshot bit
,IsReadOnly bit
,IsSingleUser bit
,HasBackupChecksums bit
,IsDamaged bit
,BeginsLogChain bit
,HasIncompleteMetaData bit
,IsForceOffline bit
,IsCopyOnly bit
,FirstRecoveryForkID uniqueidentifier
,ForkPointLSN numeric(25,0)
,RecoveryModel nvarchar(60)
,DifferentialBaseLSN numeric(25,0)
,DifferentialBaseGUID uniqueidentifier
,BackupTypeDescription nvarchar(60)
,BackupSetGUID uniqueidentifier NULL
,CompressedBackupSize bigint
,Containment tinyint
)
-- Change the table based on the SQL version, since each version has different column
insert into tbl_headeronly
EXEC ('restore headeronly from disk =''D:\BACKUP\DBA_TEST.BAK''');
select * from tbl_headeronly
Copy and place it before the refresh job trigger in the same step or new step before that.
We found that, there is a backup running to same share from other server, same database created in other servers as well.
Msg 213, Level 16, State 7, Line 1
Column name or number of supplied values does not match table definition.
Msg 3013, Level 16, State 1, Line 1
RESTORE HEADERONLY is terminating abnormally.
Change the table based on the SQL version, since each version has different column
How to you find the data MDF and transaction log LDF size from backup files.
How much size is needed for the restore of database files.
Filelistonly – You can find Logincalname, Physicalname, size of the MDF, NDF & LDF files this will help to find the target server space needed before a restore.
How to find SQL server version from backup database file
Know before we are restoring – Lower version to higher version of SQL server database.
HeaderOnly – Database Version, Database name, Server Name, Collation and Backup date.
