Backing up to NUL(L)?

Some of you may be wondering why you would want to do this.  We had a real requirement in a performance benchmark to use FULL recovery and backup the transaction log but we did not want to retain the log changes.  We already had a gold backup which we restored after each test.

I was wondering if it was still possible to BACKUP to NULL in SQL Server 2008 R2, and the answer is yes.  Warning: don’t ever do this in a production environment.

 BACKUP LOG sandbox TO DISK = 'NUL'

 I am unsure why the NUL keyword is missing a ‘L’.

Interesting, if I try to backup the log using the NUL keyword then this fails

 BACKUP LOG sandbox TO DISK = 'NUL.bak'

 

Msg 3201, Level 16, State 1, Line 1
Cannot open backup device '…\Backup\NUL.bak'.

Operating system error 2(failed to retrieve text for this error. Reason: 15105).
Msg 3013, Level 16, State 1, Line 1
BACKUP DATABASE is terminating abnormally

 

But, this works and persists the backup file in the default backup directory (look in msdb.dbo.backupmediafamily)

 

BACKUP LOG sandbox TO DISK = 'NULL.bak'