[SQL 2005] backup Diff on MSDB doesnt work

Hello all,

I need help concerning a differential backup on a MSDB database.

I received this message when I tried to backup it

Log from Windows

Event Type: Error
Event Source: SQLVDI
Event Category: None
Event ID: 1
Date: 8/1/2007
Time: 2:03:37 PM
User: N/A
Computer: XXX

Description:
SQLVDI: Loc=SignalAbort. Desc=Client initiates abort. ErrorCode=(0). Process=9028. Thread=5928. Client. Instance=. VD=Global\Data Protector_(DEFAULT)_msdb_14_00_21.

Log from DataProtector

Normal] From: XXX "(DEFAULT)" Time: XXX
SQL statement:
BACKUP DATABASE [msdb] TO
VIRTUAL_DEVICE = "Data Protector_(DEFAULT)_msdb_06_00_14"
WITH NAME = 'Data Protector: 2007/08/01 0064', DIFFERENTIAL, BLOCKSIZE = 4096, MAXTRANSFERSIZE = 65536;

[Warning] From: XXX "(DEFAULT)" Time: XXX
Error has occurred while executing a SQL statement.
Error message: '<Microsoft SQL-DMO (ODBC SQLState: 42000):bdb>
[Microsoft][ODBC SQL Server Driver][SQL Server]Cannot perform a differential backup for database "msdb", because a current database backup does not exist. Perform a full database backup by reissuing BACKUP DATABASE, omitting the WITH DIFFERENTIAL option.
[Microsoft][ODBC SQL Server Driver][SQL Server]BACKUP DATABASE is terminating abnormally.'

Ive tried to do a full followed straight after by a diff but doent help.

Thank you for your help

yes you can take differential but for master you can take only full backup ..........whats in the error log ? ? ? you need to take a full backup and then subsequently differential backup

|||

Yes we can, I do it for many servers

|||

The error is pretty explicit about what the problem is.

A differential backup is defined as the set of changes SINCE THE LAST FULL BACKUP.

If you've never done a full backup, there is nothing to base the differential on and it won't work.

Do a full backup of MSDB, and then your differentials should work.

|||

Hi thank you to replay.

That what I do, a full and after a diff, that why I don't understand why I doesnt find any base

Thank you

|||

Is the database recovery set to FULL? Simple won't do it (it actually reset the status field needed).

|||

FULL recovery model applies to log backups, not to differential backups.

In fact, there is no way to clear the differential base once it is set.

What you're attempting to do should work just fine.

Please try it from Management Studio query window and paste the commands and response.

Thanks,

Kevin

|||

Kevin Farlee wrote:

FULL recovery model applies to log backups, not to differential backups.

In fact, there is no way to clear the differential base once it is set.

Thanks for the correction. Somehow, I was thinking the OP wants to backup log for msdb (you can't 'cuz it's simple). Duh!

[SQL 2005] backup Diff on MSDB doesnt work

Hello all,

I need help concerning a differential backup on a MSDB database.

I received this message when I tried to backup it

Log from Windows

Event Type: Error
Event Source: SQLVDI
Event Category: None
Event ID: 1
Date: 8/1/2007
Time: 2:03:37 PM
User: N/A
Computer: XXX

Description:
SQLVDI: Loc=SignalAbort. Desc=Client initiates abort. ErrorCode=(0). Process=9028. Thread=5928. Client. Instance=. VD=Global\Data Protector_(DEFAULT)_msdb_14_00_21.

Log from DataProtector

Normal] From: XXX "(DEFAULT)" Time: XXX
SQL statement:
BACKUP DATABASE [msdb] TO
VIRTUAL_DEVICE = "Data Protector_(DEFAULT)_msdb_06_00_14"
WITH NAME = 'Data Protector: 2007/08/01 0064', DIFFERENTIAL, BLOCKSIZE = 4096, MAXTRANSFERSIZE = 65536;

[Warning] From: XXX "(DEFAULT)" Time: XXX
Error has occurred while executing a SQL statement.
Error message: '<Microsoft SQL-DMO (ODBC SQLState: 42000):bdb>
[Microsoft][ODBC SQL Server Driver][SQL Server]Cannot perform a differential backup for database "msdb", because a current database backup does not exist. Perform a full database backup by reissuing BACKUP DATABASE, omitting the WITH DIFFERENTIAL option.
[Microsoft][ODBC SQL Server Driver][SQL Server]BACKUP DATABASE is terminating abnormally.'

Ive tried to do a full followed straight after by a diff but doent help.

Thank you for your help

yes you can take differential but for master you can take only full backup ..........whats in the error log ? ? ? you need to take a full backup and then subsequently differential backup

|||

Yes we can, I do it for many servers

|||

The error is pretty explicit about what the problem is.

A differential backup is defined as the set of changes SINCE THE LAST FULL BACKUP.

If you've never done a full backup, there is nothing to base the differential on and it won't work.

Do a full backup of MSDB, and then your differentials should work.

|||

Hi thank you to replay.

That what I do, a full and after a diff, that why I don't understand why I doesnt find any base

Thank you

