[開發環境] MSSQL 資料庫備份

本文要記錄如何使用維護計畫精靈來設定備份策略，涵蓋了資料庫備份、Log 備份以及 Log 清除三個重要部分，並以 MSSQL 2019 為例，但請注意，SQL Server Express 版本並不支援此功能，若你使用的是 Express 版本，本篇內容並不適用。

新增維護計畫

首先新增維護計畫(如下圖)，並輸入維護計畫名稱。

設定執行時間

若需要定期執行排程，可點選下圖行事曆的 icon 來設定執行時間。

備份資料庫檔案

• 由左方工具箱，拖曳備份資料庫工作到右下畫布當中，如下圖紅框所示。
  
  
  
• 點擊該工作，並設定備份類型【完整】，並選擇要備份的資料庫，這邊的範例是只有指定一個資料庫【Test】。
  
  
  
• 設定備份路徑，但如果不需指定用預設的路徑也可以。
  
  
  
• 精靈協助產生的 T-SQL 檔案如下參考。

  EXECUTE master.dbo.xp_create_subdir N'D:\Backup\DB\Test'
  GO
  use [master];
  GO
  BACKUP DATABASE [Test] TO  DISK = N'D:\Backup\DB\Test\Test_backup_2024_03_04_113615_5402855.bak' WITH NOFORMAT, NOINIT, 
  	NAME = N'Test_backup_2024_03_04_113615_5402855', 
  	SKIP, REWIND, NOUNLOAD, STATS = 10
  

備份資料庫 Log 檔案

• 資料庫如果有很頻繁的寫入/修改/刪除，用久了可能會造成 Log 變大，為了避免硬碟爆掉，在下一個縮小交易紀錄檔案大小動作前，建議先備份一下 Log。
• 備份方式同備份資料庫，由左方工具箱，拖曳備份資料庫工作到右下畫布當中，如下圖紅框所示，但要記得把連接線接在上一個工作的後方。
  
  
  
• 點擊該工作，並設定備份類型【交易紀錄】，並選擇要備份的資料庫，這邊的範例是只有指定一個資料庫【Test】。
  
  
  
• 設定備份路徑，但如果不需指定用預設的路徑也可以。
  
  
  
• 精靈協助產生的 T-SQL 檔案如下參考。

  use [master];
  GO
  EXECUTE master.dbo.xp_create_subdir N'D:\Backup\DB\Test'
  GO
  BACKUP LOG [Test] TO  DISK = N'D:\Backup\DB\Test\Test_backup_2024_03_04_114504_5055621.trn' WITH NOFORMAT, NOINIT,
  	NAME = N'Test_backup_2024_03_04_114504_5055621', 
  	SKIP, REWIND, NOUNLOAD, STATS = 10
  

縮小交易紀錄檔案大小 

• 執行記錄檔壓縮，因為好像沒有找到現有的工具，所以這個部分只能先自己執行 SQL 語法，由左方工具箱，拖曳執行 T-SQL 陳述式工作到右下畫布當中，如下圖紅框所示，但要記得把連接線接在上一個工作的後方。
  
  
  
• 點擊該工作，並輸入要執行的語法。
  
  
  
• 語法說明如下。

  USE [資料庫名稱]
  GO
  
  -- 將資料庫復原模式(Recovery model)換為簡單模式
  ALTER DATABASE [資料庫名稱] SET RECOVERY SIMPLE WITH NO_WAIT
  
  -- 執行記錄檔壓縮，並將記錄檔大小壓縮為 1 MB。
  DBCC SHRINKFILE(記錄檔邏輯名稱, 1)
  
  -- 將DB復原模式更換回完整模式
  ALTER DATABASE [資料庫名稱] SET RECOVERY FULL WITH NO_WAIT
  GO
  

參考網站

SQL Server LDF 容量大爆炸 (SQL Server ldf truncate & shrink)

T-SQL筆記20_肥大的ldf檔案_查詢便超慢_壓縮SQL Server ldf

清除SQL Server Log檔 (交易紀錄)