When it comes to backing up your SQL Server databases, there are different types of backups you can choose from. One of these types is the differential backup. A differential backup should back up files that have changed since the last full backup, but what happens when it’s not doing this? Let’s explore this issue and find a solution.
Consider a scenario where you have a table called “photos” in a database called “testF”. Before running a full backup, this table contains two rows of data:
| photo_id | photo_desc | filepath | filename | takendate |
|---|---|---|---|---|
| 1 | C:\tempp1.jpg | |||
| 2 | C:\tempp2.jpg |
After restoring a full backup of the “testF” database to a new database called “testT”, you update the “photo_desc” column for both rows to ‘test desc’ and use a differential backup to move the changes to the “testT” database. This works as planned, and you can see that both rows’ photo descriptions have changed:
| photo_id | photo_desc | filepath | filename | takendate |
|---|---|---|---|---|
| 1 | test desc | C:\tempp1.jpg | ||
| 2 | test desc | C:\tempp2.jpg |
However, when you try to insert two more rows into the “photos” table and move the changes using a differential backup and restore from “testF” to “testT”, it starts to ignore the new rows. The differential backup only includes the changes made before the last full backup.
To resolve this issue, you can specify the file number in the restore command. By doing this, the restore operation will pick up the correct backup file that contains the changes you want to restore. For example:
RESTORE DATABASE testT
FROM DISK = 'c:\temp\test1.BAK'
WITH RECOVERY, FILE = 2,
MOVE 'testF_Data' TO 'c:\temp\testT_Data.mdf',
MOVE 'testF_Log' TO 'c:\temp\testT_Log.ldf'
However, specifying the file number every time you perform a restore can be cumbersome. To avoid this, you can use the INIT parameter when performing the backup. This parameter ensures that the backup file is overwritten each time, so you don’t have to worry about maintaining the file number.
Here’s an example of how you can use the INIT parameter:
BACKUP DATABASE [testF] TO [test] WITH INIT, NOUNLOAD, NAME = N'testF backup', NOSKIP, STATS = 10, NOFORMAT
By truncating the “photos” table and inserting the rows again before performing the backup, you can ensure that the differential backup includes all the changes you want to restore.
In conclusion, to make differential backups work as expected, you need to either use the correct file number while restoring or use the INIT parameter to overwrite the backup file each time. This ensures that the differential backup includes all the changes made since the last full backup.