Getting the FileID from the Restore HeaderOnly

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)

Getting the FileID from the Restore HeaderOnly

Author

Topic

DBASlut
Yak Posting Veteran

71 Posts

Posted - 2006-06-05 : 16:07:33

I want to grab the most recent diff backup file from a .bak.
I can run the ReSTORE HEADERONLY but how can I just get the highest FileID? Can I put the info returned from the statement into a temp? and query that? I'm attempting to build a dynamic restore cursor..
This is what I have..
DECLARE @FILEID INT, @ERRORNBR INT, @SSQL NVARCHAR(512), @DBNAME VARCHAR(50), @err int
SET NOCOUNT ON

DECLARE DB_NAME_CURSOR CURSOR FOR

SELECT [NAME] FROM SYSDATABASES WHERE STATUS =2098176

OPEN DB_NAME_CURSOR

FETCH NEXT FROM DB_NAME_CURSOR
INTO @DBNAME

WHILE @@FETCH_STATUS = 0

BEGIN
SELECT @FILEID=8, @ERRORNBR =10
WHILE @ERRORNBR <> 0
BEGIN
SET @SSQL ='RESTORE DATABASE ' + @DBNAME + ' FROM DISK= ''D:\Backups\Diff\' + @DBNAME + '_Diff.bak'' WITH FILE =' + CAST(@FILEID AS VARCHAR(10)) + ' , STANDBY =''C:\' + @DBNAME + '_Diff.ldf'''

EXEC @err = sp_executesql @SSQL
SET @ERRORNBR = @err
SET @FILEID = @FILEID - 1
PRINT @ERRORNBR
END
FETCH NEXT FROM DB_NAME_CURSOR
INTO @DBNAME
END
CLOSE DB_NAME_CURSOR
DEALLOCATE DB_NAME_CURSOR

DBASlut
Yak Posting Veteran

71 Posts

Posted - 2006-06-05 : 16:09:12

This works in that it attempts to restore with the FILEID, counting from 8 down to a FileID that it can successfully restore. HOwever when I have it in a JOB, the job will error out. In the Query Analyzer window, it will error out but keep going until it finds a FILEID it can successfully restore.

Kristen
Test

22859 Posts

Posted - 2006-06-05 : 23:53:43

If your RESTORE could work on FileNames / Timestamps alone then this may help:

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=60109

Kristen

DBASlut
Yak Posting Veteran

71 Posts

Posted - 2006-06-08 : 12:11:59

Thanks Kristen,
what I ended up doing is putting the data from reading the HeaderOnly info into a table, then selecting the Max(FileID) from the table.
If you have more than on database on the server that you're going to apply the Diff to, then it will cursor through these databases. The table will get populated for each database DIff file, truncated after it applies the restore, then populates it for the next db. I tried to paste the code in here but it looked horrible.

Kristen
Test

22859 Posts

Posted - 2006-06-08 : 14:30:31

"I tried to paste the code in here but it looked horrible."

Putting [code] tags around it will use a fixed-width font - if that helps?

Kristen

DBASlut
Yak Posting Veteran

71 Posts

Posted - 2006-06-16 : 12:30:41

[code]declare @FILEID int, @ERRORNBR int, @SSQL nvarchar(512), @DBNAME varchar(50), @DW int, @SSQL2 nvarchar(1000)

set nocount on
select @DW=datepart(DW,getdate())
IF @DW <> 1
BEGIN
SET @SSQL2='IF EXISTS (select id FROM sysobjects WHERE name = ''FILEGROUPINFO'')
DROP TABLE FILEGROUPINFO'
EXEC sp_executesql @SSQL2
SET @SSQL2=' create table FILEGROUPINFO (backupname nvarchar(128),backupdescription nvarchar(255),backuptype int,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),DifferentialLSN 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))'
EXEC sp_executesql @SSQL2

declare DB_NAME_CURSOR CURSOR FOR

select [name] FROM sysdatabases WHERE name NOT IN ('master','tempdb','model','msdb')

OPEN DB_NAME_CURSOR

FETCH NEXT FROM DB_NAME_CURSOR
INTO @DBNAME

WHILE @@FETCH_STATUS = 0

BEGIN
SET @SSQL =' RESTORE HEADERONLY FROM disk=''D:\Backups\Diff\' + @DBNAME + '_diff.bak'' '
INSERT FILEGROUPINFO
EXEC sp_executesql @SSQL

select @FILEID=MAX(position) FROM FILEGROUPINFO
SET @SSQL ='RESTORE DATABASE [' + @DBNAME + '] FROM DISK= ''D:\Backups\Diff\' + @DBNAME + '_Diff.bak'' WITH FILE =' + CAST(@FILEID AS varchar(10)) + ' , STANDBY =''C:\' + @DBNAME + '_Diff.ldf'''
EXEC sp_executesql @SSQL

TRUNCATE TABLE FILEGROUPINFO

FETCH NEXT FROM DB_NAME_CURSOR
INTO @DBNAME
END
CLOSE DB_NAME_CURSOR
DEALLOCATE DB_NAME_CURSOR
END
SET @SSQL2='IF EXISTS (select id FROM sysobjects WHERE name = ''FILEGROUPINFO'')
DROP TABLE FILEGROUPINFO'
EXEC sp_executesql @SSQL2
[/code]
Thanks Kristen..Muah...
Sorry for the mixed case

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources