[SQL Server]從備份檔bak產生完整Restore命令

　　之前一直在異機還原，要還原的DB往往拆成好多個data file，異機的磁碟分區又不一樣，還原就很麻煩了，要改路徑，不知為什用SSMS產生的Restore命令竟不完全，當時上網有找到一位大大寫好的SP，可從BAK產生Restore命令，可是我忘記是從哪找到的，最近剛好套用到SQL 2016時會出錯，發現是RESTORE FILELISTONLY的結果集不太一樣，於是改了一下再放上來給有需要的人

USE [master]

GO

CREATE PROC [dbo].[Produce_restoreDB_cmd]

    @p_strDBNameTo SYSNAME, -- New DB Name

    @p_strDBNameFrom SYSNAME, -- Old DB Name

     @p_strDataFileFolder NVARCHAR(200), -- New DB Data file path

     @p_strLogFileFolder NVARCHAR(200), -- New DB Log file path

    @p_strFQNRestoreFileName VARCHAR(255) -- Backup File

AS 

  SET NOCOUNT ON;

--Usage:

--exec [dbo].[Produce_restoreDB_cmd] 'NEW_DB_NAME','OLD_DB_NAME',null,null,'F:\Backup\LOGDB.BAK'

--exec [dbo].[Produce_restoreDB_cmd] 'NEW_DB_NAME','OLD_DB_NAME','X:\DATAFILE\','Y:\LOGFILE\','F:\Backup\LOGDB.BAK'

    DECLARE

        @v_strExecSQL NVARCHAR(1000),

        @v_strExecSQL1 NVARCHAR(1000),

        @v_strMoveSQL NVARCHAR(4000),

        @v_strREPLACE NVARCHAR(50),

        @v_strTEMP NVARCHAR(1000),

        @v_strListSQL NVARCHAR(4000),

        @v_strServerVersion NVARCHAR(20),

         @v_strFile NVARCHAR(100),

         @v_strFolder NVARCHAR(200),

         @v_strLogical NVARCHAR(128),

         @v_strType NCHAR(1);

     DECLARE @CR AS CHAR(1)    -- Carriage Return (CR)

     DECLARE @LF AS CHAR(1)    -- Line Feed (LF)

     DECLARE @CrLf AS CHAR(2)  -- Carriage Return / Line Feed

     SET @CR = CHAR(10)

     --SET @LF = CHAR(13)

     SET @CrLf = @CR

    IF RIGHT(@p_strDataFileFolder,1) <> '\'

         SET @p_strDataFileFolder = @p_strDataFileFolder + '\'

    

     IF RIGHT(@p_strLogFileFolder,1) <> '\'

         SET @p_strLogFileFolder = @p_strLogFileFolder + '\'

    

    IF OBJECT_ID('tempdb..##FILE_LIST') IS NOT NULL

         DROP TABLE ##FILE_LIST

 

    SET @v_strREPLACE = ''  

    IF exists (select name from sys.databases where name = @p_strDBNameTo)

        SET @v_strREPLACE = ', REPLACE'

       

     SET @v_strListSQL = ''

    SET @v_strListSQL = @v_strListSQL + 'CREATE TABLE ##FILE_LIST ('

    SET @v_strListSQL = @v_strListSQL + '   LogicalName VARCHAR(64),'

    SET @v_strListSQL = @v_strListSQL + '   PhysicalName VARCHAR(130),'

    SET @v_strListSQL = @v_strListSQL + '   [Type] VARCHAR(1),'

    SET @v_strListSQL = @v_strListSQL + '   FileGroupName VARCHAR(64),'

    SET @v_strListSQL = @v_strListSQL + '   Size DECIMAL(20, 0),'

    SET @v_strListSQL = @v_strListSQL + '   MaxSize DECIMAL(25,0),'

    SET @v_strListSQL = @v_strListSQL + '   FileID bigint,'

    SET @v_strListSQL = @v_strListSQL + '   CreateLSN DECIMAL(25,0),'

    SET @v_strListSQL = @v_strListSQL + '   DropLSN DECIMAL(25,0),'

    SET @v_strListSQL = @v_strListSQL + '   UniqueID UNIQUEIDENTIFIER,'

    SET @v_strListSQL = @v_strListSQL + '   ReadOnlyLSN DECIMAL(25,0),'

    SET @v_strListSQL = @v_strListSQL + '   ReadWriteLSN DECIMAL(25,0),'

    SET @v_strListSQL = @v_strListSQL + '   BackupSizeInBytes DECIMAL(25,0),'

    SET @v_strListSQL = @v_strListSQL + '   SourceBlockSize INT,'

    SET @v_strListSQL = @v_strListSQL + '   filegroupid INT,'

    SET @v_strListSQL = @v_strListSQL + '   loggroupguid UNIQUEIDENTIFIER,'

    SET @v_strListSQL = @v_strListSQL + '   differentialbaseLSN DECIMAL(25,0),'

    SET @v_strListSQL = @v_strListSQL + '   differentialbaseGUID UNIQUEIDENTIFIER,'

    SET @v_strListSQL = @v_strListSQL + '   isreadonly BIT,'

    SET @v_strListSQL = @v_strListSQL + '   ispresent BIT'

    SELECT @v_strServerVersion = CAST(SERVERPROPERTY ('PRODUCTVERSION') AS NVARCHAR)

    IF @v_strServerVersion LIKE '10.%'         --SQL 2005

         SET @v_strListSQL = @v_strListSQL + ', TDEThumbpr DECIMAL'

     ELSE IF @v_strServerVersion LIKE '13.%'    --SQL 2016

         SET @v_strListSQL = @v_strListSQL + ', TDEThumbprint varbinary(32), SnapshotUrl nvarchar(360)'

    SET @v_strListSQL = @v_strListSQL + ')'

    EXEC (@v_strListSQL)

    INSERT INTO ##FILE_LIST EXEC ('RESTORE FILELISTONLY FROM DISK = ''' + @p_strFQNRestoreFileName + '''')

    DECLARE curFileLIst CURSOR FOR

        SELECT LogicalName ,

              Replace(PhysicalName,REVERSE(left(reverse(PhysicalName),CHARINDEX('\',reverse(PhysicalName)) -1 )),'') BackupFolderName,

              REVERSE(left(reverse(PhysicalName),CHARINDEX('\',reverse(PhysicalName)) -1 )) BackupFileName,

               [type]

        FROM ##FILE_LIST

    SET @v_strMoveSQL = ''

    OPEN curFileList

    FETCH NEXT FROM curFileList into @v_strLogical, @v_strFolder, @v_strFile, @v_strType

    WHILE @@Fetch_Status = 0

    BEGIN

         IF @v_strType = 'D'

              SET @v_strTEMP =  'MOVE N''' + @v_strLogical + ''' TO N''' + coalesce(@p_strDataFileFolder,@v_strFolder) + replace(@v_strFile, @p_strDBNameFrom, @p_strDBNameTo) + ''''

         ELSE IF @v_strType = 'L'

              SET @v_strTEMP =  'MOVE N''' + @v_strLogical + ''' TO N''' + coalesce(@p_strLogFileFolder,@v_strFolder) + replace(@v_strFile, @p_strDBNameFrom, @p_strDBNameTo) + ''''

        SET @v_strMoveSQL = @v_strMoveSQL + @v_strTEMP + ', ' + @CrLf

        FETCH NEXT FROM curFileList into @v_strLogical, @v_strFolder, @v_strFile, @v_strType

    END

    CLOSE curFileList

    DEALLOCATE curFileList

    SET @v_strExecSQL = 'RESTORE DATABASE [' + @p_strDBNameTo + ']' + @CrLf

    SET @v_strExecSQL = @v_strExecSQL + ' FROM DISK = ''' + @p_strFQNRestoreFileName + '''' + @CrLf

    SET @v_strExecSQL = @v_strExecSQL + ' WITH RECOVERY,' + @CrLf

    SET @v_strExecSQL = @v_strExecSQL + @v_strMoveSQL

    SET @v_strExecSQL = @v_strExecSQL + ' NOREWIND, '

    SET @v_strExecSQL = @v_strExecSQL + ' NOUNLOAD '

    SET @v_strExecSQL = @v_strExecSQL + @v_strREPLACE

    IF OBJECT_ID('tempdb..##FILE_LIST') IS NOT NULL

         DROP TABLE ##FILE_LIST

    

    PRINT '---------------------------'

    PRINT @v_strExecSQL + ';'

    PRINT '---------------------------'

    

    --EXEC sp_executesql @v_strExecSQL

   

GO