CODEWITHSUNDEEP
sql-serverdatabase-backups
ferc
ferc

Reputation: 558

Estimate size of differential backup alternatives

I found a method to estimate the size of a differential backup in this article. The problem is that it requires the sysadmin role. Is it a good idea, as a poor-man's solution, to get the size of the backup by making a backup to the 'nul' device and then checking the backupset view of the msdb database?

Upvotes: 0

Views: 1707

Answers (2)

Ben Thul
Ben Thul

Reputation: 32697

There is a new method that's been introduced since this question was asked (and answered). The sys.dm_db_file_space_usage system view has a modified_extent_page_count column that will tell you how many pages have been modified since the last full backup. Summing that across all of the files in the database should give you a good estimate of the size of the differential backup.

The OP was concerned about the other method needing sysadmin. This also requires elevated permissions (VIEW SERVER STATE), but doesn't need sysadmin.

Upvotes: 2

blitz_jones
blitz_jones

Reputation: 1078

If you have a very small backup, performing a differential backup up to 'nul' would probably be fine because it would be quick and should give you a reliable way to predict the size of the actual differential backup to disk. However, for a large differential it will take time to complete the 'nul' backup, and depending on how much activity the server gets and how much time you have, you might not want this.

I assume if you have a need to predict/estimate the size of the differential, then you are probably working with large databases. However, unfortunately I don't think you will find a better/different way than the method outlined in the article you linked above (https://dougzuck.com/sql-differential-backup-size-prediction) to get a real estimate of a SQL differential backup size.

Upvotes: 0

Related Questions