First of all, never use this in a production environment! This script is to backup your transaction logs to a “nul device” for all online databases which are not using simple recovery model. In windows it is indeed spelled “nul” with one “L”. The only reason you would want to do this is if you have a non production environment using full recovery model and this server architecturally mirrors your production environment. For example, we have a “staging” server that is used for testing our code changes before they go into production. We require the staging environment to be as close to production as possible and have scheduled scripts that sync them weekly. In this scenario, we have many databases in the staging server that are using full recovery model but we do not want to backup the t-logs, we would rather just throw them away.
SET NOCOUNT ON
DECLARE @db AS sysname
DECLARE @SQL AS VARCHAR(250)
DECLARE @logged_DBs TABLE (DataBase_Name sysname)
INSERT INTO @logged_DBs ( DataBase_Name )
SELECT name FROM sys.databases WHERE recovery_model_desc != 'SIMPLE' and state_desc = 'ONLINE'
WHILE EXISTS (SELECT * FROM @logged_DBs)
SET @db = (SELECT TOP 1 database_name FROM @logged_DBs)
SET @SQL = 'BACKUP LOG '+@db+' TO DISK=''NUL'''
DELETE FROM @logged_DBs where DataBase_Name = @db
To learn more about NUL devices, here’s a link to the wikipedia page: https://en.wikipedia.org/wiki/Null_device