4

I'm using SQL Server (2008 R2 in this case) and I am backing up my databases into backup devices (one per database). Every Sunday the device is overwritten with a new full backup, every night a differential backup is added and every hour a transaction log backup is added to the backup device.

When restoring this onto a new server using SSMS I first create a new database, then add the backup device and then restore from that backup device. I then have to manually select the applicable files:

  • The Full backup (always the first one in the list)
  • The latest differential backup
  • All transaction log backups that came after the last differential backup.

enter image description here

This works fine for the occassional restore, but I need to automate this in T-SQL.

There is some logic on which files to pick, but I don't even know how to get to the content of the backup device yet.

I would like to have a stored procedure that takes the name of the database and the backup device and restores the latest version. Anybody knows of such a thing?

Or do I miss something here and there is a simple command for this?

Improve this question

2 Answers 2

Reset to default
3

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.

Improve this answer
1
  • Thanks Stuart, this was helpful in some ways, but it just works for backup devices that were created on the same server. It uses information about the devices from system tables, which it seems get in there when the backup is running on the same server. Adding a device to the server, still doesn't put the information about the files into the msdb tables. Because the restore happens on a different server I had to read the content of the device from the device itself (see my answer). Thanks anyway. Commented Sep 26, 2012 at 7:26
1

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
Improve this answer

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.