Category Archives: Reports

Reporting… like how old backups are, baselines, anything that gathers info about a Server

Find If Mix Backup Types are Present

Backups, Reports, ,

There are mainly two types of backups:

  • Native SQL backups (either stored to local disk or a network location)
  • API backups, where software such as TSM/TDP or NetBackup, or CommVault, SQLLite, etc

There could be problems recovering a database if a server has different types of backups configured, unbeknown to the DBA. Per example, if TDP is used, and another DBA takes a local disk backup (out of band backup), it could pose a problem when we are restoring Tlog backups. This script identifies this situation, and lists those databases where different types of backups have been performed. If none are found, the result is blank

  


/*
DESCRIPTION
Queries the location of the last X days of databases backups
and reviews its location: API, DiskBackup or NetworkLocation

If backup location has changed in the last X days,
it returns those databases
If no change in location n the last X days,
it returns empty.

CONFIGURATION Set @days for the number of days to search for backups
Set @dbname for the specific database; leave NULL for all
 
Compatibility list:
MSSQL2012 MSSQL2005 MSSQL2008 MSSQL2000  
*/

 
DECLARE @days INT
DECLARE @dbname sysname
 
SET @days = 7			-- Number of days. Limited by the history stored on MSDB
SET @dbname = NULL		-- Specify database like 'database1' . If Null it will do all
 
 
 
 ---- End of configuration
 
declare @BackupLocation table
(i int identity(1,1) ,db varchar(1000) , bk_type varchar(10), bk_Loc varchar(3))

insert into @BackupLocation 

SELECT
 bs.database_name
, CASE bs.type
WHEN 'D' THEN 'Full'
WHEN 'I' THEN 'Diff'
WHEN 'L' THEN 'TLog'
END AS backup_type
, left(bmf.physical_device_name,3)
 
FROM msdb.dbo.backupmediafamily bmf
INNER JOIN msdb.dbo.backupset bs ON bmf.media_set_id = bs.media_set_id
WHERE 1=1
and bmf.family_sequence_number = '1'
and (CONVERT(datetime, bs.backup_start_date, 102) >= GETDATE() - @days)
--and bs.type = 'D'
and bs.database_name like coalesce(@dbname,'%')


ORDER BY
bs.database_name

--------------------------
declare @summary table

(i int identity(1,1) ,db varchar(1000), db2 varchar(1000) )

insert into @summary

select db, db+'_'+bk_type
 from @BackupLocation 

group by bk_loc, db, bk_type
order by db



 select 
 case
 when count(db2) > 1 then 'Mix Backups Found'
 else 'Only one Type of Backups'
 end as 'backups'
 , db2 from @Summary 
 
 group by db2
 having count(db2)>1 


select db,  bk_type,
case substring(bk_loc,2,1)
WHEN ':' THEN 'LOCAL DISK'
WHEN '\' THEN 'NETWORK LOCATION'  --'
ELSE 'API BACKUPS'
end as 'Type of Backups'

 from @BackupLocation 

where db in
(select db from @Summary 
 group by db2, db
 having count(db2)>1 
 )
 
group by bk_loc, db, bk_type
order by db

Thank you Andrey for the idea to create this script

Location of Data files and Tlog

InTheFileSystem, Performance-Scripts, Reports

. 


/*

-- SQL Server 2008 and R2 Diagnostic Information Queries
-- Glenn Berry
-- June 2012
-- Last Modified: June 19, 2012
-- http://sqlserverperformance.wordpress.com/
-- http://sqlskills.com/blogs/glenn/
-- Twitter: GlennAlanBerry


DESCRIPTION:  List location of all files for all databases

Look for tempDB and  Tlog sharing the same drives as a source of I/O contention issues


 
Compatibility list:
MSSQL2005
MSSQL2008
MSSQL2008R2
MSSQL2012

DOES NOT WORK
MSSQL2000

Update Log:

*/

 

 

SELECT

DB_NAME([database_id])AS [Database Name]

,   [file_id]

, name

, physical_name

, type_desc

