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'

Comments

• Anonymous
  May 01, 2014
  Hi Benjamin, I know that this posting is just under 4 years old, but I thought it was worth me commenting for anyone else visiting. The post doesn't really make sense and I don't really know what you were trying to achieve (or think) you have proved? The backup to NUL is sending the backup to the NUL device, meaning the input is ignored. Therefore your command to backup to NUL.bak obviously would fail. You are telling SQL to backup to the NUL device and then trying to append those extra characters to the name (doesnt quite make sense does it?). Then the command to backup the log to NULL.bak is simply creating a log backup to a file called NULL.bak -this is all this is! NULL does not equal NUL device. Period.
• Anonymous
  May 01, 2014
  The comment has been removed