Get SQL Server database backup size in .Net

Question

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.

But you won't know the size of the backup until it's been created, assuming enough space is available. I think the only realistic solution is to warn the user when x percentage of free space is available on the drive. — OMG Ponies
– OMG Ponies, Commented Mar 20, 2012 at 4:36
Similar post: stackoverflow.com/questions/862018/calculating-db-backup-size — Cᴏʀʏ
– Cᴏʀʏ, Commented Mar 20, 2012 at 4:38
Are you backing the file up locally on the server and then copying to a new location or backing up to the target location? — tsells
– tsells, Commented Mar 20, 2012 at 4:39
@tsells: No, actually it is a forms application and is installed on the client machine. We are taking the backup directly to the specified location. — Mahesh KP
– Mahesh KP, Commented Mar 20, 2012 at 4:41
@mahesh: As Jirka said in his answer, there's just no way to get the exact backup size without doing an actual backup -- the best you can do is to take the estimate using one of the techniques in the other post, and maybe add 5 - 10% padding for safety. — Cᴏʀʏ
– Cᴏʀʏ, Commented Mar 20, 2012 at 12:25

Jirka Hanika · Accepted Answer · 2012-03-20 09:31:00Z

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.

Collectives™ on Stack Overflow

Get SQL Server database backup size in .Net

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related