check when last backup was taken
I would actually use dbatools (https://dbatools.io) these days
Get-DBALastBackup -SQLinstance Instance
Which looks like this
Old Answer Below
I am Powershell dude (sounds like I am attending a Powershell Anonymous meeting!!)
so this is the script I would use although I would use the objects returned to write to a database for a report or write to Excel or create an HTML email for my manager or whatever was required. Remember the $Servers should hold SERVERNAME\InstanceName,PORTNumber if any of those are not standard
# Load SMO extension
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo") | Out-Null;
$Servers =
## A list 'Servername1','Servername2' a text file Get-Content 'PATHTOSERVERFILE' or query a database Invoke-SQLCmd -Server SERVERNAME -Database ALLMyInstances -Query "Select Name FROM Instances"
foreach($Server in $Servers)
{
$srv = New-Object ('Microsoft.SqlServer.Management.Smo.Server') $Server
$lastDBCC_CHECKDB = @{Name="Last DBCC Check";Expression={$_.ExecuteWithResults("DBCC DBINFO () WITH TABLERESULTS").Tables[0] | where {$_.Field.ToString() -eq "dbi_dbccLastKnownGood"} | Select Value -ExpandProperty Value}}
foreach($db in $srv.databases)
{
$db|Select Parent,Name,LastBackupDate,LastDifferentialBackupDate,LastLogBackupDate,$lastDBCC_CHECKDB
}
}
USE mydb
SET NOCOUNT on
BEGIN TRY
DROP table #S
END TRY
BEGIN CATCH
END CATCH
BEGIN TRY
DROP TABLE #Mytable
END TRY
BEGIN CATCH
END CATCH
BEGIN TRY
DROP TABLE #DBInfoResults
END TRY
BEGIN CATCH
END CATCH
go
CREATE table #S ( server_name sysname, purpose VARCHAR(255), servertype VARCHAR(30) )
insert into #s
VALUES
('myserver', 'Test', 'Smallserver'),
('mybigserver\dsa', 'Production', 'Data ware house'),
('myservertoo', 'Test', 'Data ware house')
declare @Server_name sysname, @Purpose varchar(255)
declare @sql nvarchar(max) = N'', @s varchar(21)='', @loopCounter int=0, @debug TINYINT=1
DECLARE Server_Cursor CURSOR
FOR
SELECT s.server_name, s.purpose
FROM #S s
order by 1
CREATE TABLE #Mytable (server_name sysname, database_name sysname, LastFullBackup DATE, LastIncrementalBackup DATE, comment VARCHAR(255), SizeInGB BIGINT, LastRestoreDate DATE, LastKnownGoodDBCCCheck DATE)
CREATE TABLE #DBInfoResults ([ParentObject] VARCHAR(512),[Object] VARCHAR(512),[Field] VARCHAR(512),[VALUE] VARCHAR(512))
OPEN Server_Cursor
FETCH NEXT FROM Server_Cursor INTO @Server_name, @Purpose
WHILE @@FETCH_STATUS = 0 BEGIN
set @loopCounter +=1
RAISERROR ('%i server: "%s" ', 10 ,1, @Loopcounter, @Server_name) WITH NOWAIT
select @sql = N'
insert into #Mytable ( server_name, database_name, LastFullBackup, LastIncrementalBackup)
SELECT server_name, name, LastFullBackup, LastIncrementalBackup
from OPENROWSET(''SQLNCLI10'', ''Server='+@Server_name+';Trusted_Connection=yes;'',
''SELECT server.server_name, d.name, FullBackup.LastFullBackup, IncBackup.LastIncrementalBackup
FROM sys.databases d
OUTER APPLY (SELECT @@SERVERNAME AS server_name) AS server
OUTER APPLY (
SELECT TOP 1 B.backup_finish_date AS LastFullBackup
FROM msdb.dbo.backupset B
WHERE TYPE=''''d''''
AND server.server_name=B.server_name
AND d.name=b.database_name
ORDER BY B.backup_finish_date DESC
) AS FullBackup
OUTER APPLY (
SELECT TOP 1 B.backup_finish_date AS LastIncrementalBackup
FROM msdb.dbo.backupset B
WHERE TYPE=''''I''''
AND server.server_name=B.server_name
AND d.name=b.database_name
ORDER BY B.backup_finish_date DESC
) AS IncBackup
WHERE STATE_DESC = ''''ONLINE''''
AND name <> ''''tempdb'''' /* no backups, checkdbs needed */
ORDER BY 1,2
''
) as a
'
if @loopCounter <= 1 IF @debug <> 0 select @sql
begin try
exec sp_executesql @sql
end try
begin CATCH
print error_number()
print ERROR_MESSAGE()
end catch
/* loop the databases found on this server */
DECLARE @database_name sysname
DECLARE db_Cursor CURSOR
FOR
SELECT database_name
FROM #Mytable H
WHERE H.server_name=@Server_name
order by 1
OPEN db_Cursor
FETCH NEXT FROM db_Cursor INTO @database_name
WHILE @@FETCH_STATUS = 0 BEGIN
select @sql = N'
UPDATE #Mytable
SET SizeInGB=(
SELECT SizeInGB
from OPENROWSET(''SQLNCLI10'', ''Server='+@Server_name+';Trusted_Connection=yes;'',
''
SELECT SUM(CAST(size AS BIGINT))*8/1024/1024 as SizeInGB FROM ' + @database_name + '.sys.database_files DF
''
) as a
)
from #Mytable h
where h.server_name=''' + @server_name + ''' and h.DataBase_name=''' + @database_name + '''
'
RAISERROR ('%i server: "%s" db: %s get Size', 10 ,1, @Loopcounter, @Server_name, @database_name) WITH NOWAIT
if @loopCounter <= 1 IF @debug <> 0 select @sql
begin try
exec sp_executesql @sql
end try
begin CATCH
print error_number()
print ERROR_MESSAGE()
end CATCH
/* last restore date */
select @sql = N'
UPDATE #Mytable
SET LastRestoreDate=(
SELECT restore_date
FROM OPENROWSET(''SQLNCLI10'', ''Server='+@Server_name+';Trusted_Connection=yes;'',
''
SELECT max(restore_date) as restore_date FROM msdb.dbo.restorehistory where destination_database_name=''''' + @database_name + '''''
''
) as a
)
from #Mytable h
where h.server_name=''' + @server_name + ''' and h.DataBase_name=''' + @database_name + '''
'
RAISERROR ('%i server: "%s" db: %s get Restore date ', 10 ,1, @Loopcounter, @Server_name, @database_name) WITH NOWAIT
if @loopCounter <= 1 IF @debug <> 0 select @sql
begin try
exec sp_executesql @sql
end try
begin CATCH
print error_number()
print ERROR_MESSAGE()
end CATCH
/* last DBCC */
TRUNCATE TABLE #DBInfoResults
select @sql = N'
Begin Try
EXEC sys.sp_dropserver @server = ''myLinkedServer''
End try
begin catch
end catch
begin try
EXEC sp_addlinkedserver @server=''myLinkedServer'', @srvproduct='''', @provider=''sqlncli'', @datasrc='''+@Server_name+''', @location='''', @provstr='''', @catalog=''' + @database_name + '''
EXEC sp_addlinkedsrvlogin @rmtsrvname = ''myLinkedServer'', @useself = ''true''
EXEC sp_serveroption ''myLinkedServer'', ''rpc out'', true;
INSERT INTO #DBInfoResults
EXEC (''DBCC DBINFO() WITH TABLERESULTS, NO_INFOMSGS'') at myLinkedServer
UPDATE #Mytable
SET LastKnownGoodDBCCCheck=(SELECT value FROM #DBInfoResults where Field = ''dbi_dbccLastKnownGood'')
from #Mytable h
where h.server_name=''' + @server_name + ''' and h.DataBase_name=''' + @database_name + '''
end try
begin catch
print error_number()
print ERROR_MESSAGE()
end catch
EXEC sys.sp_dropserver @server = ''myLinkedServer''
'
RAISERROR ('%i server: "%s" dbcc: %s ', 10 ,1, @Loopcounter, @Server_name, @database_name) WITH NOWAIT
if @loopCounter <= 1 select @sql
begin try
exec sp_executesql @sql
end try
begin CATCH
print error_number()
print ERROR_MESSAGE()
end CATCH
SET @loopCounter+=1
FETCH NEXT FROM db_Cursor INTO @database_name
END
CLOSE db_Cursor ;
DEALLOCATE db_Cursor ;
FETCH NEXT FROM Server_Cursor INTO @Server_name, @Purpose
END
CLOSE Server_Cursor ;
DEALLOCATE Server_Cursor ;
UPDATE #Mytable SET Comment = 'Problem! ' FROM #Mytable H WHERE DATEDIFF(DAY, CASE WHEN h.LastIncrementalBackup>LastFullBackup THEN h.LastIncrementalBackup ELSE LastFullBackup END, GETDATE()) > 1
UPDATE #Mytable SET Comment = 'No backup required; structure in TFS.' WHERE database_name IN ('vdcasdw', 'mydbTemp', 'VTMChart', 'VTMFileStream', 'VTRArchive')
UPDATE #Mytable SET Comment = 'No backup required;' WHERE database_name LIKE '%ToBeDeleted'
UPDATE #Mytable SET Comment = 'No backup required; data in DWH.' WHERE database_name IN ('OperationalData')
UPDATE #Mytable SET Comment = 'No backup required; test server.' FROM #Mytable H INNER JOIN #S S ON S.server_name = H.server_name WHERE Purpose IN ('test', 'Development')
/* list all checks */
SELECT top 10000 h.*, s.purpose, s.servertype FROM #Mytable H
INNER JOIN #S S ON S.server_name = H.server_name
ORDER BY 1 DESC
/* run report on production servers */
SELECT H.server_name, H.database_name, COALESCE(CAST(H.LastFullBackup AS VARCHAR(30)), 'no backup exists!') AS LastFullBackup
, COALESCE(CAST(H.LastIncrementalBackup AS VARCHAR(30)), '') AS LastIncrementalBackup
, COALESCE(CAST(DATEDIFF(DAY, CASE WHEN h.LastIncrementalBackup>LastFullBackup THEN h.LastIncrementalBackup ELSE LastFullBackup END, GETDATE()) AS VARCHAR(30)), '') AS DaysSinceLastBackup
, COALESCE(comment, '') AS Comment
, COALESCE(CAST(sizeinGB AS VARCHAR(30)), '') AS SizeinGB
, COALESCE(CAST(H2.LastRestoreDate AS VARCHAR(30)), 'Backup never tested!') AS LastRestoreDate
, COALESCE(CAST(DATEDIFF(DAY, h2.LastRestoreDate, GETDATE()) AS VARCHAR(30)), '') AS DaysSinceRestore
, CASE WHEN H2.LastKnownGoodDBCCCheck <> '1900-01-01' THEN (CAST(H2.LastKnownGoodDBCCCheck AS VARCHAR(30))) else 'A Database without DBCC CheckDB' END AS LastKnownGoodDBCCCheck
, CASE WHEN H2.LastKnownGoodDBCCCheck <> '1900-01-01' THEN (CAST(DATEDIFF(DAY, h2.LastKnownGoodDBCCCheck, GETDATE()) AS VARCHAR(30))) else '' END AS DaysSinceLastKnownGoodDBCCCheck
, h2.Purpose AS SystemThatExists
FROM #Mytable H
INNER JOIN #S S ON S.server_name = H.server_name
OUTER APPLY (
SELECT MAX(LastKnownGoodDBCCCheck) AS LastKnownGoodDBCCCheck, MAX(LastRestoreDate ) AS LastRestoreDate, utl.CommaListConcatenate(s3.Purpose) AS Purpose FROM #Mytable H3
INNER JOIN #S S3 ON S3.server_name = H3.server_name
WHERE h.database_name=h3.database_name AND s3.servertype=s.servertype
) AS h2
WHERE s.purpose='production'
ORDER BY 1,2
maybe I'm a bit too "old school" but I've used a query and script for about 10 years now since the SQL Server 2005 days:
SELECT LEFT(d.name,20) AS database_name,
CONVERT(VARCHAR(16), MAX(CASE b.[type] WHEN 'D' THEN b.backup_finish_date END), 120) AS LastFullBackup,
CONVERT(VARCHAR(16), MAX(CASE b.[type] WHEN 'I' THEN b.backup_finish_date END), 120) AS LastDiffBackup,
CONVERT(VARCHAR(16), MAX(CASE b.[type] WHEN 'L' THEN b.backup_finish_date END), 120) AS LastLogBackup,
CONVERT(VARCHAR(16), MAX(CASE WHEN b.[type] NOT IN ('D','I','L') THEN b.backup_finish_date END), 120) AS LastOtherBackup
FROM sys.databases d
LEFT OUTER JOIN msdb.dbo.backupset b ON d.name = b.database_name
WHERE d.name <> 'tempdb'
GROUP BY d.database_id, d.name
ORDER BY CASE WHEN d.database_id <= 4 THEN 0 ELSE 1 END, d.name
This is called by a CMD script I schedule that calls SQLCMD to run this once per instance and pipes the output to a text file that I e-mail at the end.