德瑞克：SQL Server 學習筆記: [SQL Server]：謹慎使用 BACKUP DATABASE TO DISK = 'NUL'

2017-06-03

[SQL Server]：謹慎使用 BACKUP DATABASE TO DISK = 'NUL'

謹慎使用 BACKUP DATABASE TO DISK = 'NUL'

可調整為 BACKUP DATABASE TO DISK = 'NUL' WITH COPY_ONLY

警告：謹慎使用於 Production 生產環境

因為沒有實際備份檔案的產生，將無法用於還原、災難還原、HA等上
BACKUP DATABASE TO DISK = 'NUL' 對資料庫有執行備份作業，且不產生實體備份檔案
若因故寫成 TO DISK = 'NULL' ，這是會產生實體的備份檔案

NUL是虛擬的空設備，寫入到NUL設備的資料將被丟棄。這是在 DOS 作業系統(Disk Operating System)時就已經有的指令，在 Windows 作業系統同樣可以使用。

用於測試環境、測試目的：

測試 SQL Server 主機的最大備份吞吐量
測試練習用
例如，測試需求：對資料庫完整備份、交易紀錄備份，但永不使用此備份檔案

可以使用以下方式：

/*
-- 實際範例：備份到空裝置(NUL)
BACKUP DATABASE [AdventureWorks2014]
TO DISK = 'NUL' WITH COPY_ONLY

BACKUP LOG [AdventureWorks2014]
TO DISK = 'NUL' WITH COPY_ONLY
GO
*/

測試此 SQL Server 主機的最大備份吞吐量
實際備份的速度是：199.386 MB/sec，但最大備份吞吐量是 492.268 MB/sec。

-- 備份到空裝置(NUL)，SSD儲存設備
BACKUP DATABASE 已於 0.386 秒內成功處理了 24322 頁 (492.268 MB/sec)。

-- 實體備份，SSD儲存設備
BACKUP DATABASE 已於 0.953 秒內成功處理了 24322 頁 (199.386 MB/sec)。

示範環境：
SQL Server 2016 (SP1-GDR) (KB3210089)
Windows Server 2016 Datacenter 6.3 (Build 14393: )

範例程式碼

-- 備份到空裝置(NUL)
BACKUP DATABASE [資料庫名稱]
TO DISK = 'NUL' WITH COPY_ONLY
GO

BACKUP LOG [資料庫名稱]
TO DISK = 'NUL' WITH COPY_ONLY
GO

-- 實際範例：備份到空裝置(NUL)
BACKUP DATABASE [AdventureWorks2014]
TO DISK = 'NUL' WITH COPY_ONLY
GO

/*
已處理資料庫 'AdventureWorks2014' 的 24320 頁，檔案 1 上的檔案 'AdventureWorks2014_Data'。
已處理資料庫 'AdventureWorks2014' 的 2 頁，檔案 1 上的檔案 'AdventureWorks2014_Log'。
BACKUP DATABASE 已於 0.386 秒內成功處理了 24322 頁 (492.268 MB/sec)。
*/

-- 實體備份到NULL檔名，存放於預設路徑內
BACKUP DATABASE [資料庫名稱]
TO DISK = 'NULL' WITH COPY_ONLY
GO

-- 實際範例：實體備份到NULL檔名，存放於預設路徑內
BACKUP DATABASE [AdventureWorks2014]
TO DISK = 'NULL' WITH COPY_ONLY

/*
已處理資料庫 'AdventureWorks2014' 的 24320 頁，檔案 2 上的檔案 'AdventureWorks2014_Data'。
已處理資料庫 'AdventureWorks2014' 的 2 頁，檔案 2 上的檔案 'AdventureWorks2014_Log'。
BACKUP DATABASE 已於 0.953 秒內成功處理了 24322 頁 (199.386 MB/sec)。
*/

-- 01_空設備_BACKUP_NUL

-- 02_實體備份_BACKUP_NULL

-- 03_NULL檔名，實際備份檔案

檢視 SQL Server 記錄檔

訊息
Database backed up. Database: AdventureWorks2014, creation date(time): 2017/03/07(07:14:40), pages dumped: 24330, first LSN: 48:816:63, last LSN: 48:864:1, number of dump devices: 1, device information: (FILE=1, TYPE=DISK: {'NUL'}). This is an informational message only. No user action is required.

訊息
BACKUP DATABASE successfully processed 24323 pages in 0.382 seconds (497.443 MB/sec).

-- 實體備份到NULL檔名，存放於預設路徑內

訊息
Database backed up. Database: AdventureWorks2014, creation date(time): 2017/03/07(07:14:40), pages dumped: 24331, first LSN: 49:24:37, last LSN: 49:56:1, number of dump devices: 1, device information: (FILE=1, TYPE=DISK: {'C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\Backup\NULL'}). This is an informational message only. No user action is required.

訊息
BACKUP DATABASE successfully processed 24322 pages in 0.908 seconds (209.268 MB/sec).

-- 11_NUL空設備_檢視SQLServer記錄檔

-- 12_NUL空設備_檢視SQLServer記錄檔

-- 13_實體備份_檢視SQLServer紀錄檔

-- 14_實體備份_檢視SQLServer紀錄檔

查詢備份媒體集的相關資訊

/*
-- 查詢備份媒體集的相關資訊
SELECT F.logical_device_name N'邏輯裝置名稱', F.physical_device_name N'備份裝置的實體名稱',
 M.media_set_id N'媒體集的唯一識別碼', M.media_family_count N'媒體集中的媒體家族數目',
 M.mirror_count N'媒體集中的鏡像數目'
FROM msdb.dbo.backupmediaset M INNER JOIN msdb.dbo.backupmediafamily F
 ON M.media_set_id = F.media_set_id
GO
*/

-- 15_備份媒體集的資訊