As a SQL Server database administrator (DBA), it is crucial to have a solid understanding of backup and recovery strategies. One common question that often arises during interviews is whether it is possible to perform a differential backup under the simple recovery model. Surprisingly, many candidates get this question wrong, highlighting the importance of clarifying this concept.

Before diving into the details, let’s briefly explain the difference between a full backup and a differential backup. A full backup captures the entire database, including all data and objects, while a differential backup only captures the changes made since the last full backup. This means that a differential backup is smaller and faster to perform, making it an efficient option for regular backups.

So, can you perform a differential backup under the simple recovery model? The answer is yes. However, understanding how SQL Server accomplishes this is essential.

To explore the inner workings of differential backups, we can utilize the undocumented command DBCC PAGE along with the dbcc ind command. These commands allow us to examine the data pages and their properties.

Let’s use the Adventureworks database as an example. Start by setting the database to the simple recovery model and performing a full backup for a clean start. Next, select a table and use the dbcc ind command to find a data page. Look for a row with a PageType value of 1, indicating a data page.

Once you have identified the PageFID (File ID) and PagePID (Page Number) for the data page, execute the DBCC PAGE command with the appropriate parameters. Make sure to enable trace flag 3604 to view the results in Management Studio.

When examining the output, look for the keyword “Diff” next to the “Not Changed” or “Changed” value. Initially, the page will be marked as “Not Changed” since no changes have been made. However, after updating the table and re-running the DBCC PAGE command, the page will be marked as “Changed,” indicating that it is now eligible for a differential backup.

Now, perform a differential backup using the BACKUP DATABASE command and check the DBCC PAGE output again. You will notice that the “Changed” value remains, as only a full backup or a restore operation can reset it to “Not Changed.”

At this point, you might wonder how SQL Server efficiently identifies the changed extents during a differential backup without scanning every page in the database. The answer lies in a data structure called the Differential Change Map (DCM).

The DCM is located on the sixth page of every data file and uses a bitmask structure to track changes. Similar to the Global Allocation Map (GAM) and Shared Global Allocation Map (SGAM), the DCM holds pointers to 64,000 extents or 4GB of data. Every 4GB, a new DCM appears to track the following 4GB of data.

When a change occurs on a data page, the DCM is updated to indicate the changed extent in which the page resides. During a differential backup, instead of scanning all the pages in the database, SQL Server simply scans the DCM to identify the necessary data. This optimization significantly speeds up the backup process by only capturing the changed extents.

In conclusion, differential backups can indeed occur under the simple recovery model in SQL Server. By leveraging the Differential Change Map (DCM), SQL Server efficiently identifies the changed extents, making the differential backup process faster and more efficient than performing a full backup.

As a DBA, it is crucial to understand the underlying mechanisms of SQL Server to effectively manage backups and ensure data integrity. By grasping concepts like differential backups and the DCM, you can optimize your backup strategies and minimize downtime in case of data loss or system failures.

Remember, backup and recovery are essential aspects of database administration, and staying up-to-date with best practices and new features in SQL Server is crucial for maintaining a robust and reliable database environment.