Differential backup problem in SMO

[Steve]

Hi All

I have a windows forms application vb.net 2008 which uses SMO to do backups
to SQL Server 2005 express databases at intervals

In between full backups I do Incremental backups and until recently all
worked well

Now when an Incremental backup is done it is being created as another FULL
Backup within the backup set

I have actually stepped through this code as the backup is occurring , then
check the backup file in SQL server management studio and the file contains
numerous FULL backups

Any help appreciated

Regards
Steve

here is the relevant part of my code.....

srv = New Server(srvConn)

Dim bkDatabase As New Backup

bkDatabase.Action = BackupActionType.Database

bkDatabase.Incremental = True

bkDatabase.Database = "Haircuts"

Dim bkpDevice As BackupDeviceItem = New BackupDeviceItem(path,
DeviceType.File)

bkDatabase.Devices.Add(bkpDevice)

bkDatabase.SqlBackup(srv)

 

[Hongye Sun [MSFT]]

Hi Steve,

Thanks for your post. My name is Hongye Sun [MSFT] and it is my pleasure to
work with you on this issue.

The code you provided looks correct. It seems to be a database problem.
Incremental backup is based on the most recent, previous full backup of the
data. This is known as the base of the differential. Incremental backup
captures only the data that has changed since that full backup. In order to
verify if the incremental backup works correctly on database side, please
do the following:

1. Use SQL Server Management Studio to test
Please open SQL Server Management Studio -> connect to relevant database ->
right click the database -> Tasks -> Back up -> Set backup type to
"Differential" -> Click Ok.

After that, check if the saved backup is a increase backup

2. Checking Base of the Differential in master DB

Execute the following SQL statement:
USE master;
SELECT * FROM sys.database_files
SELECT * FROM sys.master_files

Following is the three columns containing the base of the differential
information:
differential_base_lsn
Base for differential backups. Data extents changed after this LSN will be
included in a differential backup.

differential_base_guid
Unique identifier of the base backup on which a differential backup will
be based.

differential_base_time
Time that corresponds to differential_base_lsn.

If a database is read/write and is online, you can view these columns by
querying the sys.database_files catalog view. If the database is read-only
or offline, query the sys.master_files catalog view, instead.

Please check if the base of the differential is correct. (Both its file
path and its base time is correct)

We will be waiting for you reply. Thanks.

Regards,
Hongye Sun ([email protected], remove 'online.')
Microsoft Online Community Support

Delighting our customers is our #1 priority. We welcome your comments and
suggestions about how we can improve the support we provide to you. Please
feel free to let my manager know what you think of the level of service
provided. You can send feedback directly to my manager at:
(e-mail address removed).

==================================================
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/en-us/subscriptions/aa948868.aspx#notifications.

Note: MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 2 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions. Issues of this
nature are best handled working with a dedicated Microsoft Support Engineer
by contacting Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/en-us/subscriptions/aa948874.aspx
==================================================

This posting is provided "AS IS" with no warranties, and confers no rights.

 

[Steve]

Hi Hongye

Thanks for the reply

I found the problem

The file name I give to the backup used the customers business name and the
date and time to the nearest minute
e.g Salon Beauty Thursday, 27 November 2008 0945.bak

The date and time part of the filename being generated from the current
computer date and time

When a backup was run immediately after another backup, the file name to
save to was the same, as the time had not moved to the next minute yet
Hence the backup was included in the original backup file

I now use the time to the nearest second in the file name and all works fine
now

Regards
Steve

Hi Steve,

Thanks for your post. My name is Hongye Sun [MSFT] and it is my pleasure
to
work with you on this issue.

The code you provided looks correct. It seems to be a database problem.
Incremental backup is based on the most recent, previous full backup of
the
data. This is known as the base of the differential. Incremental backup
captures only the data that has changed since that full backup. In order
to
verify if the incremental backup works correctly on database side, please
do the following:

1. Use SQL Server Management Studio to test
Please open SQL Server Management Studio -> connect to relevant
database ->
right click the database -> Tasks -> Back up -> Set backup type to
"Differential" -> Click Ok.

After that, check if the saved backup is a increase backup

2. Checking Base of the Differential in master DB

Execute the following SQL statement:
USE master;
SELECT * FROM sys.database_files
SELECT * FROM sys.master_files

Following is the three columns containing the base of the differential
information:
differential_base_lsn
Base for differential backups. Data extents changed after this LSN will be
included in a differential backup.

differential_base_guid
Unique identifier of the base backup on which a differential backup will
be based.

differential_base_time
Time that corresponds to differential_base_lsn.

If a database is read/write and is online, you can view these columns by
querying the sys.database_files catalog view. If the database is read-only
or offline, query the sys.master_files catalog view, instead.

Please check if the base of the differential is correct. (Both its file
path and its base time is correct)

We will be waiting for you reply. Thanks.

Regards,
Hongye Sun ([email protected], remove 'online.')
Microsoft Online Community Support

Delighting our customers is our #1 priority. We welcome your comments and
suggestions about how we can improve the support we provide to you. Please
feel free to let my manager know what you think of the level of service
provided. You can send feedback directly to my manager at:
(e-mail address removed).

==================================================
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/en-us/subscriptions/aa948868.aspx#notifications.

Note: MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 2 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions. Issues of this
nature are best handled working with a dedicated Microsoft Support
Engineer
by contacting Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/en-us/subscriptions/aa948874.aspx
==================================================

This posting is provided "AS IS" with no warranties, and confers no
rights.

 

[Hongye Sun [MSFT]]

Thanks for sharing your solution with us, Steve. It is my pleasure to work
with you.

Have a nice day.

Regards,
Hongye Sun ([email protected], remove 'online.')
Microsoft Online Community Support

Delighting our customers is our #1 priority. We welcome your comments and
suggestions about how we can improve the support we provide to you. Please
feel free to let my manager know what you think of the level of service
provided. You can send feedback directly to my manager at:
(e-mail address removed).
 
This posting is provided "AS IS" with no warranties, and confers no rights.