go backarticles

Articles of SQLschool.gr Team

sp_EstimateAllDBBackupSize - V 1.01

Antonios Chatzipavlis

Νέα έκδοση της sp_EstimateAllDBBackupSize που διορθώνει τα errors που αφορούσαν τα overflows και offline databases

use master
go

if not exists (select * from information_schema.routines where routine_name = 'sp_EstimateAllDBBackupSize')
    exec ('create proc dbo.sp_EstimateAllDBBackupSize as select ''stub version, to be replaced''')
go



alter proc sp_EstimateAllDBBackupSize
as
/*********************************************************************************************
sp_EstimateAllDBBackupSize (c) 2015 SQLschool.gr - Antonios Chatzipavlis

Version History
1.00    Sep 19, 2015
1.01    Sep 22, 2015 fix the overflow and offline databases errors

Feedback: info@sqlschool.gr

License: 
    sp_EstimateAllDBBackupSize is free to download and use for personal, educational, 
    and internal corporate purposes, provided that this header is preserved. 
*********************************************************************************************/
begin
    set nocount on;

    declare @dbname sysname;
    declare @query nvarchar(1000);
    declare @stm nvarchar(1000);

    set @query = 'use ?
    insert into #backupsize
    select      db_name() 
            , convert(decimal(15,2), sum(size) * 8192.0 / 1048576.0) 
            , convert(decimal(15,2), sum(fileproperty(name,''spaceused'')) * 8192.0 / 1048576.0) 
    from sys.database_files;'

    if ( object_id('tempdb..#backupsize') is not null )
    begin
        drop table #backupsize;
    end    

    create table  #backupsize
    (
        database_name sysname
    ,    total_db_size_mb decimal(15, 2)
    ,    estimated_backup_size_mb decimal(15, 2)
    );
    
    declare dblist cursor local fast_forward for 
            select name from sys.databases 
            where state=0 
            order by name;

    open dblist;

    fetch next from dblist into @dbname;
    while (@@fetch_status <> -1)
    begin
        if (@@fetch_status <> -2)
        begin
            set @stm =replace(@query,'?',QUOTENAME(@dbname));
            execute(@stm);
        end
        fetch next from dblist into @dbname;
    end

    close dblist;
    deallocate dblist;

    select * from #backupsize;
end
go

Relative Articles

Leave your comment

Login with your SQLschool.gr account if you want to comment on this article.


PASS chapter logo

The Official PASS Local Group for Greece

1434 33 595 27 39 1319
sql school greece logo
© 2010-2019 All rights reserved