, state_desc

,  CONVERT( bigint, size/128.0) AS [Total Size in MB]
FROM sys.master_files WITH (NOLOCK)
WHERE [database_id] > 4
AND [database_id] <> 32767
OR [database_id] = 2
ORDER BY DB_NAME([database_id]) OPTION (RECOMPILE);

.

Space usage using Mount Points

InsideDatabases, InTheFileSystem, Reports

What is this?

For reference, check

http://www.mssqltips.com/sqlservertip/2623/configuring-volume-mountpoints-on-a-sql-server-2008-failover-cluster-running-on-windows-server-2008/

A LUN can be mounted as a folder, however, traditional tools are unable to see the space utilized inside the mounted point.

 

To do that, we need to use WMI queries.

Step 1

Save this VBS file on the file system

  

'Author : Vidhya Sagar
'Modified by: Miguel Quintana
'Date : 01st Feb 2009
'Written for sql-articles.com
'Version 2

On Error Resume Next

Set objWMIService = GetObject("winmgmts:" _
    & "{impersonationLevel=impersonate}!\\.\root\cimv2")

' The WMI class will only exist when there are volumes presentm mounted to a drive or a folder
' therefore, the "on error resume next" becomes mandatory.
	
	Set colDisks = objWMIService.ExecQuery _
    ("SELECT * FROM Win32_Volume WHERE DriveLetter IS NULL")

' This section deals with volumes mounted to a NTFS folder.
If colDisks.count > 0 Then
    strComputer = "."
    Set objWMIService = GetObject("winmgmts:" _
          & "{impersonationLevel=impersonate}!\\" & strComputer & "\root\cimv2")
 
    Set colDisks = objWMIService.ExecQuery _
        ("Select * from Win32_LogicalDisk Where DriveType = 3")
    For Each objDisk in colDisks
    Wscript.Echo mid((objDisk.size)/1048576,1,8) & "*" & mid((objDisk.Freespace)/1048576,1,8) & "#" & "Logical Disk" & "|" & objDisk.DeviceID
    Next
    Set objWMIService1 = GetObject("winmgmts:" _
        & "{impersonationLevel=impersonate}!\\" & strComputer & "\root\cimv2")
 
    Set colDisks1 = objWMIService1.ExecQuery _
        ("SELECT * FROM Win32_Volume WHERE DriveLetter IS NULL")
 
    For Each objDisk1 in colDisks1
    Wscript.Echo mid((objDisk1.Capacity)/1048576,1,8) & "*" & mid((objDisk1.Freespace)/1048576,1,8) & "#" & "MountedDrive" & "|" & mid(objDisk1.Name,1,100)
    Next
Else

' This section deals with drives that are not mounted(such as C:\) and with
' mounted volumes to a drive, not a folder
    strComputer = "."
    Set objWMIService = GetObject("winmgmts:" _
          & "{impersonationLevel=impersonate}!\\" & strComputer & "\root\cimv2")
 
    Set colDisks = objWMIService.ExecQuery _
        ("Select * from Win32_LogicalDisk Where DriveType = 3")
    For Each objDisk in colDisks
    Wscript.Echo mid((objDisk.size)/1048576,1,8) & "*" & mid((objDisk.Freespace)/1048576,1,8) & "#" & "Logical_Disk" & "|" & objDisk.DeviceID
    Next
End if

Step 2, run the following TSQL script:

  

/*
DESCRIPTION    List space available in each drive. Compatible with mounted folders.
Must use with Win.vbs file
 
CONFIGURATION    See config section to specify the location of the win.vbs file
        SET @VBSPATH='cscript C:\temp\win.vbs'

 
Compatibility list:
MSSQL2005
MSSQL2008
MSSQL2000

ISSUES:

 
*/


 
    
SET NOCOUNT ON


-- Check to see if 'Show Advanced Option' is enabled
-- Check to see if 'Ole Automation options' is enabled

 
declare @chkOLE as sql_variant
declare @adv_opt as sql_variant
declare @op_ch1 as int
declare @op_ch2 as int
-- these variables keep track if we changed this options, we'll change them back before finishing
 
set @op_ch1=0
set @op_ch2=0
 
select @adv_opt = value from sys.configurations where name = 'show advanced options'
if @adv_opt = 0
begin
 EXEC sp_configure 'show advanced options',1
 RECONFIGURE WITH OVERRIDE;
 set @op_ch1 = 1
end
 
select @chkOLE = value from sys.configurations where name = 'Ole Automation Procedures'
if @chkOLE = 0
begin
 EXEC sp_configure 'xp_cmdshell', 1
 RECONFIGURE WITH OVERRIDE;
 set @op_ch2 = 1
end
 
 


--  Starting the script


-----******************** CONFIGURATION *****************
DECLARE @VBSPATH VARCHAR(200)
SET @VBSPATH='cscript C:\temp\win.vbs' -- Change the path here

-----******************** ENDS CONFIGURATION *****************
 
SET NOCOUNT ON
IF EXISTS (SELECT 1 FROM tempdb..sysobjects WHERE NAME ='##tmp')
DROP TABLE ##tmp
 
CREATE TABLE ##tmp(diskspace VARCHAR(200))
INSERT ##tmp
EXEC master.dbo.xp_cmdshell @VBSPATH
 
SET ROWCOUNT 3
DELETE ##tmp
 
SET ROWCOUNT 0
 
IF EXISTS (SELECT 1 FROM tempdb..sysobjects WHERE NAME ='##tmp2')
DROP TABLE ##tmp2
 
CREATE TABLE ##tmp2(DriveName VARCHAR(100),DriveDescription VARCHAR(15),TotalDiskSpace_in_MB VARCHAR(10), Freespace_in_MB VARCHAR(10))
INSERT ##tmp2
 
 
SELECT RTRIM(SUBSTRING(diskspace,charindex('|',diskspace)+1,100)),
 SUBSTRING(diskspace,charindex('#',diskspace)+1,charindex('|',diskspace)-charindex('#',diskspace)-1)
, SUBSTRING(diskspace,1,charindex('*',diskspace)-1) ,
SUBSTRING(diskspace,charindex('*',diskspace)+1,(charindex('#',diskspace)-charindex('*',diskspace))-1)
 
FROM ##tmp WHERE diskspace IS NOT NULL
SELECT * FROM ##tmp2
 
--select * from ##tmp
 
---  End of the routine
 
-- Clean up. See if we need to change the settings back.
 
if @op_ch1 = 1
begin
 EXEC sp_configure 'xp_cmdshell', 0
 RECONFIGURE WITH OVERRIDE;
end
 
if @op_ch2 = 1
begin
 EXEC sp_configure 'show advanced options',0
 RECONFIGURE WITH OVERRIDE;
 
end

Data file sizes and free space

InsideDatabases, InTheFileSystem, Reports

This script builds up on http://www.cookingsql.com/2014/12/free-space-on-drives/ and correlate the data with the location of data files and their sized.

  

/*
DESCRIPTION    List space on database files for each database and its location
 
CONFIGURATION    none

 
Compatibility list:
MSSQL2005
MSSQL2008
MSSQL2012

 
Issues: Some time it has issues when the database name is too long, such as in some "Sharepoint" servers

 
*/
declare @t table 
([database_name] sysname, LogicalDBName sysname, type_desc nvarchar(10), sizeMB bigint, FreeSpaceMB bigint,
freespace nvarchar(4), Autogrow nvarchar(100), MAxSize bigint, physname nvarchar(max))

