Location>code7788 >text

NUL Devices/NIL Devices in SQL Server

Popularity：66 ℃/2024-09-19 11:37:46

NUL Devices/NIL Devices in SQL Server


In SQL Server, there is a special device called NUL (note that it is not NULL), which is similar to a "black hole" in the file system. the NUL device is similar to /dev/null in Linux, and all data written to the NUL will be discarded directly.

We can use this feature to test or simulate backup operations without actually generating a backup file.

 


Routine operations for full backups
Typically, we can use the following simplest SQL statement when making a full backup of a particular database:

BACKUP DATABASE AdventureWorks2012 TO DISK = N''

After execution, SQL Server displays the results of the backup

Already in place for the database 'AdventureWorks2012'，file 'AdventureWorks2012' (Located in the document 1 first (of multiple parts))Done. 24328 Page.
A database has been created for the'AdventureWorks2012'，file 'AdventureWorks2012_log' (Located in the document 1 first (of multiple parts))Done. 2 Page.
BACKUP DATABASE Successfully handled 24330 leaf，spend (time or money) 4.103 unit of angle or arc equivalent one sixtieth of a degree(46.326 MB/unit of angle or arc equivalent one sixtieth of a degree)。



The backup file will be stored in the default backup path of the instance. This path can be confirmed by the following SQL query:

DECLARE @BackupDest VARCHAR(200)
EXEC .xp_instance_regread N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer', N'BackupDirectory', @BackupDest OUTPUT;
SELECT @BackupDest;


Backup using the NUL device
If you do not want to actually generate a backup file, but need to test the backup operation or find out how long the backup will take, etc., you can write the backup data to the NUL device.
SQL Server supports two ways to write backups to the NUL device: using NUL or NUL:.

BACKUP DATABASE AdventureWorks2012 TO DISK = N'NUL' 
GO
BACKUP DATABASE AdventureWorks2012 TO DISK = N'NUL:'
GO

After execution, SQL Server displays the results of the backup as well

Already in place for the database 'AdventureWorks2012'，file 'AdventureWorks2012' (Located in the document 1 first (of multiple parts))Done. 24328 Page.
A database has been created for the'AdventureWorks2012'，file 'AdventureWorks2012_log' (Located in the document 1 first (of multiple parts))Done. 2 Page.
BACKUP DATABASE Successfully handled 24330 leaf，spend (time or money) 1.499 unit of angle or arc equivalent one sixtieth of a degree(126.803 MB/unit of angle or arc equivalent one sixtieth of a degree)。


The NUL device can also determine the read throughput of a disk by backing up, even if you back up to multiple NUL devices. Backups to NUL devices are primarily read data; write data is not written to specific disks.

BACKUP DATABASE AdventureWorks2012 TO DISK = N'NUL',DISK = N'NUL',DISK = N'NUL',DISK = N'NUL' ,DISK = N'NUL'

 

 

 
summarize
The main uses for backing up to a NUL device include:
1. Backup throughput verification: NUL devices can be used to test databaseRead IOPS Performance, which helps verify read and write throughput during backups.
2、No log backup leads to disk full: If the log file has not been backed up for a long time and becomes very large resulting in insufficient disk space, you canLog backups are written to the NUL deviceto quickly free up log space, and then either shrink the log file or set up a simple mode to shrink the file afterwards.
3. In highly available configurations: especially SQL Server 2016 or above, AlwaysOn AG scenarios, using theautomatic seeding functionBackup is required before configuring the availability database, which can be backed up to the NUL device and then initialized for synchronization.
4、COPY_ONLY backup: in do not want to interrupt the backup chain (not truncate the log file in the log) but need to test in the production environment, you can use theCOPY_ONLY Parametersto create temporary backups to avoid impacting the backup chain of the production environment.

It is important to note that read operations when backing up to a NUL device have a similar impact on performance as an actual backup, so the impact should still be carefully evaluated when testing in a production environment.



reference article
/db/452106
/lnotime/article/details/104847946
/developer/information/%E6%AD%A3%E5%9C%A8%E8%BF%98%E5%8E%9F%E6%95%B0%E6%8D%AE%E5%BA%93SQL%20Server%20-%E6%95%B0%E6%8D%AE%E4%B8%BAnull%E4%B8%8D%E8%83%BD%E5%AF%B9null%E5%80%BC%E8%B0%83%E7%94%A8%E6%AD%A4%E6%96%B9%E6%B3%95%E6%88%96%E5%B1%9E%E6%80%A7
 


 

This article is copyrighted and may not be reproduced without the author's permission.

<Previous Next>