CODEWITHSUNDEEP
c#.netsql-serverbackupsmo
Mahesh KP
Mahesh KP

Reputation: 6446

Get SQL Server database backup size in .Net

We want to take db backups programmatically. For this we used the SqlServer.Management.Smo namespace and its classes, and it is working perfectly.

Now we have a requirement to determine whether there is enough space is there in the location to hold the backup files before saving the db backup to the specified location.

And if there is not enough space, we want to alert the user of that fact.

One way we found is that put a try catch and catch the exception if there is no enough space in memory. But we are looking for a solution to get the size before saving it.

Upvotes: 0

Views: 1562

Answers (1)

Jirka Hanika
Jirka Hanika

Reputation: 13529

There is no way to get the exact backup size until you try backing up the database.

Approximate size can be guessed at by looking at database file sizes. This is useful if you have no idea how big it will be and you need to "publish" some estimate to the unfortunate user whose disk is full. But the real database sizes will likely be much smaller.

SELECT CAST(SUM(size) AS DECIMAL) * 8192 from sys.database_files

Given that you will have to add that try-catch clause anyway, much easier and more accurate estimates can be obtained from looking at the last backup size like this:

SELECT TOP 1 database_name, backup_size FROM msdb..backupset ORDER BY backup_finish_date DESC

You can compare either of these values to the amount of free disk space. It may be useful to add some margin to the number obtained by the latter method to account for some expected gradual database growth.

Upvotes: 1

Related Questions