|||

Is the database recovery set to FULL? Simple won't do it (it actually reset the status field needed).

|||

FULL recovery model applies to log backups, not to differential backups.

In fact, there is no way to clear the differential base once it is set.

What you're attempting to do should work just fine.

Please try it from Management Studio query window and paste the commands and response.

Thanks,

Kevin

|||

Kevin Farlee wrote:

FULL recovery model applies to log backups, not to differential backups.

In fact, there is no way to clear the differential base once it is set.

Thanks for the correction. Somehow, I was thinking the OP wants to backup log for msdb (you can't 'cuz it's simple). Duh!

[help] Possible to script a diff into SQL Query?

My goal is to add a diff into a query that grabs data from 2 different tables.

The code:
SELECT
MIN(TableName) as TableName,
ID1, COL1, COL2, COL3, COL4, COL5, COL6, COL7, COL8
,COL9, COL10, COL11, COL12, COL13, COL14, COL15, --COL16,
COL17, COL18, COL19, COL20, COL21
FROM
(
SELECT 'Table A' as TableName,
SessionID as ID1,
StartDateCode as COL1,
StartTimeCode as COL2,
EndDateCode as COL3,
EndTimeCode as COL4,
HandledByCode as COL5,
DispositionCode as COL6,
DNISCode as COL7,
CallServiceQueueCode as COL8,
ApplicationCode as COL9,
IVREndPointCode as COL10,
BankCode as COL11,
TotalQueueTimeSS as COL12,
TotalAgentTalkTimeSS as COL13,
TotalAgentHoldTimeSS as COL14,
TotalAgentHandleTimeSS as COL15,
--TotalIVRTimeSS as COL16,
AfterHoursFlag as COL17,
SourceSystemID as COL18,
anubisTransferExtNumber as COL19,
anubisEndPoint as COL20,
AccountNumber as COL21

from [pdx0sql45].Rubicon_Marts.dbo.INB_Call_Fact
where startdatecode between 2738 and 2769

UNION all

SELECT 'Table B' as TableName,
SessionID as ID1,
StartDateCode as COL1,
StartTimeCode as COL2,
EndDateCode as COL3,
EndTimeCode as COL4,
HandledByCode as COL5,
DispositionCode as COL6,
DNISCode as COL7,
CallServiceQueueCode as COL8,
ApplicationCode as COL9,
IVREndPointCode as COL10,
BankCode as COL11,
TotalQueueTimeSS as COL12,
TotalAgentTalkTimeSS as COL13,
TotalAgentHoldTimeSS as COL14,
TotalAgentHandleTimeSS as COL15,
--TotalIVRTimeSS as COL16,
AfterHoursFlag as COL17,
SourceSystemID as COL18,
anubisTransferExtNumber as COL19,
anubisEndPoint as COL20,
AccountNumber as COL21

from pdx0sql04.Rubicon_Marts.dbo.INB_Call_Fact
where startdatecode between 2738 and 2769
) tmp

GROUP BY ID1, COL1, COL2, COL3, COL4, COL5, COL6, COL7, COL8
,COL9, COL10, COL11, COL12, COL13, COL14, COL15, --COL16,
COL17, COL18, COL19, COL20, COL21
HAVING COUNT(*) = 1
ORDER BY 2,1

Is it possible to add a command into the query to output diff/compare scenario?

Thanks in advance for any help.

If you are using SQL Server 2005 you might be able to use the EXCEPT operator instead of the UNION ALL operator. Give a look to the EXCEPT operator in books online.

If the EXCEPT operator looks like it does what you want and it does not perform to your satisfaction, come back and talk to us again. While the EXCEPT operator might be conceptually the most straight forward approach it is not always the most efficient.

Kent

|||Yes, we're using 2005. I'll look into using the EXCEPT op. thanks for the advise, Kent.

@@SERVERNAME sql 2k

Hi,
Diff server name for same sql box.
Select @.@.SERVERNAME
and select * from sysservers
WHy?
@.@.SERVERNAME returns incorrect name. How do I correct it?
THnksHi
At some point you probably renamed the server at some point, run
sp_dropserver and sp_addserver as shown in:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/instsql/in_afterinstall_5r8f.asp
John
"mecn" wrote:
> Hi,
> Diff server name for same sql box.
> Select @.@.SERVERNAME
> and select * from sysservers
> WHy?
> @.@.SERVERNAME returns incorrect name. How do I correct it?
> THnks
>
>

@@SERVERNAME sql 2k

Hi,
Diff server name for same sql box.
Select @.@.SERVERNAME
and select * from sysservers
WHy?
@.@.SERVERNAME returns incorrect name. How do I correct it?
THnksHi
At some point you probably renamed the server at some point, run
sp_dropserver and sp_addserver as shown in:
http://msdn.microsoft.com/library/d...nstall_5r8f.asp
John
"mecn" wrote:

> Hi,
> Diff server name for same sql box.
> Select @.@.SERVERNAME
> and select * from sysservers
> WHy?
> @.@.SERVERNAME returns incorrect name. How do I correct it?
> THnks
>
>