6

Brent Ozar had an interesing newsletter on 2015-06-22; How do you manage dbas measuring backups, where he thinks that a good DBA should check when

  • the last backup was done
  • it was last restored
  • the last CheckDB was done

It turned out to be a good exercise for me. Not only did I find two databases that were not backed up, I also discovered that we do have databases where we have never checked that we can restore the backup.

So; I have provided one solution to the task in the answer section. We have the convention that database names are unique and my script lists production server and test server(s), so I can compare them.
Do you have a solution that is better?

Best regards,

Henrik

  • 1
    I don't know that you can reliably say "this database's backups have never been checked that they can be restored." This logic only works if the only possible place to check for this is on the same server and if the restore is tested with a predictable restored database name. I can move the .bak file anywhere, rename it, and restore it as some other database on some other server. – Aaron Bertrand Jun 26 '15 at 13:00
  • In my case (I use Ola Hallengren's scripts), I can check the CommandLog table that those log to. I also get an email every day on success or failures (right now we only back up daily). And I also visit the server and check on the backups and logs, cpu, memory, etc. for a health spot check. – Chris Aldrich Jun 26 '15 at 13:09
  • @Aaron Bertrand: you are right; we have the convention that a database name is unique, and my script list the production server, and test server(s). That way we can check for CheckDB and Restores. – Henrik Staun Poulsen Jun 30 '15 at 6:52
  • @Chris Aldrich; I had not thought of Olas scripts. We only use it for index maintenance. That is a good idea. – Henrik Staun Poulsen Jun 30 '15 at 7:16
4

I would actually use dbatools (https://dbatools.io) these days

Get-DBALastBackup -SQLinstance Instance

Which looks like this

enter image description here 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
 }
}
  • Thank you very much for your suggestion. It looks a bit like mine, except mine runs entirely in SQL. But much shorter. Thank you – Henrik Staun Poulsen Jul 3 '15 at 15:41
2
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
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.

  • Running it with SQLCMD is a good idea. Your script looks like mine for the backup part. What about restores and CheckDB? – Henrik Staun Poulsen Jun 30 '15 at 7:18
1

Here's how I've done it. I don't check every server, but it could be adapted to do so without much hassle I guess.

-- I used the same method as written by Brent Ozar Unlimited in sp_Blitz to get the DBCC date.
-- All credit for that goes to them fof that.
-- I wrote the rest of it, so similarities to code, living or dead, is unintentional.

declare @databasesize table (dbname nvarchar(128), dbsize decimal(20, 6))
create table #dbcc (ParentObject varchar(255), [Object] varchar(255), Field varchar(255), Value varchar(255), DbName nvarchar(128) NULL)

insert into @databasesize exec sp_MSforeachdb '
    select
        ''?''
        ,((sum(size) * 1.0) / 128) as DatabaseSize
    from
        ?.sys.database_files df'

exec sp_MSforeachdb 'use [?]; insert into #dbcc (ParentObject,
            Object,
            Field,
            Value)
        EXEC (''DBCC DBInfo() With TableResults, NO_INFOMSGS'');
        UPDATE #dbcc SET DbName = N''?'' WHERE DbName IS NULL;'

select
    @@SERVERNAME as ServerName
    ,sd.[name] as DatabaseName
    ,ds.dbsize
    ,max(bsd.backup_finish_date) as LastFullBackupDate
    ,max(bsl.backup_finish_date) as LastLogBackupDate
    ,rh.restore_date as BackupFileRestoreDate
    ,nullif(dbc.Value, '1900-01-01 00:00:00.000') as LastDBCCDate
from
    sys.databases sd
    inner join @databasesize ds on sd.[name] = ds.dbname
    left outer join msdb..backupset bsd on sd.[name] = bsd.database_name
    and bsd.[type] = 'D'
    left outer join msdb..backupset bsl on sd.[name] = bsl.database_name
    and bsl.[type] = 'L'
    left outer join #dbcc dbc on sd.[name] = dbc.DbName
    and dbc.Field = 'dbi_dbccLastKnownGood'
    left outer join msdb..restorehistory rh on bsd.backup_set_id = rh.backup_set_id
group by
    sd.[name]
    ,ds.dbsize
    ,dbc.Value
    ,rh.restore_date

drop table #dbcc
  • hi, It looks as if you use the same approach as I do, but just one server at a time. That leads to code that is easier to read. Thank you very much. – Henrik Staun Poulsen Aug 25 '15 at 11:52

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.