Estimate Database Backup Size

The output from the system command sp_spaceused gives the reserved value of the database which indicates the total size of all the pages in the database. We can use the same value to estimate the database backup size.

Below query extracted from the sp_spaceused which gives the list of all the databases and their reserved size.

---

 

use master
 create table dbo.all_db_size   
        (dbname varchar(256),
        reserved varchar(256),
        data varchar(256),
        index_size varchar(90),
        unused varchar(90))
 EXECUTE sp_MSforeachdb  
         '   
       use [?]   
        declare   
        @reservedpages bigint,
        @usedpages bigint,@pages bigint
       select @reservedpages = sum(a.total_pages),  
        @usedpages = sum(a.used_pages),   
       @pages = sum
             (   
             CASE   
                    -- XML-Index and FT-Index internal tables are not considered "data", but is part of "index_size"  
                    When it.internal_type IN (202,204,211,212,213,214,215,216) Then 0   
                    When a.type <> 1 Then a.used_pages  
                    When p.index_id < 2 Then a.data_pages  
          
             Else 0   
             END   
             )   
   
             from sys.partitions p   
             join sys.allocation_units a   
             on p.partition_id = a.container_id   
             left join sys.internal_tables it on p.object_id = it.object_id  
   
             insert into master.dbo.all_db_size (dbname,reserved,data,index_size,unused)   
   
             select db_name() as dbname,   
             reserved_kb = ltrim(str(@reservedpages * 8192 / 1024.,15,0) ),   
             data_kb = ltrim(str(@pages * 8192 / 1024.,15,0) ),  
             index_size_kb = ltrim(str((@usedpages - @pages) * 8192 / 1024.,15,0) ),   
             unused_kb = ltrim(str((@reservedpages - @usedpages) * 8192 / 1024.,15,0) )   
       ' 
         
 select * from dbo.all_db_size