Reading backup file details - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Reading backup file details

Author

Topic

REDDY
Starting Member

43 Posts

Posted - 2005-05-23 : 13:09:29

Dear Friends
I am taking a FULL,diffrentail,Transactional backup to the same backupfile on to a different Sever as follows

FUll -- Daily (OverWrite)
Differential -- Every 3 hours (append)
Transactional -- Every 30 mins (append)

How can read the above file details programatically (T-sql) on that other server for restoration purpose ??

The file details I was looking for are file_number and type(Diff/tran) and created date.

Any help will be greately appriciated

Thanks
Reddy

nr
SQLTeam MVY

12543 Posts

Posted - 2005-05-23 : 13:30:08

try a restore headeronly

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

REDDY
Starting Member

43 Posts

Posted - 2005-05-23 : 14:59:41

Thanks for your input,
I am wondering,can we use RESTORE HEADERONLY with Just .BAK file on the other server where the actual backup was not taken??

I went through BOL for RESTORE HEADERONLY syntax,it says
RESTORE HEADERONLY
FROM < backup_device >

How can we read the header in this situation???

Thanks for all your help
Reddy

nr
SQLTeam MVY

12543 Posts

Posted - 2005-05-23 : 15:19:45

restore headeronly from disk='....'
will give you the list of backups in the file.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

REDDY
Starting Member

43 Posts

Posted - 2005-05-23 : 15:45:23

Thats what I was looking for
,it solved my problem.

Thank you verymuch sir

Reddy

REDDY
Starting Member

43 Posts

Posted - 2005-05-23 : 16:24:18

nr,
I need your help one more time,Sorry to bother you for too many times

I thougt I can send the resultset of the "restore headeronly" into a temp table easily,but it seems it is not that easy, could you please hint me how can I send the result set to a temp table??

Here I have the temp table with all the fields from "restore headeronly" result set

Create table #Filehdr
(
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)
)

thanks
Reddy

REDDY
Starting Member

43 Posts

Posted - 2005-05-23 : 16:44:53

OK got it

declare @a varchar(128)
select @a='restore headeronly from disk= ''C:\Backup\Test.BAK'''
insert #Filehdr exec (@a)

select * from #Filehdr

--------------------------

Thanks for all your help
Reddy

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources