A common task when carrying out a health check on a client’s SharePoint farm is to ensure that all databases are being backed up and that the database files have been sized correctly. Most of the time, a DBA will be tasked with carrying out backups and confirming that they are functional but in some cases, there is no DBA and there is no-one who can answer then questions around SQL Backups.
In this scenario it is useful to have a SQL script to capture and display this information.
Last Backups
The following code will present the dates of the backups, along with the collation, recovery model and whether auto shrink is on.
SELECT DISTINCT
d.Name AS [Database Name]
, d.recovery_model_desc AS [RecoveryModel]
, COALESCE((
SELECT
CONVERT(VARCHAR(12), MAX(bus_f.backup_finish_date), 101)
FROM msdb.dbo.backupset bus_f
WHERE
bus_f.database_name = d.name
AND
bus_f.type = 'd'
AND
bus_f.is_copy_only = '0'
), 'No Full') AS [Last Full Backup]
, COALESCE((
SELECT
CONVERT(VARCHAR(12), MAX(bus_d.backup_finish_date), 101)
FROM msdb.dbo.backupset bus_d
WHERE
bus_d.database_name = d.name
AND
bus_d.type = 'i'
AND
bus_d.is_copy_only = '0'
), 'No Diff') AS [Last Differential Backup]
, COALESCE((
SELECT
CONVERT(VARCHAR(20), MAX(bus_t.backup_finish_date), 120)
FROM msdb.dbo.backupset bus_t
WHERE
bus_t.database_name = d.name
AND
bus_t.type = 'l'
), 'No Log') AS [Last Log Backup]
, d.collation_name AS [Collation]
, d.recovery_model_desc AS [Recovery Mode]
, CASE(d.is_auto_shrink_on)
WHEN 1 THEN 'Yes'
WHEN 0 THEN 'No'
ELSE '??'
END AS [AutoShrink Is On]
FROM sys.databases d
WHERE
d.name != 'tempdb'
AND
d.state_desc = 'online'
ORDER BY
d.name
;
File Information
The following script will present details of all of the files associated with the databases, including the autogrowth settings and the maximum size that the files will grow to.
SELECT
DB_NAME(mf.database_id) AS [Database Name]
, mf.name AS [Logical Name]
, CONVERT (DECIMAL (20,2) , (CONVERT(DECIMAL, size)/128)) [Current File Size (MB)]
, CASE mf.is_percent_growth
WHEN 1 THEN 'Yes'
ELSE 'No'
END AS [Is Percent Growth]
, CASE mf.is_percent_growth
WHEN 1 THEN CONVERT(VARCHAR, mf.growth) + '%'
WHEN 0 THEN CONVERT(VARCHAR, mf.growth/128) + ' MB'
END AS [Growth Increment]
, CASE mf.is_percent_growth
WHEN 1 THEN CONVERT(DECIMAL(20,2), (((CONVERT(DECIMAL, size)\*growth)/100)\*8)/1024)
WHEN 0 THEN CONVERT(DECIMAL(20,2), (CONVERT(DECIMAL, growth)/128))
END AS [Next Autogrowth Size (MB)]
, CASE mf.max_size
WHEN 0 THEN 'No growth is allowed'
WHEN -1 THEN 'File will grow until the disk is full'
ELSE CONVERT(VARCHAR, mf.max_size/128)
END AS [Max Size (MB)]
, mf.physical_name AS [Physical File Name]
FROM sys.master_files mf
ORDER BY
DB_NAME(mf.database_id)