Need to Predict Backup Sizes?
Someone showed me a neat trick a little while ago that I thought I would pass on. The problem is I wrote down the trick but not who told it to me, so if you are reading this and you think it was you who told me please let me know and I will cite you properly. I apologize for this transgression but I thought the tip was appropriate given my recent posts concerning backup and recovery and I was searching through some old notes looking for something to blog about tonight.
Have you ever added a new database to your server and thought to yourself, hey do I have enough space on my backup drive to cover however many days you are backing up? Here is an easy way to get a general idea for an uncompressed backup size. If you are running compression, then it will obviously be less but this is an estimate.
USE <Database Name>
EXEC sp_spaceused @updateusage = ‘TRUE’
Which for a particular database on one of my servers it returned:
|<Database Name>||2449.81 MB||40.45 MB|
|2396472 KB||2386856 KB||5144 KB||4472 KB|
The actual uncompressed backup for this database was 2,317,543 KB and the reserved data from the stored procedure was 2,396,472 which is pretty close to the actual backup. Not bad, huh? I hope this helps.