Shulou(Shulou.com)05/31 Report--

This article mainly introduces how to achieve the backup and restore script of alwayson, which is very detailed and has a certain reference value. Friends who are interested must read it!

1. Back up the database

On the master copy, there will be a full backup and log backup of the database that needs to do AlwaysOn (NOTE: disable the transaction log backup job, if any)

Replace the parameters, execute the following script to generate the backup statement, and then execute:

DECLARE @ DBName NVARCHAR

DECLARE @ SQL NVARCHAR (MAX)

DECLARE @ BackupToPath NVARCHAR

SET @ DBName='datayesdb'-- Database name

SET @ BackupToPath='D:'-- the path where the database backup is stored in the master copy

SET NOCOUNT ON

PRINT'--='

PRINT'- backup database on AlwaysOn master copy (full backup + transaction log backup)'+ 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. Restore the database

Copy the backup files to the secondary copy server and restore them using NORECOVERY.

Replace the parameters, execute the following script to generate the backup statement, and then execute:

DECLARE @ DBName NVARCHAR

DECLARE @ SQL NVARCHAR (MAX)

DECLARE @ RestoreFromPath NVARCHAR (MAX)

DECLARE @ RestoreToDataFileFolder NVARCHAR

DECLARE @ RestoreToLogFileFolder NVARCHAR

SET @ DBName='datayesdb'-- Database name

SET @ RestoreFromPath='D:\ share'-- the path where the database backup is stored in the secondary copy

SET @ RestoreToDataFileFolder='D:\ SQLData'-- the restore path of the data file backed up by the database in the secondary copy

SET @ RestoreToLogFileFolder='D:\ SQLLog'-- the restore path of the log files backed up by the database in the secondary copy

SET NOCOUNT ON

PRINT'--='

PRINT'- AlwayOn Auxiliary copy restore Database (specify NORECOVERY restore)'+ CHAR (13)

DECLARE @ RestoreFilePath NVARCHAR (MAX)

DECLARE @ LNAME NVARCHAR

DECLARE @ PNAME NVARCHAR

DECLARE @ PFName NVARCHAR

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 (20Pol 0)

MaxSize NUMERIC (20Pol 0)

FileID BIGINT

CreateLSN NUMERIC (25Phone0)

DropLSN NUMERIC (25pi 0) NULL

UniqueID UNIQUEIDENTIFIER

ReadOnlyLSN NUMERIC (25pi 0) NULL

ReadWriteLSN NUMERIC (25pi 0) NULL

BackupSizeInBytes BIGINT

SourceBlockSize INT

FileGroupID INT

LogGroupGUID UNIQUEIDENTIFIER NULL

DifferentialBaseLSN NUMERIC (25pi 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 Nationals / LNAMEs /'TO rooms'

+ 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 = Numeric recovery recovery from FromPathology'\'+ @ DBName+'.bak'' WITH FILE = 1recoveryChar (13)

+ @ RestoreFilePath

+ 'NORECOVERY,NOUNLOAD,STATS = 10

GO

RESTORE LOG'+ @ DBName+' FROM DISK = restore FromPathology'\'+ @ DBName+'.trn'' WITH NORECOVERY

GO'+CHAR (13)

PRINT @ SQL

DROP TABLE # BackupFileList

The above is all the contents of this article "how to implement the backup and restore script of alwayson". Thank you for reading! Hope to share the content to help you, more related knowledge, welcome to follow the industry information channel!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.