I was recently tasked with creating a procedure for restoring a MSSQL databases that can have up to 47 trn files.
The scenario: A full database backup happens each night with transaction backups happening every 30 mins after that. The SQL Server Management Studio interface allows you to restore the files but you have to do it one at a time. When you have 6 databases with 6 full backups and up to 282 trn files, doing things one at a time is unacceptable. In the end i created the following SQL Script:
** NOTE: This only works on SQL 2005 and above because of the naming defaults imposed by SQL 2000.
SQL 1 - turn on xp_cmdshell
-- To allow advanced options to be changed.
EXEC sp_configure 'show advanced options', 1
GO
-- To update the currently configured value for advanced options.
RECONFIGURE
GO
-- To enable the feature.
EXEC sp_configure 'xp_cmdshell', 1
GO
-- To update the currently configured value for this feature.
RECONFIGURE
GO
SQL 2 - Restore SQL Databases
USE Master;
GO
SET NOCOUNT ON
-- 1 - Variable declaration
DECLARE @dbName sysname
DECLARE @backupPath NVARCHAR(500)
DECLARE @MDFLDFPath NVARCHAR(500)
DECLARE @cmd NVARCHAR(500)
DECLARE @fileList TABLE (backupFile NVARCHAR(255))
DECLARE @lastFullBackup NVARCHAR(500)
DECLARE @lastDiffBackup NVARCHAR(500)
DECLARE @backupFile NVARCHAR(500)
-- 2 - Initialize variables
-- Database Container Name
SET @dbName = 'DATABASE'
-- .BAK & .TRN Directory
SET @backupPath = N'C:\backup\DATABASE\'
-- Final MDF/LDF Directory.
-- ** Directory must exists prior to running script**
SET @MDFLDFPath = N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\'
-- 3 - get list of files
SET @cmd = 'DIR /b ' + @backupPath
INSERT INTO @fileList(backupFile)
EXEC master.sys.xp_cmdshell @cmd
-- 4a - Find latest full backup
SELECT @lastFullBackup = MAX(backupFile)
FROM @fileList
WHERE backupFile LIKE '%.BAK'
AND backupFile LIKE @dbName + '%'
-- 4b - Get the names of the MDF/LDF files from the backup
DECLARE @LogicalFileNameMDF NVARCHAR(128)
DECLARE @LogicalFileNameLDF NVARCHAR(128)
declare @fileListTable table
(
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,
IsReadOnl bit,
IsPresent bit,
TDEThumbprint varbinary(32)
)
INSERT INTO @fileListTable EXEC('restore filelistonly from disk = ''' + @backupPath + @lastFullBackup + '''')
SELECT @LogicalFileNameMDF = LogicalName
FROM @fileListTable
WHERE TYPE = 'D'
SELECT @LogicalFileNameLDF = LogicalName
FROM @fileListTable
WHERE TYPE = 'L'
SET @cmd = 'RESTORE DATABASE ' + @dbName + ' FROM DISK = '''
+ @backupPath + @lastFullBackup + ''' WITH MOVE '''
+ @LogicalFileNameMDF + ''' TO ''' + @MDFLDFPath + @dbName + '.MDF'', MOVE '''
+ @LogicalFileNameLDF + ''' TO ''' + @MDFLDFPath + @dbName + '.LDF'', NORECOVERY , REPLACE'
PRINT @cmd
-- 4 - Find latest diff backup
SELECT @lastDiffBackup = MAX(backupFile)
FROM @fileList
WHERE backupFile LIKE '%.DIF'
AND backupFile LIKE @dbName + '%'
AND backupFile > @lastFullBackup
-- check to make sure there is a diff backup
IF @lastDiffBackup IS NOT NULL
BEGIN
SET @cmd = 'RESTORE DATABASE ' + @dbName + ' FROM DISK = '''
+ @backupPath + @lastDiffBackup + ''' WITH NORECOVERY'
PRINT @cmd
SET @lastFullBackup = @lastDiffBackup
END
-- 5 - check for log backups
DECLARE backupFiles CURSOR FOR
SELECT backupFile
FROM @fileList
WHERE backupFile LIKE '%.TRN'
AND backupFile LIKE @dbName + '%'
AND backupFile > @lastFullBackup
OPEN backupFiles
-- Loop through all the files for the database
FETCH NEXT FROM backupFiles INTO @backupFile
WHILE @@FETCH_STATUS = 0
BEGIN
SET @cmd = 'RESTORE LOG ' + @dbName + ' FROM DISK = '''
+ @backupPath + @backupFile + ''' WITH NORECOVERY'
PRINT @cmd
FETCH NEXT FROM backupFiles INTO @backupFile
END
CLOSE backupFiles
DEALLOCATE backupFiles
-- 6 - put database in a useable state
SET @cmd = 'RESTORE DATABASE ' + @dbName + ' WITH RECOVERY'
PRINT @cmd