declare @cmd nvarchar (2000)
set @cmd =
'use [?]
SELECT 
''?'',
b.name,
type_desc,
CAST(b.size/128.0 AS int) ''SizeMB'', 
CAST(b.size/128.0 - CAST(FILEPROPERTY(b.name, ''SpaceUsed'' ) AS int)/128.0 AS int) ''FreeSpaceMB'', 
CAST(ceiling(100 * (b.size/128.0 -CAST(FILEPROPERTY(b.name,''SpaceUsed'' ) AS int)/128.0)/(b.size/128.0)) AS varchar(8)) + ''%''  ''FreeSpace'',
    CASE 
        WHEN b.is_percent_growth = 0 
            THEN LTRIM(STR(b.growth * 8.0 / 1024,10,1)) + '' MB, '' 
        ELSE
            ''By '' + CAST(b.growth AS VARCHAR) + '' percent, ''
    END + 
    CASE 
        WHEN b.max_size = -1 THEN ''unrestricted growth''
        ELSE ''restricted growth to '' + 
            LTRIM(STR(b.max_size * 8.0 / 1024,10,1)) + '' MB'' 
    END AS Autogrow,
cast(b.max_size/128.0 as int) ''MaxSize'',
physical_name

FROM [?].sys.database_files b'
insert into @t
Exec sp_MSForEachDB @cmd

select * from @t

Data Allocation per drive

InsideDatabases, InTheFileSystem, Reports

This script joins two scripts

We first get the free space o drives, and correlate the data (inner join) with the location of data files 

 /*
DESCRIPTION    List space on database files for each database and its location
 
CONFIGURATION    none
 
Compatibility list:
MSSQL2008R2 and higher

Issues: Some time it has issues when the database name is too long, such as in some "Sharepoint" servers
 
*/

 /*
DESCRIPTION    List space on database files for each database and its location
 
CONFIGURATION    none
 
Compatibility list:
MSSQL2008R2 and higher



Issues: Some time it has issues when the database name is too long, such as in some "Sharepoint" servers
 
*/

/* This section checks if xp_cmdshell is enabled, and keeps track of the settings */ 
declare @chkOLE as sql_variant
declare @adv_opt as sql_variant
declare @op_ch1 as int
declare @op_ch2 as int
-- these variables keep track if we changed this options, we will change them back before finishing
 
set @op_ch1=0
set @op_ch2=0
 
select @adv_opt = value from sys.configurations where name = 'show advanced options'
if @adv_opt = 0
begin
 EXEC sp_configure 'show advanced options',1
 RECONFIGURE WITH OVERRIDE
 set @op_ch1 = 1
end
 
select @chkOLE = value from sys.configurations where name = 'Ole Automation Procedures'
if @chkOLE = 0
begin
 EXEC sp_configure 'Ole Automation Procedures',1
 RECONFIGURE WITH OVERRIDE
 set @op_ch2 = 1
end
 
/*
This section queries space avail on each drive.
*/
-- ==============
 
DECLARE @cmd2 NVARCHAR(2000)
DECLARE @hr int
DECLARE @fso int
DECLARE @drive char(1)
DECLARE @odrive int
DECLARE @TotalSize varchar(20)
DECLARE @MB bigint ; SET @MB = 1048576
 
DECLARE @HOSTNAME varchar(20)
 
-- checks if this is a cluster
select top 1  @HOSTNAME=nodename from ::fn_virtualservernodes()
IF @HOSTNAME is NULL set @hostname = cast(ServerProperty('machinename') as nvarchar(10))
 
declare @temp table 
(servername nvarchar(100), database_name nvarchar(100),driveletter nvarchar(2),totaldriveMB int,
 freedriveMB int,FreeDiskPct nvarchar(3), filename nvarchar(100), filesizeMB nvarchar(10), freeSpaceMB int,
FreeSpacePct varchar(8),AutoGrowth nvarchar(100), MaxsizeMB int, phys nvarchar(100))
 
declare @drives TABLE
(ServerName varchar(15),  drive char(1) PRIMARY KEY,
 FreeSpace int NULL,  TotalSize int NULL)  
 
INSERT @drives(drive,FreeSpace)
EXEC master.dbo.xp_fixeddrives
EXEC @hr=sp_OACreate 'Scripting.FileSystemObject',@fso OUT
 
IF @hr <> 0 EXEC sp_OAGetErrorInfo @fso
 
DECLARE dcur CURSOR LOCAL FAST_FORWARD
 
FOR SELECT drive from @drives
ORDER by drive
OPEN dcur
 
