quote:

---

Originally posted by tkizer
It would be fairly easy to script this though if you hadn't appended the files and instead backed up to individual files with the timestamp in the file names.

---

quote:

---

Originally posted by AdrenalineSeed

Hi tkizer,

That drop down is blank. I am not working with the original database, it is up-to-date.

I am trying to update my copy of the database restored from a backup made in the past. It has a different name, resides on a different server.

---

quote:

---

Originally posted by Haywood

quote:

---

Originally posted by tkizer
It would be fairly easy to script this though if you hadn't appended the files and instead backed up to individual files with the timestamp in the file names.

---

It's still fairly easy to iterate through the single file using the "Position" value in an incremental loop...

---

quote:

---

Originally posted by tkizer

quote:

---

Originally posted by AdrenalineSeed

Hi tkizer,

That drop down is blank. I am not working with the original database, it is up-to-date.

I am trying to update my copy of the database restored from a backup made in the past. It has a different name, resides on a different server.

---

You'll need to script it then. How many backups are in the appended file?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Database maintenance routines:
http://weblogs.sqlteam.com/tarad/archive/2004/07/02/1705.aspx

---

quote:

---

Originally posted by tkizer

quote:

---

Originally posted by Haywood

quote:

---

Originally posted by tkizer
It would be fairly easy to script this though if you hadn't appended the files and instead backed up to individual files with the timestamp in the file names.

---

It's still fairly easy to iterate through the single file using the "Position" value in an incremental loop...

---

Yep but I don't have a handy script for that since I don't append backups ever.

---

quote:

---

Originally posted by tkizer

Are the backups in the correct order in the appended file, meaning the full backup is first, the first log backup is second, the second log backup is third, ...?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Database maintenance routines:
http://weblogs.sqlteam.com/tarad/archive/2004/07/02/1705.aspx

---

--
-- G. Rayburn 
--
-- 6/14/2004
--
--
--
-- 1: Restore DB from backup WITH NORECOVERY
-- 2: Restore t-logs from @iMin & @iMax File # (Internal backup sequence)
--
--
-- RESTORE HEADERONLY FROM <Database Log Device>  -- You want the Position values for the first & last logs to restore.
--

DECLARE @szDBName VARCHAR(50),
	@szDBLogDevice VARCHAR(50),
	@iCnt INT,
	@iMinPosition INT,
	@iMaxPosition INT

SELECT  @szDBName = 'YourDBName',
	@szDBLogDevice = 'YourDB_Log_Backup_Device',
	@iCnt = @iMinPosition,
	@iMinPosition = X,  -- 1
	@iMaxPosition = XX -- 999


WHILE @iCnt < @iMaxPosition

	BEGIN
		PRINT ''
		PRINT 'Restoring t-log file #' + CONVERT(CHAR(3),@iCnt) + 'at ' + CONVERT(VARCHAR(26),GETDATE(),109) + '.'
		PRINT ''

		RESTORE LOG @szDBName
		FROM @szDBLogDevice
		WITH 
			FILE = @iCnt,
			NORECOVERY

		SET @iCnt = @iCnt + 1

		IF @iCnt = @iMaxPosition

			BEGIN

				PRINT ''
				PRINT 'Restoring last file and bringing database online at ' + CONVERT(VARCHAR(26),GETDATE(),109) + '.'
				PRINT ''
	
				RESTORE LOG @szDBName 
				FROM @szDBLogDevice
				WITH
					FILE = @iMaxPosition,
					RECOVERY

				BREAK
			END

		ELSE

			CONTINUE

	END

create table #filelistonly
(logicalname nvarchar(128), physicalname nvarchar(260), type char(1), filegroupname nvarchar(128),
size numeric(20,0), maxsize numeric(20,0), fileid bigint, createlsn numeric(25,0), droplsn numeric(25,0), 
uniqueid uniqueidentifier, readonlylsn numeric(25,0), readwritelsn numeric(25,0), 
backupsizeinbytes bigint, sourceblocksize int, filegroupid int, loggroupguid uniqueidentifier,
differentialbaselsn numeric(25,0), differentialbaseguid uniqueidentifier, isreadonly bit, ispresent bit)

insert into #filelistonly
exec ('restore filelistonly from disk =''E:\AppendFile.bak''')

select 'restore database blah from disk = ''E:\blah.bak'' with norecovery'
union all
select 'restore log blah from disk = ''E:\AppendFile.bak''' + ' with norecovery, file = ' + convert(varchar(20), fileid)
from #filelistonly
order by fileid

drop table #filelistonly

quote:

---

Originally posted by tkizer

Here's what I wrote up real quick. I wrote this for SQL Server 2005. If it doesn't work for 2000, then you'll need to change the #filelistonly structure to match the output of RESTORE FILELISTONLY in 2000. SQL Server Books Online would have the details.

create table #filelistonly
(logicalname nvarchar(128), physicalname nvarchar(260), type char(1), filegroupname nvarchar(128),
size numeric(20,0), maxsize numeric(20,0), fileid bigint, createlsn numeric(25,0), droplsn numeric(25,0), 
uniqueid uniqueidentifier, readonlylsn numeric(25,0), readwritelsn numeric(25,0), 
backupsizeinbytes bigint, sourceblocksize int, filegroupid int, loggroupguid uniqueidentifier,
differentialbaselsn numeric(25,0), differentialbaseguid uniqueidentifier, isreadonly bit, ispresent bit)

insert into #filelistonly
exec ('restore filelistonly from disk =''E:\AppendFile.bak''')

select 'restore database blah from disk = ''E:\blah.bak'' with norecovery'
union all
select 'restore log blah from disk = ''E:\AppendFile.bak''' + ' with norecovery, file = ' + convert(varchar(20), fileid)
from #filelistonly

drop table #filelistonly

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Database maintenance routines:
http://weblogs.sqlteam.com/tarad/archive/2004/07/02/1705.aspx

---

quote:

---

Originally posted by AdrenalineSeed

I don't understand how to get the Positions his script needs.

---