SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 Transact-SQL (2000)
 Getting the FileID from the Restore HeaderOnly
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

DBASlut
Yak Posting Veteran

71 Posts

Posted - 06/05/2006 :  16:07:33  Show Profile  Reply with Quote
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 - 06/05/2006 :  16:09:12  Show Profile  Reply with Quote
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.
Go to Top of Page

Kristen
Test

United Kingdom
22415 Posts

Posted - 06/05/2006 :  23:53:43  Show Profile  Reply with Quote
If your RESTORE could work on FileNames / Timestamps alone then this may help:

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

Kristen
Go to Top of Page

DBASlut
Yak Posting Veteran

71 Posts

Posted - 06/08/2006 :  12:11:59  Show Profile  Reply with Quote
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.

Edited by - DBASlut on 06/08/2006 12:13:31
Go to Top of Page

Kristen
Test

United Kingdom
22415 Posts

Posted - 06/08/2006 :  14:30:31  Show Profile  Reply with Quote
"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

Edited by - Kristen on 06/08/2006 14:30:47
Go to Top of Page

DBASlut
Yak Posting Veteran

71 Posts

Posted - 06/16/2006 :  12:30:41  Show Profile  Reply with Quote
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

Thanks Kristen..Muah...
Sorry for the mixed case
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.11 seconds. Powered By: Snitz Forums 2000