Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Differential Backup Tricks

By Qian Ye,

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_id	photo_desc	filepath	filename	takendate
1				C:\temp		p1.jpg	
2				C:\temp		p2.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_id	photo_desc	filepath	filename	takendate
1		test desc	C:\temp		p1.jpg	
2		test desc	C:\temp		p2.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_id	photo_desc	filepath	filename	takendate
1		test desc	C:\temp		p1.jpg	
2		test desc	C:\temp		p2.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_id	photo_desc	filepath	filename	takendate
1	test desc	C:\temp	p1.jpg	
2	test desc	C:\temp	p2.jpg	
3	test desc	C:\temp	p3.jpg	
4	test desc	C:\temp	p4.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_id	photo_desc	filepath	filename	takendate
1	test desc	C:\temp	p1.jpg	
2	test desc	C:\temp	p2.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_id	photo_desc	filepath	filename	takendate
1	test desc	C:\temp	p1.jpg	
2	test desc	C:\temp	p2.jpg	
3	test desc	C:\temp	p3.jpg	
4	test desc	C:\temp	p4.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.

Total article views: 8217 | Views in the last 30 days: 5
 
Related Articles
FORUM

Throttling database processes?

Need to throttle back certain databases processes...

FORUM

Database Incident Management Process

Database Incident Management Process?

ARTICLE

Process Support Database Framework

Do you use or need a database process framework? Read on to see if this is something that might help...

BLOG

Database Design Process

Buck Woody recently asked a question; how do you design a database. He outlined the process he fol...

BLOG

Photos from SQL Saturday

Check out the photos from SQL Saturday 3 - Jacksonville: http://tim-mitchell.spaces.live.com/

Tags
 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones