A differential backup should back up files that have changed since the last full backup, but is there any chance when it's not doing this?
Here are the details of the problem I encountered. I have a table photos in database testF. it contains the following two rows of data before the full backup runs:
photo_idphoto_descfilepathfilenametakendate 1C:\tempp1.jpg 2C:\tempp2.jpg (2 row(s) affected)
Then I restored full backup of testF from file 'c:\temp\test.BAK' to database testT.
RESTORE DATABASE testT FROM DISK = 'c:\temp\test.BAK' WITH NORECOVERY, MOVE 'testF_Data' TO 'c:\temp\testT_Data.mdf', MOVE 'testF_Log' TO 'c:\temp\testT_Log.ldf'
Next I updated all the current photo_desc to 'test desc' and use differential backup to move changes to database testT.
Use testF update photos set photo_desc = 'test desc' Backup database [testF] to [test1] with differential RESTORE DATABASE testT FROM DISK = 'c:\temp\test1.BAK' WITH RECOVERY, MOVE 'testF_Data' TO 'c:\temp\testT_Data.mdf', MOVE 'testF_Log' TO 'c:\temp\testT_Log.ldf'
It works as planned so far. I can see the both rows' photo description changed from empty to 'test desc'.
Use testT select * from photos photo_idphoto_descfilepathfilenametakendate 1test descC:\tempp1.jpg 2test descC:\tempp2.jpg (2 row(s) affected)
However when I tried to insert two more rows to it and move the changes using differential backup and restore from testF to testT, it starts to ignore me. Here's the details:
Use testF
insert into photos (filepath, filename) values ('C:\temp', 'p3.jpg')
insert into photos (filepath, filename) values ('C:\temp', 'p4.jpg')
update photos set photo_desc = 'test desc'
Backup database [testF] to [test1] with differential
RESTORE DATABASE testT
FROM DISK = 'c:\temp\test.BAK'
WITH NORECOVERY,
MOVE 'testF_Data' TO 'c:\temp\testT_Data.mdf',
MOVE 'testF_Log' TO 'c:\temp\testT_Log.ldf'
RESTORE DATABASE testT
FROM DISK = 'c:\temp\test1.BAK'
WITH RECOVERY,
MOVE 'testF_Data' TO 'c:\temp\testT_Data.mdf',
MOVE 'testF_Log' TO 'c:\temp\testT_Log.ldf'
Use testT
select * from photos
Output Results are as follows:
(1 row(s) affected) (1 row(s) affected) (4 row(s) affected) Processed 32 pages for database 'testF', file 'testF_Data' on file 2. Processed 1 pages for database 'testF', file 'testF_Log' on file 2. BACKUP DATABASE WITH DIFFERENTIAL successfully processed 33 pages in 0.222 seconds (1.185 MB/sec). Processed 80 pages for database 'testT', file 'testF_Data' on file 1. Processed 1 pages for database 'testT', file 'testF_Log' on file 1. RESTORE DATABASE successfully processed 81 pages in 0.092 seconds (7.134 MB/sec). Processed 24 pages for database 'testT', file 'testF_Data' on file 1. Processed 1 pages for database 'testT', file 'testF_Log' on file 1. RESTORE DATABASE successfully processed 25 pages in 0.099 seconds (1.996 MB/sec). photo_idphoto_descfilepathfilenametakendate 1test descC:\tempp1.jpg 2test descC:\tempp2.jpg (2 row(s) affected)
It ignored two new rows I just inserted. Why? Since the problem is with the differential backup,
I started to try different types of differential backups. If I specify the file number in the restore as follows, it works.
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' Use testT select * from photos photo_idphoto_descfilepathfilenametakendate 1test descC:\tempp1.jpg 2test descC:\tempp2.jpg 3test descC:\tempp3.jpg 4test descC:\tempp4.jpg (4 row(s) affected)
Then I realized it's because without any parameters specified, the backup files keep appending to it, and the default restore always pickup the first one until we told it not to. But while I'm doing regualar backup and restore, I do not want to maintenance the file number, so here's the solution I'm choosing:
To be comparable with the example above, I truncate table photos and insert back two rows to it before backup:
Use testF
Truncate table photos
insert into photos (filepath, filename) values ('C:\temp', 'p1.jpg')
insert into photos (filepath, filename) values ('C:\temp', 'p2.jpg')
BACKUP DATABASE [testF] TO [test] WITH INIT , NOUNLOAD , NAME = N'testF backup', NOSKIP , STATS = 10, NOFORMAT
RESTORE DATABASE testT
FROM DISK = 'c:\temp\test.BAK'
WITH NORECOVERY,
MOVE 'testF_Data' TO 'c:\temp\testT_Data.mdf',
MOVE 'testF_Log' TO 'c:\temp\testT_Log.ldf'
update photos set photo_desc = 'test desc'
Backup database [testF] to [test1] with differential, INIT
RESTORE DATABASE testT
FROM DISK = 'c:\temp\test1.BAK'
WITH RECOVERY,
MOVE 'testF_Data' TO 'c:\temp\testT_Data.mdf',
MOVE 'testF_Log' TO 'c:\temp\testT_Log.ldf'
Use testT
select * from photos
Use testF
insert into photos (filepath, filename) values ('C:\temp', 'p3.jpg')
insert into photos (filepath, filename) values ('C:\temp', 'p4.jpg')
update photos set photo_desc = 'test desc'
Backup database [testF] to [test1] with differential, INIT
RESTORE DATABASE testT
FROM DISK = 'c:\temp\test.BAK'
WITH NORECOVERY,
MOVE 'testF_Data' TO 'c:\temp\testT_Data.mdf',
MOVE 'testF_Log' TO 'c:\temp\testT_Log.ldf'
RESTORE DATABASE testT
FROM DISK = 'c:\temp\test1.BAK'
WITH RECOVERY,
MOVE 'testF_Data' TO 'c:\temp\testT_Data.mdf',
MOVE 'testF_Log' TO 'c:\temp\testT_Log.ldf'
Use testT
select * from photos
Output Results:
(1 row(s) affected) (1 row(s) affected) 99 percent backed up. Processed 80 pages for database 'testF', file 'testF_Data' on file 1. 100 percent backed up. Processed 1 pages for database 'testF', file 'testF_Log' on file 1. BACKUP DATABASE successfully processed 81 pages in 0.168 seconds (3.907 MB/sec). Processed 80 pages for database 'testT', file 'testF_Data' on file 1. Processed 1 pages for database 'testT', file 'testF_Log' on file 1. RESTORE DATABASE successfully processed 81 pages in 0.063 seconds (10.418 MB/sec). (2 row(s) affected) Processed 24 pages for database 'testF', file 'testF_Data' on file 1. Processed 1 pages for database 'testF', file 'testF_Log' on file 1. BACKUP DATABASE WITH DIFFERENTIAL successfully processed 25 pages in 0.287 seconds (0.688 MB/sec). Processed 24 pages for database 'testT', file 'testF_Data' on file 1. Processed 1 pages for database 'testT', file 'testF_Log' on file 1. RESTORE DATABASE successfully processed 25 pages in 0.054 seconds (3.659 MB/sec). photo_idphoto_descfilepathfilenametakendate 1test descC:\tempp1.jpg 2test descC:\tempp2.jpg (2 row(s) affected) (1 row(s) affected) (1 row(s) affected) (4 row(s) affected) Processed 32 pages for database 'testF', file 'testF_Data' on file 1. Processed 1 pages for database 'testF', file 'testF_Log' on file 1. BACKUP DATABASE WITH DIFFERENTIAL successfully processed 33 pages in 0.244 seconds (1.078 MB/sec). Processed 80 pages for database 'testT', file 'testF_Data' on file 1. Processed 1 pages for database 'testT', file 'testF_Log' on file 1. RESTORE DATABASE successfully processed 81 pages in 0.054 seconds (12.155 MB/sec). Processed 32 pages for database 'testT', file 'testF_Data' on file 1. Processed 1 pages for database 'testT', file 'testF_Log' on file 1. RESTORE DATABASE successfully processed 33 pages in 0.078 seconds (3.373 MB/sec). photo_idphoto_descfilepathfilenametakendate 1test descC:\tempp1.jpg 2test descC:\tempp2.jpg 3test descC:\tempp3.jpg 4test descC:\tempp4.jpg (4 row(s) affected)
Conclusion
To make differential backup works, we need to use correct file number while we restore or use the INIT parameter to make backup file overwrite each time.
