如何實現(xiàn)alwayson的備份還原腳本

這篇文章主要介紹如何實現(xiàn)alwayson的備份還原腳本，文中介紹的非常詳細(xì)，具有一定的參考價值，感興趣的小伙伴們一定要看完！

1、 備份數(shù)據(jù)庫

在主副本上，將需要做AlwaysOn的數(shù)據(jù)庫做一次全備和日志備份（NOTE：禁用事務(wù)日志備份作業(yè)，如果有的話）

替換參數(shù)，執(zhí)行如下腳本生成備份語句，然后執(zhí)行：

DECLARE @DBName NVARCHAR(255)

DECLARE @SQL NVARCHAR(MAX)

DECLARE @BackupToPath NVARCHAR(500)

SET @DBName='datayesdb'                       --數(shù)據(jù)庫名稱

SET @BackupToPath='D:'                        --數(shù)據(jù)庫備份在主副本的存放路徑

SET NOCOUNT ON

PRINT '-- ============================================='

PRINT '-- AlwaysOn主副本上備份數(shù)據(jù)庫(完整備份+事務(wù)日志備份)'+CHAR(13)

SET  @SQL='USE [master]

GO

ALTER DATABASE ['+@DBName+'] SET RECOVERY FULL;

GO

BACKUP DATABASE ['+@DBName+']

TO DISK='''+@BackupToPath+'\'+@DBName+'.bak'' WITH COMPRESSION

GO

BACKUP LOG ['+@DBName+']

TO DISK='''+@BackupToPath+'\'+@DBName+'.trn'' WITH COMPRESSION

GO'+CHAR(13)

PRINT @SQL

2、 還原數(shù)據(jù)庫

將備份文件復(fù)制到輔助副本服務(wù)器，使用NORECOVERY方式還原。

替換參數(shù)，執(zhí)行如下腳本生成備份語句，然后執(zhí)行：

DECLARE @DBName NVARCHAR(255)

DECLARE @SQL NVARCHAR(MAX)

DECLARE @RestoreFromPath NVARCHAR(MAX)

DECLARE @RestoreToDataFileFolder NVARCHAR(200)

DECLARE @RestoreToLogFileFolder NVARCHAR(200)

SET @DBName='datayesdb'                       --數(shù)據(jù)庫名稱

SET @RestoreFromPath='D:\share'               --數(shù)據(jù)庫備份在輔助副本的存放路徑

SET @RestoreToDataFileFolder='D:\SQLData'     --數(shù)據(jù)庫備份的數(shù)據(jù)文件在輔助副本的還原路徑

SET @RestoreToLogFileFolder='D:\SQLLog'       --數(shù)據(jù)庫備份的日志文件在輔助副本的還原路徑

SET NOCOUNT ON

PRINT '-- ============================================='

PRINT '-- AlwayOn輔助副本還原數(shù)據(jù)庫(指定NORECOVERY方式還原)'+CHAR(13)

DECLARE @RestoreFilePath NVARCHAR(MAX)

DECLARE @LNAME NVARCHAR(500)

DECLARE @PNAME NVARCHAR(500)

DECLARE @PFName NVARCHAR(500)

DECLARE @BackupType CHAR(1)

SET @RestoreFilePath=''

SET @SQL = 'RESTORE FILELISTONLY FROM DISK = '''+@RestoreFromPath+'\'+@DBName+'.bak'+'''' 

if OBJECT_ID ('tempdb..#temp')is not null

BEGIN

 DROP TABLE #BackupFileList

END

CREATE TABLE #BackupFileList

    (

      LogicalName NVARCHAR(128) ,

      PhysicalName NVARCHAR(260) ,

      BackupType 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) NULL ,

      ReadWriteLSN NUMERIC(25,0) NULL ,

      BackupSizeInBytes BIGINT ,

      SourceBlockSize INT ,

      FileGroupID INT ,

      LogGroupGUID UNIQUEIDENTIFIER NULL ,

      DifferentialBaseLSN NUMERIC(25,0) NULL ,

      DifferentialBaseGUID UNIQUEIDENTIFIER ,

      IsReadOnly BIT ,

      IsPresent BIT ,

      TDEThumbprint NVARCHAR(100)

    )

INSERT INTO #BackupFileList EXEC (@SQL);

DECLARE CurTBName CURSOR

FOR

    SELECT LogicalName,PhysicalName,BackupType FROM #BackupFileList 

OPEN CurTBName

FETCH NEXT FROM CurTBName INTO @LNAME,@PNAME,@BackupType

WHILE @@FETCH_STATUS = 0

BEGIN 

    SELECT @PFName=RIGHT(@PNAME, CHARINDEX('\',REVERSE(@PNAME))-1)

    SET  @RestoreFilePath=' MOVE N'''+@LNAME+''' TO N'''

    +CASE WHEN @BackupType='D' THEN @RestoreToDataFileFolder ELSE @RestoreToLogFileFolder END

    +'\'+@PFName+''', '+CHAR(13)+@RestoreFilePath

    FETCH NEXT FROM CurTBName INTO  @LNAME,@PNAME,@BackupType

END

CLOSE CurTBName

DEALLOCATE CurTBName

SET  @SQL='USE [master]

GO

RESTORE DATABASE '+@DBName+' FROM DISK = N'''+@RestoreFromPath+'\'+@DBName+'.bak'' WITH FILE = 1,'+CHAR(13)

+@RestoreFilePath

+'NORECOVERY,NOUNLOAD,STATS = 10

GO

RESTORE LOG '+@DBName+' FROM DISK = N'''+@RestoreFromPath+'\'+@DBName+'.trn'' WITH NORECOVERY

GO'+CHAR(13)

PRINT @SQL

DROP TABLE #BackupFileList

以上是“如何實現(xiàn)alwayson的備份還原腳本”這篇文章的所有內(nèi)容，感謝各位的閱讀！希望分享的內(nèi)容對大家有幫助，更多相關(guān)知識，歡迎關(guān)注億速云行業(yè)資訊頻道！