Does Restoring A Database Modify the Boot Page?

At work, a key function of my team is importing new systems into our data warehouse, most of which come from vendors. To do this we utilize many different methods depending on the capabilities of the source system and desired timeliness of the data. ETL through SSIS, snapshot replication, and transactional replication are among the different technologies we employ. Occasionally for data sources backed by small SQL Server databases, we'll restore full backups of the database in lieu of ETL. This method is very straightforward and requires zero modifications to the source system, since all we need are copies of backups that are already being created.

When restoring a backup for this purpose I will always run DBCC CHECKDB to make sure all the data is intact before proceeding. Last week I was working on importing a new system in this way and for no reason at all I decided to check and see when the vendor last ran a consistency check before I started my own. SQL Server keeps track of the last successful consistency check and stores it in what's known as the "boot page" of each database, which is page 9 of file 1. The contents of the boot page can be viewed by using either the DBCC PAGE or DBCC DBINFO commands, both of which are officially undocumented by Microsoft but widely mentioned in blog posts. Paul Randal (@PaulRandal) has an excellent post on this topic that explains some of the data stored on the boot page.

While both commands will return what I want in this case, I usually run DBCC DBINFO as it's quicker to type, so that's what I'll demonstrate. To view the boot page using DBCC DBINFO you first need to enable trace flag 3604 so SQL Server will direct its output to the client. After that, pass the name of the database into DBCC DBINFO as shown below:

1DBCC TRACEON (3604);
2DBCC DBINFO ([DatabaseName]);

You'll see that the boot page contains a great deal of information, but what we're interested in is the dbi_dbccLastKnownGood field, which contains the date and time of the last run of DBCC CHECKDB where no errors were found.

Getting back to my story, this database I restored from our vendor showed a dbi_dbccLastKnownGood value of 1900-01-01, which means that DBCC CHECKDB has never been run on that database (or at least has never completed successfully.) This led me to wonder if the vendor wasn't running consistency checks, or if the restore process simply cleared out that value. I turned to the #sqlhelp hash tag on Twitter, where I got a quick reply from Paul Randal saying that the boot page is copied from the backup and restored just like any other page in the database.

To prove this on our own, here's a simple test:

 1-- create a database
 2IF EXISTS (SELECT [name] FROM sys.databases WHERE [name] = 'TestDB')
 3	DROP DATABASE TestDB;
 4GO
 5
 6CREATE DATABASE TestDB;
 7GO
 8
 9ALTER DATABASE TestDB SET RECOVERY SIMPLE;
10GO
11
12USE TestDB;
13GO
14
15-- run DBCC CHECKDB on it
16DBCC CHECKDB (TestDB);
17
18-- view the boot page
19DBCC TRACEON (3604);
20DBCC DBINFO (TestDB);
21-- save the output of DBCC DBINFO to a text file.
22
23-- back the database up
24BACKUP DATABASE TestDB
25TO DISK = 'F:\TestDB.bak';
26
27-- restore a copy of it
28RESTORE DATABASE TestDB_Restored
29FROM DISK = 'F:\TestDB.bak'
30WITH MOVE 'TestDB' TO 'F:\TestDB_R.mdf',
31MOVE 'TestDB_log' TO 'F:\TestDB_R.ldf';
32
33-- view the boot page from the restored copy
34DBCC DBINFO (TestDB_Restored);
35-- save the output of DBCC DBINFO to another text file.

From here I compared the two text files using a compare utility. I use WinMerge because it's very simple, and also can generate HTML output which you can see here. You'll notice the dbi_dbccLastKnownGood values are identical as expected. There are very few differences between the files, but all of them are a necessary part of the restore operation. The fields I saw differences in are:

dbi_dbid – Database IDs must be unique within an instance, so a restored database will receive a new ID.

dbi_dbname – We also restored our database with a different name, so the names are different as well.

dbi_crdate – The time the database was restored is listed as its creation date, so these values differ.

dbi_dbbackupLSN – I'm speculating here, but I'd imagine these fields differ because I viewed the boot page of TestDB before I backed it up.

dbi_LastLogBackupTime – This database is in the SIMPLE recovery model so log backups aren't possible, but the restore operation apparently sets this value.

recovery fork information – These fields will differ because my restore of the database created a different recovery path. This is a very interesting topic, and I recommend reading about recovery paths if you're curious.

service broker – Every database should have a unique Service Broker ID. Service Broker IDs should be unique globally, but there's no easy way to enforce that, so instead it is enforced at the instance level. Backing up and restoring a database doesn't change it's Service Broker ID by default, so if you restore a database with the same Service Broker ID as an already existing database, Service Broker is disabled on the new database. You can see that the dbi_svcBrokerGUID values match in both files, proving this is the case. To avoid Service Broker being automatically disabled at restore time, a new Service Broker ID can be generated during the restore by using the NEW_BROKER option in the RESTORE command.

We now have proof that the dbi_dbccLastKnownGood field is not changed by backing up and restoring, meaning that the vendor in question was not running DBCC CHECKDB. Don't be so quick to yell at your vendors though, because it turns out that not all integrity check operations will update this field. Erin Stellato (@erinstellato) wrote a great post last week on what checks update dbccLastKnownGood. Be sure to check it out to get that part of the story.