FETCH NEXT FROM dcur INTO @drive
WHILE @@FETCH_STATUS=0
 
BEGIN
EXEC @hr = sp_OAMethod @fso,'GetDrive', @odrive OUT, @drive
IF @hr <> 0 EXEC sp_OAGetErrorInfo @fso
EXEC @hr = sp_OAGetProperty @odrive,'TotalSize', @TotalSize OUT
IF @hr <> 0 EXEC sp_OAGetErrorInfo @odrive
UPDATE @drives
SET TotalSize=@TotalSize/@MB, ServerName = @HOSTNAME
WHERE drive=@drive
 
FETCH NEXT FROM dcur INTO @drive
END
CLOSE dcur
DEALLOCATE dcur
 
EXEC @hr=sp_OADestroy @fso
 
IF @hr <> 0 EXEC sp_OAGetErrorInfo @fso
 
 
 /* This section turns off the xp_cmdshell settings if needed  */
 
if @op_ch2 = 1
begin
 EXEC sp_configure 'Ole Automation Procedures', 0
 RECONFIGURE WITH OVERRIDE;
end
 
if @op_ch1 = 1
begin
 EXEC sp_configure 'show advanced options',0
 RECONFIGURE WITH OVERRIDE;
 
end



/* This section queries every database for space stats.*/  

declare @t table 
([database_name] sysname, LogicalDBName sysname, sizeMB bigint, FreeSpaceMB bigint,
freespace nvarchar(4), Autogrow nvarchar(100), MAxSize bigint, physname nvarchar(max))

declare @cmd nvarchar (2000)
set @cmd =
'use [?]
SELECT 
''?'',
b.name,
CAST(b.size/128.0 AS int) ''SizeMB'', 
CAST(b.size/128.0 - CAST(FILEPROPERTY(b.name, ''SpaceUsed'' ) AS int)/128.0 AS int) ''FreeSpaceMB'', 
CAST(ceiling(100 * (b.size/128.0 -CAST(FILEPROPERTY(b.name,''SpaceUsed'' ) AS int)/128.0)/(b.size/128.0)) AS varchar(8)) + ''%''  ''FreeSpace'',
    CASE 
        WHEN b.is_percent_growth = 0 
            THEN LTRIM(STR(b.growth * 8.0 / 1024,10,1)) + '' MB, '' 
        ELSE
            ''By '' + CAST(b.growth AS VARCHAR) + '' percent, ''
    END + 
    CASE 
        WHEN b.max_size = -1 THEN ''unrestricted growth''
        ELSE ''restricted growth to '' + 
            LTRIM(STR(b.max_size * 8.0 / 1024,10,1)) + '' MB'' 
    END AS Autogrow,
cast(b.max_size/128.0 as int) ''MaxSize'',
physical_name

FROM [?].sys.database_files b'
insert into @t
Exec sp_MSForEachDB @cmd

-----------------------------------


select * 
from @t t
inner join @drives d on left(t.physname,1)=d.drive

Backup Report – All Servers

Backups, Reports

This article describes how to run a single TSQL script against a list of SQL Servers.

It can be re purpose to run any number of script against a set of SQL servers.

 

  1.  Create a list of servers, place them on a file, and name it server_list.txt

The servers should be listed one per line.  You can use any string used to connect to SQL server, and enter port number if needed.

The only prerequisite is that the account running SQL Agent must have sysadmin access to all servers on the list.

Backup_solution

 

  1. Create a Batch file, name it run_sql.cmd 

The content of the Batch file should look like this:

Batch File: 

REM: This batch file runs a SQL scripts against the SQL server 
setlocal 
C:
cd "C:\Program Files\Microsoft SQL Server\bk_report"
REM The echo is to set the headers of the resulting CSV file
echo server_name,SQLVersion,ServicePack,Database,RecoveryMode,FullBackupAge,LogBackupAge,DiffBackupAge, > "C:\Program Files\Microsoft SQL Server\bk_report\log\bk_report.csv"

