Share via

Setting Up SQL Server 2008 R2 Log Shipping step by step

  • Article

Setting Up SQL Server 2008 R2 Log Shipping step by step

1.Prerequisites

(1)確認資料庫復原模式recovery model為[完整](Full)或[大量記錄](bulk-logged)

(2)建立一個交易紀錄備份用目錄並設定分享，如果只有兩台SQL Server通常建立在主要伺服器Primary Server上，此目錄也可以建立在第三台主機上。

此範例的目錄建立在主要伺服器上，C:\LogShip

並給予主要伺服器Primary Server的SQL Agent啟動帳戶SQLservice有寫入與讀取權限

 

(3) 建立一個放置要還原的交易紀錄檔目錄並設定分享，此目錄建立在次要伺服器secondary Server主機上。

此範例的目錄建立在次要伺服器上，C:\LogShipRestore

並給予次要伺服器secondary Server的SQL Agent啟動帳戶SQLservice有寫入與讀取權限

2.Prepare a Secondary Database for Log Shipping

(1)在主要伺服器上對要設定log shipping的資料庫執行完整備份。

(2)並執行一次交易紀錄檔備份Log Backup。(非必要Optional)

(3)複製備份檔到次要伺服器。

(4)在次要伺服器，以WITH NORECOVERY參數還原完整備份與交易紀錄檔備份。

還原後資料庫狀態為(正在還原中)(Restoring)

 

3.開始設定Log Shipping

(1)在主體伺服器設定主體資料庫屬性

(2)在選取頁面點選[交易紀錄傳送]，右邊勾選[將此啟用為記錄傳送組態的主要資料庫]

Under Select a page, click Transaction Log Shipping.

Select the Enable this as a primary database in a log shipping configuration check box.

(3)點擊[備份設定]。

Under Transaction log backups, click Backup Settings.

(4)在[備份資料夾的網路路徑]輸入之前在主要伺服器所建立的目錄分享的路徑，以下範例為\\SQL2K8R2N02\LogShip，在[如果備份資料價位於主要伺服器上，請輸入至該資料夾的本機路徑]輸入C:\LogShip

  • In the Network path to the backup folder box, type the network path to the share you created for the transaction log backup folder.
  • If the backup folder is located on the primary server, type the local path to the backup folder in the If the backup folder is located on the primary server, type a local path to the folder box. (If the backup folder is not on the primary server, you can leave this box empty.)
    Important noteImportant
    If the SQL Server service account on your primary server runs under the local system account, you must create your backup folder on the primary server and specify a local path to that folder.
  • Configure the Delete files older than and Alert if no backup occurs within parameters.
  • Note the backup schedule listed in the Schedule box under Backup job. If you want to customize the schedule for your installation, then click Schedule and adjust the SQL Server Agent schedule as needed.
  • SQL Server 2008 Enterprise supports backup compression. When creating a log shipping configuration, you can control the backup compression behavior of log backups by choosing one of the following options: Use the default server setting, Compress backup, or Do not compress backup. For more information, see Log Shipping Transaction Log Backup Settings.

(5)在次要伺服器，點擊[加入]。

Under Secondary server instances and databases, click Add.

(6)點擊[連線]，連線到次要伺服器。

Click Connect and connect to the instance of SQL Server that you want to use as your secondary server.

(7)在初始化次要資料庫頁籤，選擇一種初始化方式，再次範例我們選擇[否，次要資料庫已初始化]。

On the Initialize Secondary database tab, choose the option that you want to use to initialize the secondary database.

NoteNote
If you choose to have Management Studio initialize the secondary database from a database backup, the data and log files of the secondary database are placed in the same location as the data and log files of the master database. This location is likely to be different than the location of the data and log files of the primary database.

(8) 在複製檔案頁籤，輸入次要伺服器上建立的目錄，此範例為C:\LogShipRestore

  • On the Copy Files tab, in the Destination folder for copied files box, type the path of the folder into which the transaction logs backups should be copied. This folder is often located on the secondary server.
  • Note the copy schedule listed in the Schedule box under Copy job. If you want to customize the schedule for your installation, click Schedule and then adjust the SQL Server Agent schedule as needed. This schedule should approximate the backup schedule.

(9)在還原交易記錄頁籤，選擇復原備份時的資料庫狀態，預設值選擇[不復原模式]則資料庫狀態會維持"正在還原中"而無法存取，如果選擇[待命模式]則資料庫可以唯讀。

  • On the Restore tab, under Database state when restoring backups, choose the No recovery mode or Standby mode option.
  • If you chose the Standby mode option, choose if you want to disconnect users from the secondary database while the restore operation is underway.
  • If you want to delay the restore process on the secondary server, choose a delay time under Delay restoring backups at least.
  • Choose an alert threshold under Alert if no restore occurs within.
  • Note the restore schedule listed in the Schedule box under Restore job. If you want to customize the schedule for your installation, click Schedule and then adjust the SQL Server Agent schedule as needed. This schedule should approximate the backup schedule.

(10)勾選[使用監視伺服器執行個體]，點擊[設定]，跳出連線到哪一個SQL Server，通常選擇主要伺服器作為監視伺服器。

  • Under Monitor server instance, select the Use a monitor server instance check box, and then click Settings.
    Important noteImportant
    To monitor this log shipping configuration, you must add the monitor server now. To add the monitor server later, you would need to remove this log shipping configuration and then replace it with a new configuration that includes a monitor server.
  • Click Connect and connect to the instance of SQL Server that you want to use as your monitor server.

(11)選擇backup,copy,restore Job連線到監控伺服器的方法，此範例保留預設值即可。

  • Under Monitor connections, choose the connection method to be used by the backup, copy, and restore jobs to connect to the monitor server.
  • Under History retention, choose the length of time you want to retain a record of your log shipping history.

(12)按下確定，就會開始設定Log Shipping。

(13)設定成功。

(14)設定成功後會出現以下幾個JOB，且次要資料庫狀態若選擇[不復原模式]，資料庫狀態就會是[正在還原中]。

PS.備份檔的命名是用UTC時間，若要用備份檔名稱來判斷該檔案的備份時間，則需要加上Time Zone時數，所以台灣的話就是+8

例如: AdventureWorks2008R2_20171103024530.trn

UTC 02:45就是台灣時間10:45

 

關於Log Shopping的監控與疑難排解，可以參考這篇

https://blogs.msdn.microsoft.com/bradchen/2016/10/15/log-shipping-monitoring-and-troubleshooting/

[Reference]

Configure Log Shipping (SQL Server)
https://technet.microsoft.com/en-us/library/ms190640.aspx

Log Shipping Deployment
https://msdn.microsoft.com/en-us/library/ms188698.aspx

Comments

  • Anonymous
    October 19, 2016
    請教一下，若是要設定的DB原本有如下DB backup:每周六Full備份一次每周1~5+日差異備份一次每30分鐘交易紀錄檔備份若是這個DB要建立log shipping，則以上備份是不是就最好停掉?我的環境是SQL2008R3+SP3
    • Anonymous
      October 19, 2016
      你只需要停止交易紀錄檔備份(Log Backup)的排程，因為log Shipping就是對Primary DB排程進行交易紀錄檔備份(Log Backup)，然後複製到Secondary Server進行restore log