I ended up writing my own solution, I have just started testing it, but it looks good. All I need is the backup device file and I can go:
EXEC [dbo].[dba_RestoreFromBackupDevice] @DBName = 'test', @File = 'C:\test.bak', @WhatIf = 1
It will create the database, then read the content of the backup device and figure out which of the files to restore. Using @WhatIf it spits out sql like this:
USE master;
CREATE DATABASE [lab];
RESTORE DATABASE [lab] FROM DISK = N'C:\test.bak' WITH FILE = 1, NORECOVERY, NOUNLOAD, REPLACE, STATS = 10;
RESTORE DATABASE [lab] FROM DISK = N'C:\test.bak' WITH FILE = 49, NORECOVERY, NOUNLOAD, REPLACE, STATS = 10;
RESTORE LOG [lab] FROM DISK = N'C:\test.bak' WITH FILE = 50, NORECOVERY, NOUNLOAD, REPLACE, STATS = 10;
RESTORE LOG [lab] FROM DISK = N'C:\test.bak' WITH FILE = 51, NORECOVERY, NOUNLOAD, REPLACE, STATS = 10;
RESTORE database [lab] WITH RECOVERY;
With @WhatIf = 0 or missing, it executes the restore process right away.
Here's the full stored procedure:
/* ========================================================
Author: Peter Hahndorf
Creation date: 25-Sep-2012
Restores a database from a backup device with multiple files.
Uses the first file, the lastest differential files and
all further transactional log files.
Creates the database if not already there.
Use @WhatIf = 1 to just print the SQL statement without
executing it.
========================================================= */
ALTER PROCEDURE [dbo].[dba_RestoreFromBackupDevice]
@DBName varchar(100),
@File varchar(250),
@WhatIf bit = 0
AS
BEGIN
-- We need a table to store the backup devices file data
-- This table is for SQL Server 2008R2 earlier versions
-- have less fields, later may have more.
DECLARE @Headers TABLE (
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),
DifferentialBackupLsn 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),
FamilyGUID uniqueidentifier,
HasBulkLoggedData bit,
IsSnapshot bit,
IsReadOnly bit,
IsSingleUser bit,
HasBackupChecksums bit,
IsDamaged Int,
BeginsLogChain bit,
HAsIncompleteMetaData bit,
IsForceOFfline bit,
IsCopyOnly bit,
FirstRecoveryForkID uniqueidentifier,
ForkPointLSN numeric(25,0),
RecoveryModel nvarchar(60),
DifferentialBaseLSN numeric(25,0),
DifferentialBAseGUID uniqueidentifier,
BackupTypeDescription nvarchar(60),
BackupSetGUID uniqueidentifier,
CompressedBackupSize bigint
);
-- this gets the content of the backup device and puts it in
-- a table so we can work with it.
INSERT @Headers EXEC ('RESTORE HEADERONLY FROM DISK=''' + @File + '''')
/*
The device may have one of the following combinations of files:
Full only
Full and at least one log
Full, at least one diff and some further logs
Full, at least one diff and no further logs
*/
-- We store all our commands in a string and then either print or
-- execute it at the end
DECLARE @SQL varchar(max)
SET @SQL = 'USE master; '
IF NOT EXISTS (SELECT name FROM sys.databases WHERE name = @DBName )
BEGIN
SET @SQL = @SQL + 'CREATE DATABASE [' + @DBName + ']; '
END
-- Always restore the first file, it is the only full backup in the device
SET @SQL = @SQL + 'RESTORE DATABASE [' + @DBName + '] FROM DISK = N''' + @File + ''' WITH FILE = 1, NORECOVERY, NOUNLOAD, REPLACE, STATS = 10; '
-- Find the last differential backup, the highest position of any type 5 files
-- If there is none, we assign a one, which makes it the same as the full one
DECLARE @DiffFileNo INT
SELECT @DiffFileNo = (SELECT ISNULL(MAX(Position),1) FROM @Headers WHERE BackupType = 5)
-- If there is one, restore it
IF @DiffFileNo > 1
BEGIN
SET @SQL = @SQL + 'RESTORE DATABASE [' + @DBName + '] FROM DISK = N''' + @File + ''' WITH FILE = ' + CAST(@DiffFileNo AS VARCHAR) + ', NORECOVERY, NOUNLOAD, REPLACE, STATS = 10;'
END
-- now we also need all log files that are newer/greater than the last differential
DECLARE @fileno INT
DECLARE restore_cursor CURSOR FOR
SELECT Position FROM @Headers WHERE Position > @DiffFileNo ORDER BY Position
OPEN restore_cursor
FETCH NEXT FROM restore_cursor INTO @fileno
WHILE @@FETCH_STATUS = 0
BEGIN
SET @SQL = @SQL + 'RESTORE LOG [' + @DBName + '] FROM DISK = N''' + @File + ''' WITH FILE = ' + CAST(@fileno AS VARCHAR) + ', NORECOVERY, NOUNLOAD, REPLACE, STATS = 10; '
FETCH NEXT FROM restore_cursor INTO @fileno
END
CLOSE restore_cursor
DEALLOCATE restore_cursor
-- finally we have to finish the restore
SET @SQL = @SQL + 'RESTORE database [' + @DBName + '] WITH RECOVERY; '
IF @WhatIf = 1
BEGIN
PRINT @SQL
END
ELSE
BEGIN
EXEC(@SQL)
END
END