DIFFERENTIAL Backup Problem

I have a full recovery model backup scenario in place where I do full db
backups every night, incremental transaction log backups every 15 minutes and
differential backups of the database every hour. O/S is Windows 2000 Advanced
Server, SQL 2000 sp3a, latest patches, hot fixes etc on everything…
This has been working flawlessly for over a year; now for some reason one of
my differential backup jobs is backing up the entire database every hour
instead of what has changed since the last full.
This happened for the first time about a week ago and after checking the
database for errors etc and finding nothing; all I could narrow it down to
was that for some
reason the job did not like the applicable record in msdb..backupset; so the
interim fix was to do a manual full backup forcing a new record to be created
and it started working fine again for a few days...
It started doing it again on the same database yesterday; same same; no
errors found; the differential just started doing full backups instead of
differentials...
Here is my script for my incremental backup;
declare @.strFileNamePath varchar(100)
set @.strFileNamePath = '\\BackupServer\SQL Backups\TheDbTransLog' +
convert(varchar, getdate(),112) + cast(datepart(hh,getdate())as varchar) +
cast(datepart(mi,getdate())as varchar) + '.bak'
BACKUP LOG [TheDb]
TO DISK = @.strFileNamePath
WITH NOINIT, NOUNLOAD, RETAINDAYS = 5,
NAME = N'TheDb backup',
NOSKIP,
STATS = 10,
NOFORMAT
DECLARE @.i INT
select @.i = position
from msdb..backupset
where database_name='TheDb'
and type!='F'
and backup_set_id=(select max(backup_set_id)
from msdb..backupset
where database_name='TheDb')
RESTORE VERIFYONLY
FROM DISK = @.strFileNamePath
WITH FILE = @.i
Here is my script for my differential backup:
declare
@.strFileNamePath varchar(100)
set @.strFileNamePath = '\\BackupServer\SQL Backups\TheDbDaily' +
convert(varchar, getdate(),112) + cast(datepart(hh,getdate())as varchar) +
'.bak'
BACKUP DATABASE [TheDb]
TO DISK = @.strFileNamePath
WITH NOINIT, NOUNLOAD, DIFFERENTIAL, RETAINDAYS = 5,
NAME = N'TheDb backup',
NOSKIP ,
STATS = 10,
NOFORMAT
DECLARE @.i INT
select @.i = position
from msdb..backupset
where database_name='TheDb'and type!='F'
and backup_set_id=(select max(backup_set_id)
from msdb..backupset
where database_name='TheDb')
RESTORE VERIFYONLY FROM DISK = @.strFileNamePath
WITH FILE = @.i
The full (a db maintenance plan job):
EXECUTE master.dbo.xp_sqlmaint N'-PlanID
65514236-64B7-493A-841A-88F695626EB1 -Rpt "\\BackupServer\SQL Backups\TheDb
DB Maintenance Plan14.txt" -DelTxtRpt 5DAYS -WriteHistory -VrfyBackup
-BkUpOnlyIfClean -CkDB -BkUpMedia DISK -BkUpDB "\\BackupServer\SQL
Backups\TheDb" -DelBkUps 5DAYS -BkExt "BAK"'
As I said all these have been working flawlessly for over a year...
Thanks in advance.
I'm not sure what you are saying. Does it really produce a full backup (judging by file size)? If
you look at this backup using RESTORE HEADERONLY, is it a db backup or a diff backup? I don't see
how backup history msdb could affect the backup command you execute.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"LvBohemian" <LvBohemian@.discussions.microsoft.com> wrote in message
news:5F24EC8D-A6B0-496B-B1F1-7D8B3687F059@.microsoft.com...
>I have a full recovery model backup scenario in place where I do full db
> backups every night, incremental transaction log backups every 15 minutes and
> differential backups of the database every hour. O/S is Windows 2000 Advanced
> Server, SQL 2000 sp3a, latest patches, hot fixes etc on everything…
> This has been working flawlessly for over a year; now for some reason one of
> my differential backup jobs is backing up the entire database every hour
> instead of what has changed since the last full.
> This happened for the first time about a week ago and after checking the
> database for errors etc and finding nothing; all I could narrow it down to
> was that for some
> reason the job did not like the applicable record in msdb..backupset; so the
> interim fix was to do a manual full backup forcing a new record to be created
> and it started working fine again for a few days...
> It started doing it again on the same database yesterday; same same; no
> errors found; the differential just started doing full backups instead of
> differentials...
> Here is my script for my incremental backup;
> declare @.strFileNamePath varchar(100)
> set @.strFileNamePath = '\\BackupServer\SQL Backups\TheDbTransLog' +
> convert(varchar, getdate(),112) + cast(datepart(hh,getdate())as varchar) +
> cast(datepart(mi,getdate())as varchar) + '.bak'
> BACKUP LOG [TheDb]
> TO DISK = @.strFileNamePath
> WITH NOINIT, NOUNLOAD, RETAINDAYS = 5,
> NAME = N'TheDb backup',
> NOSKIP,
> STATS = 10,
> NOFORMAT
> DECLARE @.i INT
> select @.i = position
> from msdb..backupset
> where database_name='TheDb'
> and type!='F'
> and backup_set_id=(select max(backup_set_id)
> from msdb..backupset
> where database_name='TheDb')
> RESTORE VERIFYONLY
> FROM DISK = @.strFileNamePath
> WITH FILE = @.i
> Here is my script for my differential backup:
> declare
> @.strFileNamePath varchar(100)
> set @.strFileNamePath = '\\BackupServer\SQL Backups\TheDbDaily' +
> convert(varchar, getdate(),112) + cast(datepart(hh,getdate())as varchar) +
> '.bak'
> BACKUP DATABASE [TheDb]
> TO DISK = @.strFileNamePath
> WITH NOINIT, NOUNLOAD, DIFFERENTIAL, RETAINDAYS = 5,
> NAME = N'TheDb backup',
> NOSKIP ,
> STATS = 10,
> NOFORMAT
> DECLARE @.i INT
> select @.i = position
> from msdb..backupset
> where database_name='TheDb'and type!='F'
> and backup_set_id=(select max(backup_set_id)
> from msdb..backupset
> where database_name='TheDb')
> RESTORE VERIFYONLY FROM DISK = @.strFileNamePath
> WITH FILE = @.i
> The full (a db maintenance plan job):
> EXECUTE master.dbo.xp_sqlmaint N'-PlanID
> 65514236-64B7-493A-841A-88F695626EB1 -Rpt "\\BackupServer\SQL Backups\TheDb
> DB Maintenance Plan14.txt" -DelTxtRpt 5DAYS -WriteHistory -VrfyBackup
> -BkUpOnlyIfClean -CkDB -BkUpMedia DISK -BkUpDB "\\BackupServer\SQL
> Backups\TheDb" -DelBkUps 5DAYS -BkExt "BAK"'
> As I said all these have been working flawlessly for over a year...
> Thanks in advance.
>
|||I don't understand it either...
running RESTORED HEADERONLY tells me that it is in fact a differential
backup with a backuptype of 5 in the result set...
I am ascertaining that it is the entire database based on the physical size
of the backup, its just too big to be the differential...
Am I missing somthing obvious?
The scripts in my previous post have been working as is for over a year, now
this headache...
Any suggestions would be most welcome.
Thanks in advance.
|||and the differential backup size is almost exactly the same size as the full
backups
|||Perhaps you did some mass-modification of data? Like rebuilding indexes...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"LvBohemian" <LvBohemian@.discussions.microsoft.com> wrote in message
news:15B2AB34-D6B7-4E87-9828-A7ACABD731F7@.microsoft.com...
>I don't understand it either...
> running RESTORED HEADERONLY tells me that it is in fact a differential
> backup with a backuptype of 5 in the result set...
> I am ascertaining that it is the entire database based on the physical size
> of the backup, its just too big to be the differential...
> Am I missing somthing obvious?
> The scripts in my previous post have been working as is for over a year, now
> this headache...
> Any suggestions would be most welcome.
> Thanks in advance.
|||Indexes are rebuilt as needed by another job, that also has not changed in
over a year...
How would some mass-modification of data like rebuilding an index or other?
cause something like this?
I'd really like to figure out the cause and solution...
Thanks in advance.
RE:
"Tibor Karaszi" wrote:
Perhaps you did some mass-modification of data? Like rebuilding indexes...
|||A diff backup contains all pages that has been modified since the last database backup. If you
rebuild an index, then all pages that the index uses are modified (both pages for the old index and
for the new index).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"LvBohemian" <LvBohemian@.discussions.microsoft.com> wrote in message
news:7E5552A0-77C5-40B0-9262-7B01E8646B8D@.microsoft.com...
> Indexes are rebuilt as needed by another job, that also has not changed in
> over a year...
> How would some mass-modification of data like rebuilding an index or other?
> cause something like this?
> I'd really like to figure out the cause and solution...
> Thanks in advance.
> RE:
> "Tibor Karaszi" wrote:
> Perhaps you did some mass-modification of data? Like rebuilding indexes...
>