SQL Server Complete Over View Details Query
Query to find Daily backup Status, data,log,Total Size and other Useful information of all databases – SQL 2000/2005/2008/R2
Method :1Find out the database file,log file, total size and other useful information about a database on SQL Server 2000/2005/2008/R2 versions. The requirement is to use same query and it has to be executed across all version of SQL SERVER. We can also do this using dynamic SQL’s.
Here is the T-SQL which you can use to run on any version of SQL Server to fetch the result.
Method :1Find out the database file,log file, total size and other useful information about a database on SQL Server 2000/2005/2008/R2 versions. The requirement is to use same query and it has to be executed across all version of SQL SERVER. We can also do this using dynamic SQL’s.
Here is the T-SQL which you can use to run on any version of SQL Server to fetch the result.
USE MASTER
Go
SELECT @@SERVERNAME Servername,@@VERSION SQLVersion,
CONVERT(VARCHAR(25), DB.name) AS dbName,
CONVERT(VARCHAR(25), suser_sname(sid)) AS dbOwner,
filename AS FileName,
CONVERT(VARCHAR(10), DATABASEPROPERTYEX(name, 'status')) AS [Status],
(SELECT COUNT(1) FROM sysaltfiles WHERE DB_NAME(dbid) = DB.name AND groupid !=0 ) AS DataFiles,
(SELECT SUM((size*8)/1024) FROM sysaltfiles WHERE DB_NAME(dbid) = DB.name AND groupid!=0) AS [Data MB],
(SELECT COUNT(1) FROM sysaltfiles WHERE DB_NAME(dbid) = DB.name AND groupid=0) AS LogFiles,
(SELECT SUM((size*8)/1024) FROM sysaltfiles WHERE DB_NAME(dbid) = DB.name AND groupid=0) AS [Log MB],
(SELECT SUM((size*8)/1024) FROM sysaltfiles WHERE DB_NAME(dbid) = DB.name AND groupid!=0)+(SELECT SUM((size*8)/1024) FROM sysaltfiles WHERE DB_NAME(dbid) = DB.name AND groupid=0) TotalSizeMB,
convert(sysname,DatabasePropertyEx(name,'Updateability')) Updateability,
convert(sysname,DatabasePropertyEx(name,'UserAccess')) UserAccess ,
convert(sysname,DatabasePropertyEx(name,'Recovery')) RecoveryModel ,
convert(sysname,DatabasePropertyEx(name,'Version')) Version ,
CASE cmptlevel
WHEN 60 THEN '60 (SQL Server 6.0)'
WHEN 65 THEN '65 (SQL Server 6.5)'
WHEN 70 THEN '70 (SQL Server 7.0)'
WHEN 80 THEN '80 (SQL Server 2000)'
WHEN 90 THEN '90 (SQL Server 2005)'
WHEN 100 THEN '100 (SQL Server 2008)'
END AS [compatibility level],
CONVERT(VARCHAR(20), crdate, 103) + ' ' + CONVERT(VARCHAR(20), crdate, 108) AS [Creation date],
ISNULL((SELECT TOP 1
CASE TYPE WHEN 'D' THEN 'Full' WHEN 'I' THEN 'Differential' WHEN 'L' THEN 'Transaction log' END + ' – ' +
LTRIM(ISNULL(STR(ABS(DATEDIFF(DAY, GETDATE(),Backup_finish_date))) + ' days ago', 'NEVER')) + ' – ' +
CONVERT(VARCHAR(20), backup_start_date, 103) + ' ' + CONVERT(VARCHAR(20), backup_start_date, 108) + ' – ' +
CONVERT(VARCHAR(20), backup_finish_date, 103) + ' ' + CONVERT(VARCHAR(20), backup_finish_date, 108) +
' (' + CAST(DATEDIFF(second, BK.backup_start_date,
BK.backup_finish_date) AS VARCHAR(4)) + ' '+ 'seconds)'
FROM msdb.dbo.backupset BK WHERE BK.database_name = DB.name ORDER BY backup_set_id DESC),'-') AS [Last backup]
FROM sysdatabases DB
ORDER BY dbName, [Last backup] DESC, NAME
|
OUTPUT:
Method :2
USE AdventureWorks
GO
-- Get Backup History for required database
SELECT TOP 100
s.database_name,
m.physical_device_name,
CAST(CAST(s.backup_size / 1000000 AS INT) AS VARCHAR(14)) + ' ' + 'MB' AS bkSize,
CAST(DATEDIFF(second, s.backup_start_date,
s.backup_finish_date) AS VARCHAR(4)) + ' ' + 'Seconds' TimeTaken,
s.backup_start_date,
CAST(s.first_lsn AS VARCHAR(50)) AS first_lsn,
CAST(s.last_lsn AS VARCHAR(50)) AS last_lsn,
CASE s.[type]
WHEN 'D' THEN 'Full'
WHEN 'I' THEN 'Differential'
WHEN 'L' THEN 'Transaction Log'
END AS BackupType,
s.server_name,
s.recovery_model
FROM msdb.dbo.backupset s
INNER JOIN msdb.dbo.backupmediafamily m ON s.media_set_id = m.media_set_id
WHERE s.database_name = DB_NAME() -- Remove this line for all the database
ORDER BY backup_start_date DESC, backup_finish_date
GO
|
Method :3
Database Backup from different view points.To get the List/History/Log of all the Successful Backups:
To get a list of all successful Backups taken till date for a particular Database:
To get the List of all Databases which are not backed up till date:
To get a list of the Latest successful backups of all Databases:
To get a list of Databases that were backed-up and do not currently exist:
Database Backup from different view points.To get the List/History/Log of all the Successful Backups:
SELECT
b.machine_name,
b.server_name,
b.database_name as DBName,
b.backup_start_date,
b.backup_finish_date,
CASE
WHEN b.[type] = 'D' THEN 'Database'
WHEN b.[type] = 'I' THEN 'Differential database'
WHEN b.[type] = 'L' THEN 'Log'
WHEN b.[type] = 'F' THEN 'File or filegroup'
WHEN b.[type] = 'G' THEN 'Differential file'
WHEN b.[type] = 'P' THEN 'Partial'
WHEN b.[type] = 'Q' THEN 'Differential partial'
ELSE b.[type]
END Backup_Type,
b.expiration_date,
b.[user_name],
DATEDIFF(MINUTE,b.backup_start_date ,b.backup_finish_date) as Total_Time_in_Minute,
b.recovery_model,
b.backup_size/(1024 * 1024 * 1024) as Total_Size_GB,
bf.physical_device_name as Location
FROM
msdb.dbo.backupset AS b
INNER JOIN msdb.dbo.backupmediafamily AS bf
ON b.media_set_id=bf.media_set_id
ORDER BY
b.backup_start_date DESC
GO
|
To get a list of all successful Backups taken till date for a particular Database:
DECLARE @DBName AS VARCHAR(100) = 'Your Database Name'
SELECT
b.machine_name,
b.server_name,
b.database_name as DBName,
b.backup_start_date,
b.backup_finish_date,
CASE
WHEN b.[type] = 'D' THEN 'Database'
WHEN b.[type] = 'I' THEN 'Differential database'
WHEN b.[type] = 'L' THEN 'Log'
WHEN b.[type] = 'F' THEN 'File or filegroup'
WHEN b.[type] = 'G' THEN 'Differential file'
WHEN b.[type] = 'P' THEN 'Partial'
WHEN b.[type] = 'Q' THEN 'Differential partial'
ELSE b.[type]
END Backup_Type,
b.expiration_date,
b.[user_name],
DATEDIFF(MINUTE,b.backup_start_date ,b.backup_finish_date) as Total_Time_in_Minute,
b.recovery_model,
b.backup_size/(1024 * 1024 * 1024) as Total_Size_GB,
bf.physical_device_name as Location
FROM
msdb.dbo.backupset AS b
INNER JOIN msdb.dbo.backupmediafamily AS bf
ON b.media_set_id=bf.media_set_id
WHERE
b.database_name = @DBName
ORDER BY
b.backup_start_date DESC
GO
|
To get the List of all Databases which are not backed up till date:
SELECT
d.name [DB_Name]
FROM
master.sys.databases d
LEFT JOIN msdb.dbo.backupset b
ON b.database_name = d.name
WHERE
d.database_id IS NULL
To get the List of all Databases which are not backed up since last X days
DECLARE @LastXDays AS INT = 1
;WITH LatestBackupSet AS (
SELECT
b.database_name as DBName,
b.backup_start_date LastBackedUpOn,
b.[user_name],
ROW_NUMBER() OVER(PARTITION BY b.database_name ORDER BY b.backup_start_date DESC) Rnk
FROM
msdb.dbo.backupset AS b
)
SELECT
lbs.DBName,
lbs.LastBackedUpOn,
lbs.[user_name]
FROM
LatestBackupSet AS lbs
WHERE
DATEDIFF(DAY,lbs.LastBackedUpOn ,CURRENT_TIMESTAMP) = @LastXDays
AND lbs.Rnk = 1
ORDER BY
lbs.DBName DESC
GO
|
To get a list of the Latest successful backups of all Databases:
;WITH LatestBackupSet AS (
SELECT
b.machine_name,
b.server_name,
b.database_name as DBName,
b.backup_start_date,
b.backup_finish_date,
CASE
WHEN b.[type] = 'D' THEN 'Database'
WHEN b.[type] = 'I' THEN 'Differential database'
WHEN b.[type] = 'L' THEN 'Log'
WHEN b.[type] = 'F' THEN 'File or filegroup'
WHEN b.[type] = 'G' THEN 'Differential file'
WHEN b.[type] = 'P' THEN 'Partial'
WHEN b.[type] = 'Q' THEN 'Differential partial'
ELSE b.[type]
END Backup_Type,
b.expiration_date,
b.[user_name],
DATEDIFF(MINUTE,b.backup_start_date ,b.backup_finish_date) as Total_Time_in_Minute,
b.recovery_model,
b.backup_size/(1024 * 1024 * 1024) as Total_Size_GB,
bf.physical_device_name as Location,
ROW_NUMBER() OVER(PARTITION BY b.database_name ORDER BY b.backup_start_date DESC) Rnk
FROM
msdb.dbo.backupset AS b
INNER JOIN msdb.dbo.backupmediafamily AS bf
ON b.media_set_id=bf.media_set_id
)
SELECT
machine_name,
server_name,
DBName,
backup_start_date,
backup_finish_date,
Backup_Type,
expiration_date,
[user_name],
Total_Time_in_Minute,
recovery_model,
Total_Size_GB,
Location
FROM
LatestBackupSet AS lbs
WHERE
lbs.Rnk = 1
ORDER BY
lbs.DBName DESC
GO
|
To get the Latest successful backup of a particular Database:
DECLARE @DBName AS VARCHAR(100) = 'Your Database Name'
;WITH LatestBackupSet AS (
SELECT
b.machine_name,
b.server_name,
b.database_name as DBName,
b.backup_start_date,
b.backup_finish_date,
CASE
WHEN b.[type] = 'D' THEN 'Database'
WHEN b.[type] = 'I' THEN 'Differential database'
WHEN b.[type] = 'L' THEN 'Log'
WHEN b.[type] = 'F' THEN 'File or filegroup'
WHEN b.[type] = 'G' THEN 'Differential file'
WHEN b.[type] = 'P' THEN 'Partial'
WHEN b.[type] = 'Q' THEN 'Differential partial'
ELSE b.[type]
END Backup_Type,
b.expiration_date,
b.[user_name],
DATEDIFF(MINUTE,b.backup_start_date ,b.backup_finish_date) as Total_Time_in_Minute,
b.recovery_model,
b.backup_size/(1024 * 1024 * 1024) as Total_Size_GB,
bf.physical_device_name as Location,
ROW_NUMBER() OVER(PARTITION BY b.database_name ORDER BY b.backup_start_date DESC) Rnk
FROM
msdb.dbo.backupset AS b
INNER JOIN msdb.dbo.backupmediafamily AS bf
ON b.media_set_id=bf.media_set_id
WHERE
b.database_name = @DBName
)
SELECT
machine_name,
server_name,
DBName,
backup_start_date,
backup_finish_date,
Backup_Type,
expiration_date,
[user_name],
Total_Time_in_Minute,
recovery_model,
Total_Size_GB,
Location
FROM
LatestBackupSet AS lbs
WHERE
lbs.Rnk = 1
ORDER BY
lbs.DBName DESC
GO
|
To get a list of Databases that were backed-up and do not currently exist:
SELECT
DISTINCT b.database_name
FROM
msdb.dbo.backupset b
WHERE
DB_ID(b.database_name) IS NULL
|