1. Log transport overview

SQL Server using log transport, can automatically send the transaction log backup of the main server to one or more auxiliary databases. Transaction log backup is applied to each auxiliary database separately. The optional third server instances (called "monitor servers") record the history and state of backup and restore operations and can also trigger alerts when they are unable to execute these operations as planned.

optional monitoring server records the history and status of backup and restore operations.

not much to say next. Let's have a detailed introduction. The


  • provides a disaster recovery solution,
  • supports restricted read-only access to the auxiliary database,
  • allows the user to define the delay time of the log sent by the user (if the data on the main database is accidentally changed, the longer delay will be useful)

< < P> terminology and definition of the proxy job of the

backup job

master server, which performs backup operations, records transaction logs to local servers and monitoring servers, and deletes old records. The

replicates the proxy job of the

master server, which copies the backup files from the master server to the auxiliary server, and records it on the auxiliary server and the monitoring server. The

restore job's

assistant server's proxy job, the backup file is restored to the auxiliary database, the local server and the monitoring server are recorded, and the old files are deleted.

repeatedly executes replication and restore jobs for multiple secondary servers.

two. Preparation work

the same service database two instances (can be multiple servers in the LAN)

master database sqlserver 2012 R2. Er ">

demonstration library LogShipping_Test, the master and the same library, the same table structure and data. The same SQL login name, the password

three. The configuration log transmits

 -- step 1: set the master server recovery mode to fully backup the ALTER DATABASE, step master service database for a complete backup BACKUP DATABASE LogShipping_Test TO DISK='D:LogShippingLogShipping_Test.bak'WITH NOFORMAT, INIT

-- step 3:

on the main server building shared directory C:LogShippingprimary with job access rights


--- step 4:

master server LogShipping_Test library --> property --> task --> after sending transaction logs to the master server and auxiliary database configuration success, set copy and restore every two minutes to do once every two minutes, such as

log transport configuration successful: auxiliary database identification (standby / read-only) as follows:

master database SQL Server proxy job such as the following diagram:

backup job (LSBackup_LogShipping_Test)

warning job (LSAlert_{computer name}) (LSRestore_{computer name}_LogShipping_Test)

warning job (LSAlert_{computer name}MSSQLSERVERTWO)

master server is a 2 minute backup file sharing directory such as the following

at last: see whether log transmission is correct and unmistakable

using the visualization operation in the SQL broker job to see whether the log transmission is normal Line normal

 -- (primary database query) exec master..Sp_help_log_shipping_monitor exec master..Sp_help_log_shipping_primary_database'LogShipping_Test'-- (auxiliary database query) exec master..Sp_help_log_shipping_secondary_database'LogShipping_Test'

four. Master slave database manually switch configuration

step 1: in the main database, make it in the restore

 use master Backup log [LogShipping_Test] to disk = execute the auxiliary data manually Copy and restore operations on the library (fast replication restore to the table) 

-- manually disable the backup and warning jobs on the main database (stop backup)

step 2: on the auxiliary database, use the spare parts file of the step to restore the

 use master Restore log [LogShipping_Test] from GShippingLogShipping_Test1.bak'with RECOVERY 

removes the data of the previous backup and copy of the folder where it is located (D:LogShippingprimary, D:LogShippingsecondary)

reconfiguration log transfer, on the auxiliary database (LogShipping_Test Library - gt; property --> task); transmits transaction logs. After the main server and the auxiliary database configuration successful), the auxiliary database becomes the primary database

to delete the original master database log transfer (LogShipping_Test library --> attribute --> task --> transmits transaction log, will check out).

is the full content of this article, I hope that the content of this article for everyone's learning or work has a certain reference value, if there is a doubt that people can leave a message, thank you for the support of the home of the script.

This paper fixed link:http://www.script-home.com/the-method-of-high-available-log-transfer-for-sql-server-database.html | Script Home | +Copy Link

Article reprint please specify:The method of high available log transfer for SQL Server database | Script Home

You may also be interested in these articles!