DBA

Filelistonly and headeronly – Find SQL server version from backup and use of header only version, ldf mdf size and LSN restore and file list only logical name.

Advertisements

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.

Filelistonly

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.

Header Only

I’m currently working as a SQL server DBA in one of the top MNC. I’m passionate about SQL Server And I’m specialized in Administration and Performance tuning. I’m an active member of SQL server Central and MSDN forum. I also write articles in SQL server Central. For more Click here

Leave a Reply

%d bloggers like this: