SQL 2000 Restore with Multiple .trn files

My previous blog post showed how to script a restore for SQL Server 2005 and above.  Below is a modified script which works on SQL Server 2000.  Basically i had to use temp tables instead of dynamic variables and I had to deal with SQL Server 2000's wacky backup file naming convention.

SQL:


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)

CREATE TABLE #fileList (backupFile NVARCHAR(255))

-- 2 - Initialize variables
-- Database Container Name
SET @dbName = 'ProgramPlan'

-- .BAK & .TRN Directory
SET @backupPath = N'C:\backup\sql\ProgramPlan\'

-- Final MDF/LDF Directory.
-- ** Directory must exists prior to running script**
SET @MDFLDFPath = N'C:\Program Files\Microsoft SQL Server\MSSQL\Data\'


-- 3 - get list of files
SET @cmd = 'DIR /b ' + @backupPath
INSERT INTO #fileList(backupFile)
EXEC 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)

Create TABLE #fileListTable
(
    LogicalName          nvarchar(128),
    PhysicalName         nvarchar(260),
    [Type]               char(1),
    FileGroupName        nvarchar(128),
    Size                 numeric(20,0),
    MaxSize              numeric(20,0),
   
)
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 RIGHT(backupFile,12) > RIGHT(@lastFullBackup,12)
-- 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 RIGHT(backupFile,12) > RIGHT(@lastFullBackup,12)
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

drop table #fileList
drop table #fileListTable