Is NTBACKUP breaking SQL Server 2005 differential backup chain..again?

Hi All,
We recently upgraded a box running Windows Server 2003 SP1 with SQL Server
2005 SP1 to Windows Server 2003 SP2 and SQL Server SP2.
For some time we have had the following backup regime for some SQL Server
full recovery model databases that are present on this server:
1) SQL Server Full backup on a Sunday night at around midnight
2) SQL Server Transaction log backups every three hours from 03:00
until 18:00
3) SQL Server Differential backups at 21:00 every day except Sunday
night.
4) NTBACKUP at 22:00 every night to do a differential backup of the
server using option /SNAP:on (Use Volume Shadow Copy) but with the directory
containing SQL Server database files excluded.
5) Note the output TRN files and BAK files from points 1 to 4 above are
mirrored off to another server by a 3rd party application, not by using
NTBACKUP.
All has worked fine for many months until our upgrade of the server to SP2
of SQL Server and SP2 of Windows Server 2003.
Immediately after that upgrade we would get the Full backup at point 1
completing as planned, the transaction log backups at point 2 complete on
the Monday, then the differential SQL backup on Monday night works fine as
well followed by the NT Backup at point 4. The cycle works fine on the
Tuesday up to the repeat of the SQL Server differential backup at point 3
which fails for every database with an error message not unlike this
example:
[QUOTE]
Executing the query "BACKUP DATABASE [dbTPL] TO DISK =
N'D:\\NoBackup\\SQLBackup\\UserDBs\\Diff
\\dbTPL\\dbTPL_backup_200703272000.b
ak'
WITH DIFFERENTIAL , NOFORMAT, NOINIT, NAME =
N'dbTPL_backup_20070327200004', SKIP, REWIND, NOUNLOAD, STATS = 10" failed
with the following error: "Cannot perform a differential backup for database
"dbTPL", because a current database backup does not exist. Perform a full
database backup by reissuing BACKUP DATABASE, omitting the WITH DIFFERENTIAL
option. BACKUP DATABASE is terminating abnormally.". Possible failure
reasons: Problems with the query, "ResultSet" property not set correctly,
parameters not set correctly, or connection not established correctly.
[END QUOTE]
As if NTBackup has broken the link to the original full backup.
There used to be a problem with the Volume Shadow Copy Service whereby
NTBACKUP would not manage a backup of SQL Server Full recovery model
databases. As in this article here: http://support.microsoft.com/kb/903643
I noticed in the event log since our upgrade to SP2 of Windows Server 2003
and SQL Server 2005 we get errors around the time NTBACKUP runs that are
like this one:
[QUOTE]
Event Type: Error
Event Source: SQLVDI
Event Category: None
Event ID: 1
Date: 29/03/2007
Time: 07:03:47
User: N/A
Computer: SERVERA
Description:
SQLVDI: Loc=CVDS::Cleanup. Desc=Release(ClientAliveMutex).
ErrorCode=(288)Attempt to release mutex not owned by caller.. Process=1968.
Thread=3564. Client. Instance=. VD=.
For more information, see Help and Support Center at
http://go.microsoft.com/fwlink/events.asp.
[END QUOTE]
The SQL Server maintenance plans at points 1 through to 3 above are not hand
crafted scripts, just output from the Maintenance plan wizard. Furthermore
they have been deleted and recreated since updating to SP2 in case that was
the cause but the problem remains.
The only way around this problem has been to amend the NTBACKUP command at
point 4 above and use /SNAP:off so that it doesn't use the Volume Shadow
Copy service. I can only draw the conclusion that the Shadow Copy Service or
NTBackup has something to do with this? Has anybody else encountered a
similar problem?
I have been researching this for days but due to the relative brief period
since the release of Service Pack 2 for Windows Server 2003 there isn't
really too much about on the web regarding this. In fact I hadn't even
though of it being the NTBACKUP if it hadn't been for the thread I found
here: http://forums.microsoft.com/MSDN/Sh...293496&SiteID=1
Any tips / ideas much appreciated.
Kind RegardsCan you configure NTBACKUP to not grab the SQL Server database files? Just t
hinking out loud...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"NGD66" <ngd66no-spam@.hotmailno-spam.co.uk> wrote in message
news:uR0BrQecHHA.1312@.TK2MSFTNGP06.phx.gbl...
> Hi All,
>
> We recently upgraded a box running Windows Server 2003 SP1 with SQL Server
2005 SP1 to Windows
> Server 2003 SP2 and SQL Server SP2.
>
> For some time we have had the following backup regime for some SQL Server
full recovery model
> databases that are present on this server:
>
> 1) SQL Server Full backup on a Sunday night at around midnight
> 2) SQL Server Transaction log backups every three hours from 03:00 un
til 18:00
> 3) SQL Server Differential backups at 21:00 every day except Sunday n
ight.
> 4) NTBACKUP at 22:00 every night to do a differential backup of the s
erver using option
> /SNAP:on (Use Volume Shadow Copy) but with the directory containing SQL Se
rver database files
> excluded.
> 5) Note the output TRN files and BAK files from points 1 to 4 above a
re mirrored off to
> another server by a 3rd party application, not by using NTBACKUP.
>
> All has worked fine for many months until our upgrade of the server to SP2
of SQL Server and SP2
> of Windows Server 2003.
>
> Immediately after that upgrade we would get the Full backup at point 1 com
pleting as planned, the
> transaction log backups at point 2 complete on the Monday, then the differ
ential SQL backup on
> Monday night works fine as well followed by the NT Backup at point 4. The
cycle works fine on the
> Tuesday up to the repeat of the SQL Server differential backup at point 3
which fails for every
> database with an error message not unlike this example:
>
> [QUOTE]
> Executing the query "BACKUP DATABASE [dbTPL] TO DISK =
> N'D:\\NoBackup\\SQLBackup\\UserDBs\\Diff
\\dbTPL\\dbTPL_backup_200703272000
.bak' WITH DIFFERENTIAL
> , NOFORMAT, NOINIT, NAME = N'dbTPL_backup_20070327200004', SKIP, REWIND,
NOUNLOAD, STATS = 10"
> failed with the following error: "Cannot perform a differential backup for
database "dbTPL",
> because a current database backup does not exist. Perform a full database
backup by reissuing
> BACKUP DATABASE, omitting the WITH DIFFERENTIAL option. BACKUP DATABASE is
terminating
> abnormally.". Possible failure reasons: Problems with the query, "ResultSe
t" property not set
> correctly, parameters not set correctly, or connection not established cor
rectly.
> [END QUOTE]
>
> As if NTBackup has broken the link to the original full backup.
>
> There used to be a problem with the Volume Shadow Copy Service whereby NTB
ACKUP would not manage a
> backup of SQL Server Full recovery model databases. As in this article her
e:
> http://support.microsoft.com/kb/903643
>
> I noticed in the event log since our upgrade to SP2 of Windows Server 2003
and SQL Server 2005 we
> get errors around the time NTBACKUP runs that are like this one:
>
> [QUOTE]
> Event Type: Error
> Event Source: SQLVDI
> Event Category: None
> Event ID: 1
> Date: 29/03/2007
> Time: 07:03:47
> User: N/A
> Computer: SERVERA
> Description:
> SQLVDI: Loc=CVDS::Cleanup. Desc=Release(ClientAliveMutex). ErrorCode=(288)
Attempt to release mutex
> not owned by caller.. Process=1968. Thread=3564. Client. Instance=. VD=.
>
> For more information, see Help and Support Center at http://go.microsoft.com/fwl
ink/events.asp.
> [END QUOTE]
>
> The SQL Server maintenance plans at points 1 through to 3 above are not ha
nd crafted scripts, just
> output from the Maintenance plan wizard. Furthermore they have been delete
d and recreated since
> updating to SP2 in case that was the cause but the problem remains.
>
> The only way around this problem has been to amend the NTBACKUP command at
point 4 above and use
> /SNAP:off so that it doesn't use the Volume Shadow Copy service. I can onl
y draw the conclusion
> that the Shadow Copy Service or NTBackup has something to do with this? Ha
s anybody else
> encountered a similar problem?
>
> I have been researching this for days but due to the relative brief period
since the release of
> Service Pack 2 for Windows Server 2003 there isn't really too much about o
n the web regarding
> this. In fact I hadn't even though of it being the NTBACKUP if it hadn't b
een for the thread I
> found here: http://forums.microsoft.com/MSDN/Sh...iteID
=1
>
> Any tips / ideas much appreciated.
>
> Kind Regards
>
>
>|||Hi Tibor,
Some of the databases are quite large and backing them up fully using
NTBACKUP with snap:on option is not really an option. The idea is to leave
the SQL databases' backups outside of the control of NTBACKUP. This used to
work though until application of the Service Packs and now NTBACKUP seems to
be breaking that Differential chain. Although I hasten to add, that is the
only possible cause I have been able to identify thus far however I accept
there could be other causes. Luckily this server doesn't have a basket load
of other applications / services that could have any indirect effect on this
problem.
Kind Regards
Nick
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:%239h7jwecHHA.4352@.TK2MSFTNGP03.phx.gbl...
> Can you configure NTBACKUP to not grab the SQL Server database files? Just
> thinking out loud...
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "NGD66" <ngd66no-spam@.hotmailno-spam.co.uk> wrote in message
> news:uR0BrQecHHA.1312@.TK2MSFTNGP06.phx.gbl...
>|||I see. SQL Server get confused even if NTBACKUP excludes the database files.
Can you configure
NTBACKUP to not interface with SQL Server? It seems from the eventlog entry
you posted that SQL
Server got some VDI call from NTBACKUP. This is probably the one that broke
the backup chain.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"NGD66" <ngd66no-spam@.hotmailno-spam.co.uk> wrote in message
news:ec$Hn8ecHHA.3484@.TK2MSFTNGP04.phx.gbl...
> Hi Tibor,
> Some of the databases are quite large and backing them up fully using NTBA
CKUP with snap:on option
> is not really an option. The idea is to leave the SQL databases' backups o
utside of the control of
> NTBACKUP. This used to work though until application of the Service Packs
and now NTBACKUP seems
> to be breaking that Differential chain. Although I hasten to add, that is
the only possible cause
> I have been able to identify thus far however I accept there could be othe
r causes. Luckily this
> server doesn't have a basket load of other applications / services that co
uld have any indirect
> effect on this problem.
> Kind Regards
> Nick
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote i
n message
> news:%239h7jwecHHA.4352@.TK2MSFTNGP03.phx.gbl...
>|||Hi Tibor,
Unfortunately now you cannot. The only way to prevent this is to turn off
the command line SNAP option of NTBACKUP with /snap:off however this defeats
the object since there are other files (Non-SQL Server) on the drive that I
do want to have backed up even though they maybe in use.
Regards
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:Od23CWhcHHA.2316@.TK2MSFTNGP04.phx.gbl...
>I see. SQL Server get confused even if NTBACKUP excludes the database
>files. Can you configure NTBACKUP to not interface with SQL Server? It
>seems from the eventlog entry you posted that SQL Server got some VDI call
>from NTBACKUP. This is probably the one that broke the backup chain.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "NGD66" <ngd66no-spam@.hotmailno-spam.co.uk> wrote in message
> news:ec$Hn8ecHHA.3484@.TK2MSFTNGP04.phx.gbl...
>|||> Unfortunately now you cannot.
That sounds horrible to me, to be honest. I'd talk to the guys who wrote thi
s software...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"NGD66" <ngd66no-spam@.hotmailno-spam.co.uk> wrote in message
news:eP70LihcHHA.1244@.TK2MSFTNGP04.phx.gbl...
> Hi Tibor,
> Unfortunately now you cannot. The only way to prevent this is to turn off
the command line SNAP
> option of NTBACKUP with /snap:off however this defeats the object since th
ere are other files
> (Non-SQL Server) on the drive that I do want to have backed up even though
they maybe in use.
> Regards
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote i
n message
> news:Od23CWhcHHA.2316@.TK2MSFTNGP04.phx.gbl...
>|||NTBACKUP is Microsoft. It's the default backup software in the operating
system. I don't think talking to them is going to get me anywhere.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:uXTpylhcHHA.2068@.TK2MSFTNGP06.phx.gbl...
> That sounds horrible to me, to be honest. I'd talk to the guys who wrote
> this software...
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "NGD66" <ngd66no-spam@.hotmailno-spam.co.uk> wrote in message
> news:eP70LihcHHA.1244@.TK2MSFTNGP04.phx.gbl...
>|||> NTBACKUP is Microsoft.
I know. I was just suggestion that this should be configurable in this app.
Perhaps ask in a Windows
forum? To the best of my knowledge, there's no way to say to SQL Server to i
gnore this backup VDI
message.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"NGD66" <ngd66no-spam@.hotmailno-spam.co.uk> wrote in message
news:OOVKXyhcHHA.984@.TK2MSFTNGP04.phx.gbl...
> NTBACKUP is Microsoft. It's the default backup software in the operating s
ystem. I don't think
> talking to them is going to get me anywhere.
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote i
n message
> news:uXTpylhcHHA.2068@.TK2MSFTNGP06.phx.gbl...
>|||Come to think of it, perhaps stopping the SQL Server VSS service... But I do
n't know what side
effects that would have. I'd check with MS first.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"NGD66" <ngd66no-spam@.hotmailno-spam.co.uk> wrote in message
news:OOVKXyhcHHA.984@.TK2MSFTNGP04.phx.gbl...
> NTBACKUP is Microsoft. It's the default backup software in the operating s
ystem. I don't think
> talking to them is going to get me anywhere.
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote i
n message
> news:uXTpylhcHHA.2068@.TK2MSFTNGP06.phx.gbl...
>|||Btw again. I found some words on this in BOL 2005, searching for VSS, like:
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/0f299867-f499-4c2a-ad6f-b2ef
1869381d.htm
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"NGD66" <ngd66no-spam@.hotmailno-spam.co.uk> wrote in message
news:OOVKXyhcHHA.984@.TK2MSFTNGP04.phx.gbl...
> NTBACKUP is Microsoft. It's the default backup software in the operating s
ystem. I don't think
> talking to them is going to get me anywhere.
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote i
n message
> news:uXTpylhcHHA.2068@.TK2MSFTNGP06.phx.gbl...
>