Menu
Is there a way to know the size of a database full backup file before we actually do the backup? I did some analyses and figured sp_spaceused could give a close figure but thats not all, there seem(s) to be one/more factors other than the used space that go into determining the size?
Has anyone been down this path before? Any ideas?
244
If you need to check a single database, you can quickly find the SQL Server database sizein SQL Server Management Studio (SSMS): Right-click the database and then click Reports -> Standard Reports -> Disk Usage. Alternatively, you can use stored procedures like exec sp_spaceused to get database size.
A typical backup strategy would include: One differential (partial) backup 5 days per week. One full backup per week. Keep your last 4 full weekly backups.
A full database backup backs up the whole database. This includes part of the transaction log so that the full database can be recovered after a full database backup is restored. Full database backups represent the database at the time the backup finished.
This means the compressed backup would be 19.4% of the size of the uncompressed backup. This is pretty significant, the compressed backup would be more than 5 times smaller than the uncompressed backup.
sp_spaceused should be pretty close if you look at the reserved space.
Estimating the Size of your Database Backups
Paul Randal blogged recently about how to know how large your TLOG backup might be:
How much data will the next log backup include
A combination of the two values, seems to be really close to the actual backup size in some brief testing.
158
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
