Backup to the null device on SQL Server for Linux

Question

In SQL Server on Windows, it's a common enough thing to backup a database to the NULL device if you need to take a backup but don't actually care about that backup. This is useful for changing the recovery model in a dev environment, as an example.

The backup statement is something like:

BACKUP DATABASE [xyz] TO DISK = 'NUL:';

In SQL Server for Linux, one might assume /dev/null would be the logical equivalent, however if you attempt that:

Processed 125224 pages for database 'xyz', file 'xyz01' on file 1.
Processed 2 pages for database 'xyz', file 'xyzLog' on file 1.
Msg 3634, Level 16, State 2, Line 16
The operating system returned the error '31(A device attached to the system is not functioning.)' while attempting 'DiskChangeFileSize' on '/dev/null'.
Msg 3013, Level 16, State 1, Line 16
BACKUP DATABASE is terminating abnormally.

The above command does appear to reset the log_reuse_wait and log_reuse_wait_desc columns in sys.databases, so perhaps /dev/null really does work.

I'm using SQL Server on Linux v14.0.900.75 on RedHat Enterprise Linux 7.2.

Answer 1

The "typical" SQL Server syntax does actually work in SQL Server for Linux; so try:

BACKUP DATABASE [xyz] TO DISK = 'NUL';