Simulate a backup in SQL Server without taking up disk space

Hello everyone, today we are going to talk about a simple trick that can be useful in various situations. It is about simulating a backup in SQL Server .

We put you in a situation. Recently, we have encountered a problem working. In an instance of SQL Server, there were multiple databases, some of which were backed up. Among the databases, there was one especially large database compared to the rest. We needed:

• Know the final size of the backup, both compressed and uncompressed.
• How long will it take to complete in either way?
• The speed in MB/s that you have obtained.

All of this, in an ideal scenario in which there is space, can be simulated with a backup with copy only so as not to interfere with the current copies. The problem is that we don’t have that space, so we had to simulate a backup in SQL Server .

How to simulate a backup in SQL Server?

Once we knew what we had to do, we began to investigate. The solution was to make the backup to NUL . We emphasize the NUL (with an L), not to be confused with NULL (with two L) .

First of all, let’s see what a “usual” backup to disk would look like, without compressing:

After executing it, it returns the following information:

It tells us the time it has taken: just over a second (1 second), the speed (almost 151 MB/s) and it has occupied 207 MB uncompressed:

Backup to disk with compression

In the case of the compressed backup, the same previous query would be worth adding the “COMPRESSION” parameter.

BACKUP DATABASE AdventureWorks2019 TO DISK='C:\backup_sql\bkp_compressed.bak' WITH INIT, STATS=5, COPY_ONLY, COMPRESSION
Go

If we compare it with the previous backup (uncompressed). All values are better. It has taken less time and done it faster. The size has also been smaller (from 207 MB to 48.2 MB):

Get compression ratio by backing up to NUL

Once we have seen how “normal” backups are made to disk and the advantages of compression over them, we are going to see how to obtain the final size of the backup, obtaining the compression ratio.

To do this, first of all we are going to thank the code shared by Eli Leiba , which gave us the perfect solution. On the one hand, we could know the size of the backup without performing thanks to the compression ratio. And on the other hand, it allowed us to see how long it took, from loading to a future backup task schedule. All this without currently having enough disk space.

To make it work, the first thing to do is create the stored procedure as indicated on your page:

USE master
Go

-- ================================================== ============
-- Author: Eli Leiba
--Create date: 12-2017
-- Description: Compute the DB backup compression ratio %
-- ================================================== ============
CREATE PROCEDURE usp_Calc_DB_Compression_Ratio_Pct (
 @dbName SYSNAME,
 @compressPct DECIMAL (5, 1) OUTPUT
  )
ACE
START
 DECLARE @dynaTSQL VARCHAR(400)

 SET NOCOUNT ON
 SET @dynaTSQL = CONCAT (
 'BACKUP DATABASES',
 @dbName,
 'TO DISK = N',
 '''',
 'null',
 '''',
 ' with compression, copy_only '
  )

 EXEC (@dynaTSQL)

 SELECT @compressPct = cast (100.0*a.compressed_backup_size / a.backup_size AS DECIMAL (5, 1))
 FROM msdb..backupsetto
 WHERE lower (a.database_name) = @dbName AND a.backup_finish_date = (
 SELECT max(backup_finish_date)
 FROM msdb..backupset
  )

 SETNOCOUNTOFF
END
Go

And once created, you can already test. Following the previous example from AdventureWorks2019, in our case it would be:

USEmaster
Go

DECLARE @comppct DECIMAL (5, 1)

EXEC usp_Calc_DB_Compression_Ratio_Pct @dbname = 'AdventureWorks2019',
 @compressPct = @comppct OUTPUT

PRINT @comppct

The output would look like this:

Processed 1184 pages for database 'Northwind', file 'Northwind' on file 1.
Processed 2 pages for database 'Northwind', file 'Northwind_log' on file 1.
BACKUP DATABASE successfully processed 1186 pages in 0.312 seconds (29.674 MB/sec).
19.4  

In this code, the compression ratio is 19.4%. So if the uncompressed backup occupied exactly 100 MB. The compressed backup would occupy 19.4 MB. And all this without having the need (or the option), to be able to do it directly to disk due to lack of space.

If you prefer that we take care of the backup, or you want us to assess your environment, contact us without obligation on our contact page .

If you want to be up to date with our publications, sign up for our monthly newsletter:

We hope these tricks have been useful to you. See you in next innings.