2017年7月4日 星期二

[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






0 意見:

張貼留言