This procedure should do what you want:
create procedure dbo.dbrestore
@dbname nvarchar(50)
as
declare @fileno integer
declare @dumpdevice nvarchar(50)
declare @mediasetid integer
declare @position integer
select top(1) @dumpdevice=a.logical_device_name, @mediasetid=b.media_set_id, @position=b.position from backupmediafamily a inner join backupset b on a.media_set_id=b.media_set_id
where b.database_name='backuptest' and b.type='D' order by a.media_set_id desc, b. position desc
declare restore_cursor cursor for
select position from msdb.dbo.backupset where database_name=@dbname and type='D' and media_set_id=@mediasetid and position=@position
union
select max(position) as position from msdb.dbo.backupset where database_name=@dbname and type='I' and media_set_id=@mediasetid and position>@position
union
select position from msdb.dbo.backupset where database_name=@dbname and type='L' and media_set_id=@mediasetid and position>@position
and last_lsn>(select max(last_lsn)from msdb.dbo.backupset where database_name=@dbname and type='I' and media_set_id=@mediasetid and position>@position)
order by position asc;
open restore_cursor
fetch next from restore_cursor into @fileno
while @@FETCH_STATUS = 0
begin
--comment the print statement and uncomment the exec statement to run
--exec('restore database ['+@dbname+'] from '+@dumpdevice+' with norecovery, replace, FILE='+@fileno+';')
print ('restore database ['+@dbname+'] from '+@dumpdevice+' with norecovery, replace, FILE='+convert(varchar(4),@fileno)+';')
fetch next from restore_cursor into @fileno
end
close restore_cursor
deallocate restore_cursor
--exec('restore database ['+@dbname+'] with recovery;')
go
execution goes:
get the name of the dumpdevice based on the supplied database name. I'm assuming that they are all on the same device. The top is to work around having multiple media sets per database if using 'with format' for the initial DB, and to cope with the possibility of multiple full backups in the device
Then build up a union query consisting of:
The last full database backup (type=D) in the device
the last differential backup (type=I and has the greatest LSN (Log Sequence Number)) taken after the last full db backup.
the transaction logs (type=L) taken after the above diff.
returning just the position value, which maps to the FILE value in this case (http://msdn.microsoft.com/en-us/library/ms186299.aspx)
Loop through the result restoring them individually, and then a final restore with recovery to finish it off.
Call it as:
exec dbrestore 'db2restore'
I've tried it on a test db and it appears to work happily, and comparing it to the files SSMS wants to restore for the same operation it looks the same.
Provided as is, there's probably typos, I've probably missed a possible boundary condition, and there's no error checking.
From your reply to my question, then it does appear to be too much backup history. For a one off cleardown, something along these lines will do it:
USE msdb
GO
DECLARE @days DATETIME
SET @days = DATEADD(day,-30,current_timestamp)
EXEC sp_delete_backuphistory @days
GO
That example gets rid of everything over 30 days old, so you may need to change the -30
for a value appropriate for your needs. But remember that you'll then have to build up any restores for backups over 30 days old from hand (may or may not be a problem for you, but thought I'd better mention it).
Going forward you can schedule that to run via a scheduled job to keep you small.
Best Answer
As no one got back to me (fair enough - maybe you've got jobs too!) I have tested this.
We didn't use this method in the end for the production task because the powers that be decided it was ok/best to use 7-Zip to split the single .bak file.
However, the answer is - you only need to specify the a file of the set and it works fine (I did this on my test setup):
Restore as normal > when specifying the Backup to use as the initialization point, give it the first file name.
I assume this is because the LSN is listed in this file (whether it contains the right bit of the Log data or not is irrelevant - as it's only looking for a point to reference through the Log Reader agent).
In fact to double check this, I have just gone back to my test setup and commenced replication (P2P in this case as in SQL 2008R2 it allows you to specify the Backup file through the SSMS GUI Wizard - call me lazy!) by using the 3rd file in my 4 file backup set, which worked fine. I believe this proves that the LSN is in the header of each file in the Backup set and also why using a Log backup or Differential backup also works.
Just to make sure that anyone in the future that needs the same answer to the multi-file backup initialization question can find this - I need to include the initial question as I would have asked it so they find this when they search:
Q: Can I use a split Backup set to Initialize replication?
A: Yes you can, simply specify any of the bak files used in the Restore to initialize.