Database Backup Growth Report Script

Following Script will return the real-time database backup growth report for Week or Month or Year. Script was developed and tested in SQL 10.0, however, with small changes it can be used in SQL 2000 or SQL 2005.
CREATE proc usp_RPT_Backup_Growth (@dbname varchar(100), @type varchar(100), @servername varchar(100), @year varchar(4), @freq varchar(10))
as
if (@freq='Week')
begin
    with bkupreport(database_name, Type, backup_size, server_name, unit_num, year_num) as (
    select a.database_name,
    case a.type when 'D' then 'Full'
    when 'I' then 'Diff'
    when 'L' then 'Log' end as Type
    ,avg(a.compressed_backup_size) Total_Backup_Size
    ,a.server_name
    ,DATEPART(wk,a.backup_start_date) 'WeekNumber', DATEPART(year,a.backup_start_date) 'YearNumber'
    from msdb.dbo.backupset a with (nOLOCK)
    where /*a.backup_start_date between (GETDATE()-32) and GETDATE()
    and */a.database_name = @dbname and a.type =@type and a.server_name = @servername
    and DATEPART(year,a.backup_start_date) = @year
    group by a.database_name, a.type, a.server_name, DATEPART(wk,a.backup_start_date)
    , DATEPART(year,a.backup_start_date)
)
select a.database_name, a.Type, a.backup_size, a.server_name, a.unit_num, a.year_num
,Cast((100. * (a.backup_size - b.backup_size)) / b.backup_size As numeric(18,2)) As 'Growth' from bkupreport a
left outer join bkupreport b with (NOLOCK)
        on b.unit_num+1 = a.unit_num
order by a.unit_num
end
if (@freq='Month')
    begin
    with bkupreport(database_name, Type, backup_size, server_name, unit_num, year_num) as (
    select a.database_name,
    case a.type when 'D' then 'Full'
    when 'I' then 'Diff'
    when 'L' then 'Log' end as Type
    ,avg(a.compressed_backup_size) Total_Backup_Size
    ,a.server_name
    ,DATEPART(MM,a.backup_start_date) 'MonthNumber', DATEPART(year,a.backup_start_date) 'YearNumber'
    from msdb.dbo.backupset a with (nOLOCK)
    where /*a.backup_start_date between (GETDATE()-32) and GETDATE()
    and */a.database_name = @dbname and a.type =@type and a.server_name = @servername
    and DATEPART(year,a.backup_start_date) = @year
    group by a.database_name, a.type, a.server_name, DATEPART(MM,a.backup_start_date)
    , DATEPART(year,a.backup_start_date)
)
select a.database_name, a.Type, a.backup_size, a.server_name, a.unit_num, a.year_num
,Cast((100. * (a.backup_size - b.backup_size)) / b.backup_size As numeric(18,2)) As 'Growth' from bkupreport a
left outer join bkupreport b with (NOLOCK)
        on b.unit_num+1 = a.unit_num
order by a.unit_num
end
if (@freq='Year')
    begin
    with bkupreport(database_name, Type, backup_size, server_name, unit_num, year_num) as (
    select a.database_name,
    case a.type when 'D' then 'Full'
    when 'I' then 'Diff'
    when 'L' then 'Log' end as Type
    ,avg(a.compressed_backup_size) Total_Backup_Size
    ,a.server_name
    ,0 'MonthNumber', DATEPART(year,a.backup_start_date) 'YearNumber'
    from msdb.dbo.backupset a with (nOLOCK)
    where a.database_name = @dbname and a.type =@type and a.server_name = @servername
    group by a.database_name, a.type, a.server_name, DATEPART(year,a.backup_start_date)
    , DATEPART(year,a.backup_start_date)
)
select a.database_name, a.Type, a.backup_size, a.server_name, a.unit_num, a.year_num
,Cast((100. * (a.backup_size - b.backup_size)) / b.backup_size As numeric(18,2)) As 'Growth' from bkupreport a
left outer join bkupreport b with (NOLOCK)
        on b.year_num+1 = a.year_num
order by a.unit_num
end
GO

SQL Server 2008 SP1 CU#8

Microsoft released CU#8 for SQL Server 2008 SP1. Use the following direct URL for more information.

http://blogs.msdn.com/b/sqlreleaseservices/archive/2010/05/17/cumulative-update-8-for-sql-server-2008-service-pack-1.aspx

Changing ‘sa’ password in MS SQL Server, if 'sa' password is misplaced or forgotten.

Sometimes there might be a situation when DBA may forget ‘sa’ password and there is no other way to login to SQL Server, in that situation DBA may reset the ‘sa’ password or add another user with SA rights.
To do this, first Stop SQL Server and then start SQL Server with –m option in startup parameter to start SQL in single user mode.

Then login to Windows with Domain Administrator and use sqlcmd utility to connect to sql server. Once connected,  write t-sql commands to reset ‘sa’ password or add another domain user with SA rights. After completion, stop SQL Server, remove –m option from startup parameters and restart sql server.

To reset sa password, use the following script.

C:\Users\Administrator>sqlcmd -E -Ssql2k8
1> alter login sa with password = 'password'
2> go
1>

This procedure has been tested with SQL Server 2008, where server was already added to domain.