for /F %%a in (server_list.txt) do (
sqlcmd -S%%a -dmaster -E -m 1 -h-1 -idetail_report.sql -s"," -W -w700 >> "C:\Program Files\Microsoft SQL Server\bk_report\log\bk_report.csv"
)
 
end

 

 

  1. Place a script with your backup report, name it detail_report.sql.

  You can use this script:  http://www.cookingsql.com/2014/10/backup-report/

HINT HINT This is the place where you can put pretty much any script and it will run against all your servers. Not only backup scripts, but also anything that you want to collect information from. Think about it like a poor's man Central Management Server.

  1. Configure a SQL Job to run the CMD file.

job

 

  1. Add a second step to email the report.

email

 

The code for the second step is: 

     
    USE msdb
    EXEC sp_send_dbmail
    @profile_name='DBA', -- Please note, this Profile Must exist
    @recipients='DBA_GROUP_Email@us.company.com',
    @subject='Backup Report',
    @body='Please review the attachments. This is an automatic email generated from {server_name}',
    @file_attachments='C:\Program Files\Microsoft SQL Server\bk_report\log\bk_report.csv'

  1. Schedule the job to run daily.

 

Backup Report

Backups, Reports

The most useful script.

There is a newer version below, that displays results in a better way

Plug this in a SQL job and send the results via email and you have a flexible reporting tool: click here for instructions

  

/*
DESCRIPTION    List the age of the last backup for all databases  for FULL, DIFF and Tlog

Plus databases with no backups ever


CONFIGURATION   none

 
Compatibility list:
MSSQL2005
MSSQL2008

 */



set nocount on
use msdb
 
Select Distinct
    serverproperty('servername')
    , serverproperty('productversion')
    , serverproperty('productlevel')
    ,convert(varchar(30),e.database_name) as DBname
    ,convert(varchar(20),convert(sysname,DatabasePropertyEx(e.database_name,'Recovery')))
    ,(Select datediff(dd,Max(backup_finish_date), getdate()) From backupset a Where a.database_name=e.database_name --and a.server_name  = @@servername
        and type='D'  Group by a.database_name) Full_Bk
        ,(Select datediff(dd,Max(backup_finish_date), getdate()) From backupset b  Where b.database_name=e.database_name
              --and b.server_name  = @@servername
              and type='L' Group by b.database_name) Log_Bk
       ,(Select datediff(dd,Max(backup_finish_date), getdate())  From backupset c Where c.database_name=e.database_name --and c.server_name  = @@servername
              and type='I' Group by c.database_name) Diff_Bk
From backupset e

Where
    e.database_name Not in ('tempdb','pubs','northwind','model')
    and databasepropertyex(database_name,'Status') = 'online'
    and e.database_name in (Select Distinct name from master..sysdatabases)
    --and e.server_name = select @@Servername
 
-- never backed up
Union all
select Distinct
    serverproperty('servername')
    , serverproperty('productversion')
    , serverproperty('productlevel')
    ,convert(varchar(30),name) as DBname
    , 'Never Backup, EVER'
    ,NULL
    ,NULL
    ,NULL
 
from master..sysdatabases as record
where
    name not in (select distinct database_name from msdb..backupset)
    and name not in ('tempdb','pubs','northwind','model')
order by 1,2

This is another script, that better displays the missing backups and TLOG intervale of backups. ( source: LINK )

  

/*
DESCRIPTION    List the age of the last backup for all databases  for FULL, DIFF and Tlog
Plus databases with no backups ever
CONFIGURATION   none
 
Compatibility list:
MSSQL2005
MSSQL2008
MSSQL2000
 */
