SQL 2005 Restore Script Generator

SET NOCOUNT ON
GO
/******************************************************************************
**
**    Name: Quick-n-Diry restore script generator.sql
**
**    Description: Quickly generate restore script from a backup device.
**
**         Generate a restore script with the proper move
**         statements automatically.
**
**         Change values for:
**
**             @i_db_name -- Database name to restore as.
**             @FilePath -- Backup directory
**            @BackupFileName -- Backup filename
**             @i_data_location -- Restore to path.
**
**
**    Return values: 0 - Success
**            -1 - Error
**
**
**    Author: G. Rayburn
**
**    Date: 01/27/2006
**
*******************************************************************************
**        Modification History
*******************************************************************************
**
**    Initial Creation: 01/27/2006 G. Rayburn
**             03/16/2007 G. Rayburn -- Fixed ALTER issue for SQL2000.
**             01/25/2008 G. Rayburn -- Added RESTORE LABELONLY code.
**
*******************************************************************************
**
******************************************************************************/DECLARE @retcode int
        , @l_bkup_file_nm varchar(520)
        , @l_bkup_file_location varchar(520)
        , @l_index int
        , @l_exec_stmt varchar(8000)
        , @l_Old_file_location varchar(520)
        , @l_New_file_location varchar(520)
        , @l_file_nm varchar(520)
        , @l_file_location varchar(520)
        , @l_LogicalName varchar(256)
        , @l_exec_stmt_len int
        , @i_backup varchar(8000)
        , @i_db_name sysname
        , @i_data_location varchar(8000)
        , @RestoreLABELONLY varchar(1024)
        , @RestoreLABELONLYCMD varchar(2048)
        , @FilePath varchar(1024)
        , @BackupFileName varchar(128)
        , @FamilyCount int
        , @CurrLoopOp int
        , @DynString_01 varchar(8000)
        , @RestoreStr_01 varchar(512)
        , @RestoreStr_02 varchar(512)
        , @FileExistCheck varchar(1024)

-- Change these variables:
SET @i_db_name = 'YourDBName'
SET @FilePath = '\\Server\Share\'
SET @BackupFileName = 'FooDB_20080125_01.BAK'
SET @i_data_location = 'D:\MSSQL\Data'


-- Don't change.
SET @l_index = 0
SET @l_bkup_file_nm = '''' + @FilePath + @BackupFileName + ''''
SET @FileExistCheck = @FilePath + @BackupFileName
SET @RestoreLABELONLY = 'RESTORE LABELONLY FROM DISK = '
SET @RestoreLABELONLYCMD = @RestoreLABELONLY + '''' + @FilePath + @BackupFileName + ''''


-- -- Verify backup file exists.
EXEC master..xp_fileexist @FileExistCheck, @retcode OUTPUT
    IF (@retcode) = 0
        BEGIN
            PRINT @FileExistCheck + ' does not exist, exiting process.'
            PRINT ''
            RETURN
        END



-- Gather # of backup files and build FROM DISK string:
CREATE TABLE #_RESTORE_LABELONLY
    (
     [MediaName] nvarchar(128)
    , [MediaSetId] uniqueidentifier
    , [FamilyCount] int
    , [FamilySequenceNumber] int
    , [MediaFamilyId] uniqueidentifier
    , [MediaSequenceNumber] int
    , [MediaLabelPresent] tinyint
    , [MediaDescription] nvarchar(255)
    , [SoftwareName] nvarchar(128)
    , [SoftwareVendorId] int
    , [MediaDate] datetime
    , [Mirror_Count] int
    )

INSERT INTO #_RESTORE_LABELONLY
    EXEC (@RestoreLABELONLYCMD)

