Backup software and Maintenance plans usually take care of tracking backup schedules and maintenance schedules for us… but how else do we find this information inside SQL Server?  The following query will get the last backup date and time from MSDB:

select database_name, max(backup_finish_date) as 'last backup' from dbo.backupset
    group by database_name

And the error log records the last DBCC. 

Also, if you have access to the backup file, you can get the last backup timestamp and database version (in the backup file) using the RESTORE HEADERONLY command.

Though most of this is maintained and available in MSDB and other system base tables (database create date), I wanted to introduce you to the database boot page.  This information (and other information) is also stored in each database on the Database Boot Page.  Not sure which page that is?  Well, there are 2 ways you can get to this information stored on it.

First, we can use DBCC DBINFO:

use scratchdb
go

dbcc traceon(3604)
go

dbcc dbinfo()
go

And we get our answers (results truncated):

DBCC execution completed. If DBCC printed error messages, contact your system administrator.

DBINFO STRUCTURE:


DBINFO @0x000000001A2DD520

dbi_dbid = 15                        dbi_status = 65536                   dbi_nextid = 2105058535
dbi_dbname = scratchdb               dbi_maxDbTimestamp = 2000            dbi_version = 661
dbi_createVersion = 661              dbi_ESVersion = 0                    
dbi_nextseqnum = 1900-01-01 00:00:00.000                                  dbi_crdate = 2012-07-29 18:43:55.670
dbi_filegeneration = 0               
dbi_checkptLSN

m_fSeqNo = 28                        m_blockOffset = 54                   m_slotId = 64
dbi_RebuildLogs = 0                  dbi_dbccFlags = 2                    
dbi_dbccLastKnownGood = 1900-01-01 00:00:00.000                           
dbi_dbbackupLSN

m_fSeqNo = 28                        m_blockOffset = 54                   m_slotId = 64

dbi_oldestBackupXactLSN

m_fSeqNo = 0                         m_blockOffset = 0                    m_slotId = 0
dbi_LastLogBackupTime = 2012-07-29 18:44:37.040                           
dbi_differentialBaseLSN

 

Here you can see our information as well as the original database creation version, database status, it’s DB ID, and other information that is needed by SQL Server at startup when mounting a database.

So my database is version 661 (SQL Server 2008 R2 SP1), and the log was just backed up (and the database created just before that), and has never had DBCC CHECKDB run.  As mentioned, this information is stored on the database boot page (pagetype 13).  That is page #10 or ordinal position 9 starting from 0.  You can find it with DBCC PAGE (the page will have just 1 slot for the DBINFO structure):

dbcc traceon(3604)
go

dbcc page(15, 1, 9, 3)
go
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

PAGE: (1:9)


BUFFER:


BUF @0x00000000B8FDBE80

bpage = 0x00000000B89FC000           bhash = 0x0000000000000000           bpageno = (1:9)
bdbid = 15                           breferences = 0                      bcputicks = 0
bsampleCount = 0                     bUse1 = 57609                        bstat = 0xc00009
blog = 0x89898989                    bnext = 0x0000000000000000           

PAGE HEADER:


Page @0x00000000B89FC000

m_pageId = (1:9)                     m_headerVersion = 1                  m_type = 13
m_typeFlagBits = 0x0                 m_level = 0                          m_flagBits = 0x200
m_objId (AllocUnitId.idObj) = 99     m_indexId (AllocUnitId.idInd) = 0    Metadata: AllocUnitId = 6488064
Metadata: PartitionId = 0            Metadata: IndexId = 0                Metadata: ObjectId = 99
m_prevPage = (0:0)                   m_nextPage = (0:0)                   pminlen = 0
m_slotCnt = 1                        m_freeCnt = 6650                     m_freeData = 1540
m_reservedCnt = 0                    m_lsn = (28:85:3)                    m_xactReserved = 0
m_xdesId = (0:0)                     m_ghostRecCnt = 0                    m_tornBits = –223305262
<truncated>
DBINFO @0x000000001857A060

dbi_dbid = 15                        dbi_status = 65536                   dbi_nextid = 2105058535
dbi_dbname = scratchdb               dbi_maxDbTimestamp = 2000            dbi_version = 661
dbi_createVersion = 661              dbi_ESVersion = 0                    
dbi_nextseqnum = 1900-01-01 00:00:00.000                                  dbi_crdate = 2012-07-29 18:43:55.670
dbi_filegeneration = 0               
dbi_checkptLSN

m_fSeqNo = 28                        m_blockOffset = 54                   m_slotId = 64
dbi_RebuildLogs = 0                  dbi_dbccFlags = 2                    
dbi_dbccLastKnownGood = 1900-01-01 00:00:00.000                           
dbi_dbbackupLSN

m_fSeqNo = 28                        m_blockOffset = 54                   m_slotId = 64

dbi_oldestBackupXactLSN

m_fSeqNo = 0                         m_blockOffset = 0                    m_slotId = 0
dbi_LastLogBackupTime = 2012-07-29 18:44:37.040                           
dbi_differentialBaseLSN

-Jay