SELECT
  DISTINCT
        a.Name AS DatabaseName ,
        CONVERT(SYSNAME, DATABASEPROPERTYEX(a.name, 'Recovery')) RecoveryModel ,
        COALESCE(( SELECT   CONVERT(VARCHAR(12), datediff(dd,MAX(backup_finish_date),getdate()), 101)
                   FROM     msdb.dbo.backupset
                   WHERE    database_name = a.name
                            AND type = 'D'
                            AND is_copy_only = '0'
                 ), 'No Full') AS 'Full (days ago)' ,
        COALESCE(( SELECT   CONVERT(VARCHAR(12), datediff(dd,MAX(backup_finish_date),getdate()), 101)
                   FROM     msdb.dbo.backupset
                   WHERE    database_name = a.name
                            AND type = 'I'
                            AND is_copy_only = '0'
                 ), 'No Diff') AS 'Diff (days ago)' ,
        CASE DATABASEPROPERTYEX(a.name, 'Recovery')
        WHEN 'SIMPLE' THEN '--'
        ELSE
        COALESCE(( SELECT   CONVERT(VARCHAR(20), datediff(hh,MAX(backup_finish_date),getdate()), 120)
                   FROM     msdb.dbo.backupset
                   WHERE    database_name = a.name
                            AND type = 'L'
                 ), 'No Log') 
        END AS 'LastLog Hours Ago' ,
        CASE DATABASEPROPERTYEX(a.name, 'Recovery')
        WHEN 'SIMPLE' THEN '--'
        ELSE
        COALESCE(( SELECT   CONVERT(VARCHAR(20), datediff(hh,backup_finish_date,getdate()), 120)
                   FROM     ( SELECT    ROW_NUMBER() OVER ( ORDER BY backup_finish_date DESC ) AS 'rownum' ,
                                        backup_finish_date
                              FROM      msdb.dbo.backupset
                              WHERE     database_name = a.name
                                        AND type = 'L'
                            ) withrownum
                   WHERE    rownum = 2
                 ), 'No Log')
        END AS 'LastLog2 hours ago'
                 
                 
                 
FROM    sys.databases a
        LEFT OUTER JOIN msdb.dbo.backupset b ON b.database_name = a.name
WHERE   a.name <> 'tempdb'
        AND a.state_desc = 'online'
GROUP BY a.Name ,
        a.compatibility_level
ORDER BY a.name

Backup – Total Size Full Backups

Backups, Reports, Space-Issues,

This script is useful to find the size all last backups and an estimate on how long they took the last time they run.

It displays two queries. The first one gets the start time of the first database and end time of the last one. Naturally, if all databases were not backup at the same time this value will be off.

The second query is more reliable as it presents information for each database

It queries the MSDB for last backup on each database.

  


/*
DESCRIPTION: Queries the MSDB database for the size of the last full backup of each database
Configuration: No Configuration needed
 
Two queires: individual sizes, and Total sizes PLUS DURATION
 
Compatibility list:
MSSQL2005 - MSSQL2008 - MSSQL2008R2 - MSSQL2012
 
Does not work: MSSQL2000
 Update Log:
*/
 
 DECLARE @t TABLE
(dbname VARCHAR(500), physical_device_name VARCHAR(2000)
, sizeMB NUMERIC(20,0), StartDate DATETIME
, FinishDate DATETIME,age_in_Days INT)
 
INSERT INTO @t
EXEC sp_MSforEachDb '
SELECT
TOP 1 (s.database_name)
,m.physical_device_name
,s.backup_size/1000000
,s.backup_start_date
,s.backup_finish_date
,DATEDIFF(dd,(s.[backup_finish_date]),GETDATE()) AS [age_in_days]
FROM msdb.dbo.backupset s
INNER JOIN msdb.dbo.backupmediafamily m ON s.media_set_id = m.media_set_id
WHERE
s.database_name = ''?''
and s.type =''D''
ORDER BY s.backup_finish_date desc
'
SELECT
SERVERPROPERTY('servername') 'ServerName',
SUM(sizeMB) 'TotalBackupMB',
MIN(StartDate),
sum(datediff(mi,(StartDate),(FinishDate))) 'DurationMinutes' -- This adds individaul backup times
FROM @t
 
SELECT dbname, physical_device_name, sizeMB,
Age_in_Days, StartDate, FinishDate,datediff(mi,(StartDate),(FinishDate)) 'DurationMinutes'
 
FROM @t