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'