SET @FamilyCount = (SELECT FamilyCount FROM #_RESTORE_LABELONLY)

SET @CurrLoopOp = 2 -- Have to start at 2 because 1 is being used by @RestoreStr_01.
SET @DynString_01 = ''

SELECT @RestoreStr_01 = 'FROM DISK = ''' + @FilePath + @BackupFileName + '''' + char(10) + char(13)
    , @RestoreStr_02 = ', DISK = ''' + @FilePath 

WHILE @CurrLoopOp <= @FamilyCount
    BEGIN

        SET @DynString_01 = @DynString_01 + @RestoreStr_02 + STUFF(@BackupFileName,LEN(@BackupFileName)-4,1,CONVERT(char(1),@CurrLoopOp)) + '''' + char(10) + char(13)

        SET @CurrLoopOp = @CurrLoopOp + 1
            IF @CurrLoopOp > @FamilyCount
                BREAK
            ELSE
                CONTINUE
    END



-- Get information on logical names and paths from the backup itself.
IF EXISTS (SELECT * FROM tempdb..sysobjects WHERE NAME LIKE '#bkup_file_details%' )
    DROP TABLE #bkup_file_details

SET NOCOUNT ON

CREATE TABLE #bkup_file_details
    (
     LogicalName nvarchar(128)
    ,Old_PhysicalName nvarchar(128)
    ,[Type] char(1)
    ,FileGroupName nvarchar(128)
    ,[Size] numeric(20,0)
    ,[MaxSize] numeric(20,0)
    ,FileID bigint
    ,CreateLSN numeric(25,0)
    ,DropLSN numeric(25,0) NULL
    ,UniqueID uniqueidentifier
    ,ReadOnlyLSN numeric(25,0)
    ,ReadWriteLSN numeric(25,0)
    ,BackupSizeInByte bigint
    ,SourceBlockSize int
    ,FilegroupID int
    ,LogGroupGUID uniqueidentifier NULL
    ,DifferentialBaseLSN numeric(25,0)
    ,DifferentialbaseGUID uniqueidentifier
    ,IsReadOnly bit
    ,IsPresent bit
    )

-- Get number and names of the files in the backup.
SELECT @l_exec_stmt = 'RESTORE FILELISTONLY FROM DISK = ''' + @FilePath + @BackupFileName + ''''

INSERT INTO #bkup_file_details 
    EXEC (@l_exec_stmt)

-- Add New_PhysicalName for cursor later on.
ALTER TABLE #bkup_file_details
    ADD New_PhysicalName varchar(128)

-- Update New_PhysicalName with the target path.
SELECT @retcode = 0

DECLARE file_details scroll CURSOR 
    FOR SELECT Old_PhysicalName
        FROM #bkup_file_details

    OPEN file_details 

        WHILE @retcode = 0
            BEGIN
                FETCH NEXT FROM file_details 
                INTO @l_Old_file_location
        
        IF(@@fetch_status <> 0)
            BREAK

                SELECT @l_file_nm = @l_Old_file_location
                SELECT @l_index = charindex('\', @l_file_nm)

            WHILE(@l_index <> 0)
                BEGIN
                    SELECT @l_file_nm = right(@l_file_nm, datalength(@l_file_nm)- @l_index)
                    SELECT @l_index = charindex('\', @l_file_nm)
                END

                SELECT @l_New_file_location = @i_data_location + '\' + @i_db_name + '_' + @l_file_nm

                UPDATE #bkup_file_details
                    SET New_PhysicalName = @l_New_file_location 
                    WHERE Old_PhysicalName = @l_Old_file_location

            END

CLOSE file_details 
DEALLOCATE file_details


-- Generate first portion of the restore statement.
SELECT @l_exec_stmt = 'RESTORE DATABASE [' + @i_db_name + ']

' + @RestoreStr_01 + @DynString_01 + '

     WITH RECOVERY
    , REPLACE
    , STATS = 5'

-- Generate the MOVE statements and append them to the first.
DECLARE file_details scroll CURSOR 
    FOR SELECT LogicalName
            , New_PhysicalName
        FROM #bkup_file_details

    OPEN file_details 

    WHILE @retcode = 0
        BEGIN
            FETCH next FROM file_details 
            INTO @l_LogicalName,
            @l_file_location

    IF(@@fetch_status <> 0)
        BREAK

            SELECT @l_exec_stmt = @l_exec_stmt + '
    , MOVE ''' + @l_LogicalName + ''' TO ''' + @l_file_location + ''''

        END

CLOSE file_details 
DEALLOCATE file_details 

-- DEBUG:
PRINT ''
PRINT (@l_exec_stmt)
PRINT ''


DROP TABLE #_RESTORE_LABELONLY