Log Shipping Monitoring and Troubleshooting

Log Shipping Monitoring and Troubleshooting 交易紀錄傳送監控與疑難排解 1.Log Shipping status View the Log Shipping Report (SQL Server Management Studio) https://msdn.microsoft.com/en-us/library/ms181149.aspx To display the Transaction Log Shipping Status report on a server instance Connect to a monitor server, primary server, or secondary server. Right-click the server instance in Object Explorer, point to Reports, and point to Standard…

0

Query SQL Server backup history and restore history records

SQL Server備份還原紀錄 1.使用以下TSQL語法查詢備份檔紀錄 SELECT bs.backup_set_id, bs.database_name, bs.backup_start_date, bs.backup_finish_date, CAST(CAST(bs.backup_size/1000000 AS INT) AS VARCHAR(14)) + ‘ ‘ + ‘MB’ AS [Size], CAST(DATEDIFF(second, bs.backup_start_date, bs.backup_finish_date) AS VARCHAR(4)) + ‘ ‘ + ‘Seconds’ [TimeTaken], CASE bs.[type] WHEN ‘D’ THEN ‘Full Backup’ WHEN ‘I’ THEN ‘Differential Backup’ WHEN ‘L’ THEN ‘TLog Backup’ WHEN ‘F’ THEN ‘File or filegroup’ WHEN…

4

Setting Up SQL Server 2008 R2 Log Shipping step by step

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…

2