Need to find the Database Version? Last Log Backup? Last DBCC execution? Ummm….
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