[lnkForumImage]
TotalShareware - Download Free Software
Confronta i prezzi di migliaia di prodotti.
Asp Forum
 Home | Login | Register | Search 


 

Forums >

microsoft.public.sqlserver.programming

Differential backup problem

unknown

3/7/2007 2:10:00 PM

SQL Server 2005 express.

To automate the backup process, I am using SQLCMD along with windows
schedulers to do log/differential/full backup. At 12:00 AM, I do full
backup, differential backup at every 4 hours (excluding 12:00 am when I do
full backup), and log backup every 30 minutes.

Everything works fine (both full and log backup) but the problem is that
when differential backup happens (say 12:00 pm), I am getting the error

Msg 3035, Level 16, State 1, Line 1
Cannot perform a differential backup for database "testdatabase", because a
current database backup does not exist. Perform a full database backup by
reissuing BACKUP DATABASE, omitting the WITH DIFFERENTIAL option.
Msg 3013, Level 16, State 1, Line 1
BACKUP DATABASE is terminating abnormally

I think the database is thinking that I am doing the differential backup
without doing the full backup first. However, full backup was succeeded at
12:00 am.....
If I manually do the full backup, say 2:00 pm, then the scheduled
differential backup succeeds at 4:00 pm, 8:00 pm, but fails 4:00 am onward.

What is the problem? Any help would be appreciated.
Thanks
7 Answers

Alejandro Mesa

3/7/2007 2:42:00 PM

0

JJ,

Can you post the scripts being used?


Thanks,

AMB

"JJ" wrote:

> SQL Server 2005 express.
>
> To automate the backup process, I am using SQLCMD along with windows
> schedulers to do log/differential/full backup. At 12:00 AM, I do full
> backup, differential backup at every 4 hours (excluding 12:00 am when I do
> full backup), and log backup every 30 minutes.
>
> Everything works fine (both full and log backup) but the problem is that
> when differential backup happens (say 12:00 pm), I am getting the error
>
> Msg 3035, Level 16, State 1, Line 1
> Cannot perform a differential backup for database "testdatabase", because a
> current database backup does not exist. Perform a full database backup by
> reissuing BACKUP DATABASE, omitting the WITH DIFFERENTIAL option.
> Msg 3013, Level 16, State 1, Line 1
> BACKUP DATABASE is terminating abnormally
>
> I think the database is thinking that I am doing the differential backup
> without doing the full backup first. However, full backup was succeeded at
> 12:00 am.....
> If I manually do the full backup, say 2:00 pm, then the scheduled
> differential backup succeeds at 4:00 pm, 8:00 pm, but fails 4:00 am onward.
>
> What is the problem? Any help would be appreciated.
> Thanks
>
>
>
>

Uri Dimant

3/7/2007 2:45:00 PM

0

JJ
What if you add a second step to the job which does FULL backup database to
do DIFFERENTIAL database backup.





"JJ" <nospam@nospam.com> wrote in message
news:OTeRCJMYHHA.5044@TK2MSFTNGP05.phx.gbl...
> SQL Server 2005 express.
>
> To automate the backup process, I am using SQLCMD along with windows
> schedulers to do log/differential/full backup. At 12:00 AM, I do full
> backup, differential backup at every 4 hours (excluding 12:00 am when I do
> full backup), and log backup every 30 minutes.
>
> Everything works fine (both full and log backup) but the problem is that
> when differential backup happens (say 12:00 pm), I am getting the error
>
> Msg 3035, Level 16, State 1, Line 1
> Cannot perform a differential backup for database "testdatabase", because
> a
> current database backup does not exist. Perform a full database backup by
> reissuing BACKUP DATABASE, omitting the WITH DIFFERENTIAL option.
> Msg 3013, Level 16, State 1, Line 1
> BACKUP DATABASE is terminating abnormally
>
> I think the database is thinking that I am doing the differential backup
> without doing the full backup first. However, full backup was succeeded
> at
> 12:00 am.....
> If I manually do the full backup, say 2:00 pm, then the scheduled
> differential backup succeeds at 4:00 pm, 8:00 pm, but fails 4:00 am
> onward.
>
> What is the problem? Any help would be appreciated.
> Thanks
>
>
>


unknown

3/7/2007 2:57:00 PM

0

SQLCMD uses the following script

USE BackupDatabase

DECLARE @Hour INT
DECLARE @Minute INT
DECLARE @Date SMALLDATETIME
SET @Date = GETDATE()
SET @Hour = DATEPART (HOUR , @Date)
SET @Minute = DATEPART (MINUTE , @Date)

-- We need to either do FULL or DIFFERENTIAL Backups
IF (@Hour % 4) = 0 AND @Minute = 0
BEGIN
-- IF 12:00 AM, then we do full backup
IF @Hour = 0
BEGIN
-- FULL BACKUP
EXEC dbo.usp_BackupDatabase 'test', 1
EXEC dbo.usp_BackupDatabase 'test2', 1
EXEC dbo.usp_BackupDatabase 'master', 1
EXEC dbo.usp_BackupDatabase 'BackupDatabase', 1
END
ELSE
BEGIN
-- DIFFERENTIAL BACKUP
EXEC dbo.usp_BackupDatabase 'test', 2
EXEC dbo.usp_BackupDatabase 'test2', 2
END
END
ELSE
BEGIN
-- LOG BACKUP
EXEC dbo.usp_BackupDatabase 'test, 3
EXEC dbo.usp_BackupDatabase 'test2', 3
END


and the stored procedures

CREATE PROCEDURE [dbo].[usp_BackupDatabase]
(

@DatabaseName VARCHAR(50),

@BackupMode TINYINT -- 1 FULL, 2 Differential 3 Log

)

AS

BEGIN

SET NOCOUNT ON;

DECLARE @BackupCommand AS NVARCHAR(1000)

DECLARE @FileName AS NVARCHAR(100)

IF @BackupMode = 1

BEGIN

SET @FileName = '''C:\SQL2005Backups\'+ @DatabaseName + '\Full'+
LEFT(REPLACE(CONVERT(VARCHAR(25), GETDATE(),108), ':', ''), 4) + '.BAK'''

SET @BackupCommand = 'BACKUP DATABASE ' + @DatabaseName + ' TO DISK = ' +
@FileName + ' WITH INIT, NOSKIP'

END

IF @BackupMode = 2

BEGIN

SET @FileName = '''C:\SQL2005Backups\'+ @DatabaseName + '\Differential'+
LEFT(REPLACE(CONVERT(VARCHAR(25), GETDATE(),108), ':', ''), 4) + '.BAK'''

SET @BackupCommand = 'BACKUP DATABASE ' + @DatabaseName + ' TO DISK = ' +
@FileName + ' WITH DIFFERENTIAL, INIT, NOSKIP'

END

IF @BackupMode = 3

BEGIN

SET @FileName = '''C:\SQL2005Backups\'+ @DatabaseName + '\Log'+
LEFT(REPLACE(CONVERT(VARCHAR(25), GETDATE(),108), ':', ''), 4) + '.BAK'''

SET @BackupCommand = 'BACKUP LOG ' + @DatabaseName + ' TO DISK = ' +
@FileName + ' WITH INIT, NOSKIP'

END


--PRINT @BackupCommand

EXECUTE sp_executesql @BackupCommand

END

unknown

3/7/2007 4:45:00 PM

0

If that is the case, then what is the point of doing the differential
backup?
Thanks


"Uri Dimant" <urid@iscar.co.il> wrote in message
news:Oh64WdMYHHA.688@TK2MSFTNGP03.phx.gbl...
> JJ
> What if you add a second step to the job which does FULL backup database
> to do DIFFERENTIAL database backup.
>
>
>
>
>
> "JJ" <nospam@nospam.com> wrote in message
> news:OTeRCJMYHHA.5044@TK2MSFTNGP05.phx.gbl...
>> SQL Server 2005 express.
>>
>> To automate the backup process, I am using SQLCMD along with windows
>> schedulers to do log/differential/full backup. At 12:00 AM, I do full
>> backup, differential backup at every 4 hours (excluding 12:00 am when I
>> do
>> full backup), and log backup every 30 minutes.
>>
>> Everything works fine (both full and log backup) but the problem is that
>> when differential backup happens (say 12:00 pm), I am getting the error
>>
>> Msg 3035, Level 16, State 1, Line 1
>> Cannot perform a differential backup for database "testdatabase", because
>> a
>> current database backup does not exist. Perform a full database backup by
>> reissuing BACKUP DATABASE, omitting the WITH DIFFERENTIAL option.
>> Msg 3013, Level 16, State 1, Line 1
>> BACKUP DATABASE is terminating abnormally
>>
>> I think the database is thinking that I am doing the differential backup
>> without doing the full backup first. However, full backup was succeeded
>> at
>> 12:00 am.....
>> If I manually do the full backup, say 2:00 pm, then the scheduled
>> differential backup succeeds at 4:00 pm, 8:00 pm, but fails 4:00 am
>> onward.
>>
>> What is the problem? Any help would be appreciated.
>> Thanks
>>
>>
>>
>
>


Alejandro Mesa

3/7/2007 5:34:00 PM

0

JJ,

Be sure that the full backup is being created. You are casting datetime data
type to smalldatetime when assigning GETDATE() to your smalldatetime
variable. If that assignment occurs beyond '??:00:29.998', @Minute will be 1
and you can miss the full or the differential backup.

declare @Date smalldatetime

set @Date = '2007-03-07T00:00:29.999'

select
DATEPART (HOUR , @Date) as [Hour],
DATEPART (MINUTE , @Date) as [Minute]
go


AMB


"Alejandro Mesa" wrote:

> JJ,
>
> So, based in this script, at 12:00 pm the differential backup for databases
> [test] and [test2] would fail, correct?
>
> When you got the error, did you check the existence of the full backupset
> using "restore headeronly" on the related backup file?
>
>
> AMB
>
> "JJ" wrote:
>
> > SQLCMD uses the following script
> >
> > USE BackupDatabase
> >
> > DECLARE @Hour INT
> > DECLARE @Minute INT
> > DECLARE @Date SMALLDATETIME
> > SET @Date = GETDATE()
> > SET @Hour = DATEPART (HOUR , @Date)
> > SET @Minute = DATEPART (MINUTE , @Date)
> >
> > -- We need to either do FULL or DIFFERENTIAL Backups
> > IF (@Hour % 4) = 0 AND @Minute = 0
> > BEGIN
> > -- IF 12:00 AM, then we do full backup
> > IF @Hour = 0
> > BEGIN
> > -- FULL BACKUP
> > EXEC dbo.usp_BackupDatabase 'test', 1
> > EXEC dbo.usp_BackupDatabase 'test2', 1
> > EXEC dbo.usp_BackupDatabase 'master', 1
> > EXEC dbo.usp_BackupDatabase 'BackupDatabase', 1
> > END
> > ELSE
> > BEGIN
> > -- DIFFERENTIAL BACKUP
> > EXEC dbo.usp_BackupDatabase 'test', 2
> > EXEC dbo.usp_BackupDatabase 'test2', 2
> > END
> > END
> > ELSE
> > BEGIN
> > -- LOG BACKUP
> > EXEC dbo.usp_BackupDatabase 'test, 3
> > EXEC dbo.usp_BackupDatabase 'test2', 3
> > END
> >
> >
> > and the stored procedures
> >
> > CREATE PROCEDURE [dbo].[usp_BackupDatabase]
> > (
> >
> > @DatabaseName VARCHAR(50),
> >
> > @BackupMode TINYINT -- 1 FULL, 2 Differential 3 Log
> >
> > )
> >
> > AS
> >
> > BEGIN
> >
> > SET NOCOUNT ON;
> >
> > DECLARE @BackupCommand AS NVARCHAR(1000)
> >
> > DECLARE @FileName AS NVARCHAR(100)
> >
> > IF @BackupMode = 1
> >
> > BEGIN
> >
> > SET @FileName = '''C:\SQL2005Backups\'+ @DatabaseName + '\Full'+
> > LEFT(REPLACE(CONVERT(VARCHAR(25), GETDATE(),108), ':', ''), 4) + '.BAK'''
> >
> > SET @BackupCommand = 'BACKUP DATABASE ' + @DatabaseName + ' TO DISK = ' +
> > @FileName + ' WITH INIT, NOSKIP'
> >
> > END
> >
> > IF @BackupMode = 2
> >
> > BEGIN
> >
> > SET @FileName = '''C:\SQL2005Backups\'+ @DatabaseName + '\Differential'+
> > LEFT(REPLACE(CONVERT(VARCHAR(25), GETDATE(),108), ':', ''), 4) + '.BAK'''
> >
> > SET @BackupCommand = 'BACKUP DATABASE ' + @DatabaseName + ' TO DISK = ' +
> > @FileName + ' WITH DIFFERENTIAL, INIT, NOSKIP'
> >
> > END
> >
> > IF @BackupMode = 3
> >
> > BEGIN
> >
> > SET @FileName = '''C:\SQL2005Backups\'+ @DatabaseName + '\Log'+
> > LEFT(REPLACE(CONVERT(VARCHAR(25), GETDATE(),108), ':', ''), 4) + '.BAK'''
> >
> > SET @BackupCommand = 'BACKUP LOG ' + @DatabaseName + ' TO DISK = ' +
> > @FileName + ' WITH INIT, NOSKIP'
> >
> > END
> >
> >
> > --PRINT @BackupCommand
> >
> > EXECUTE sp_executesql @BackupCommand
> >
> > END
> >
> >
> >

Alejandro Mesa

3/7/2007 5:34:00 PM

0

JJ,

So, based in this script, at 12:00 pm the differential backup for databases
[test] and [test2] would fail, correct?

When you got the error, did you check the existence of the full backupset
using "restore headeronly" on the related backup file?


AMB

"JJ" wrote:

> SQLCMD uses the following script
>
> USE BackupDatabase
>
> DECLARE @Hour INT
> DECLARE @Minute INT
> DECLARE @Date SMALLDATETIME
> SET @Date = GETDATE()
> SET @Hour = DATEPART (HOUR , @Date)
> SET @Minute = DATEPART (MINUTE , @Date)
>
> -- We need to either do FULL or DIFFERENTIAL Backups
> IF (@Hour % 4) = 0 AND @Minute = 0
> BEGIN
> -- IF 12:00 AM, then we do full backup
> IF @Hour = 0
> BEGIN
> -- FULL BACKUP
> EXEC dbo.usp_BackupDatabase 'test', 1
> EXEC dbo.usp_BackupDatabase 'test2', 1
> EXEC dbo.usp_BackupDatabase 'master', 1
> EXEC dbo.usp_BackupDatabase 'BackupDatabase', 1
> END
> ELSE
> BEGIN
> -- DIFFERENTIAL BACKUP
> EXEC dbo.usp_BackupDatabase 'test', 2
> EXEC dbo.usp_BackupDatabase 'test2', 2
> END
> END
> ELSE
> BEGIN
> -- LOG BACKUP
> EXEC dbo.usp_BackupDatabase 'test, 3
> EXEC dbo.usp_BackupDatabase 'test2', 3
> END
>
>
> and the stored procedures
>
> CREATE PROCEDURE [dbo].[usp_BackupDatabase]
> (
>
> @DatabaseName VARCHAR(50),
>
> @BackupMode TINYINT -- 1 FULL, 2 Differential 3 Log
>
> )
>
> AS
>
> BEGIN
>
> SET NOCOUNT ON;
>
> DECLARE @BackupCommand AS NVARCHAR(1000)
>
> DECLARE @FileName AS NVARCHAR(100)
>
> IF @BackupMode = 1
>
> BEGIN
>
> SET @FileName = '''C:\SQL2005Backups\'+ @DatabaseName + '\Full'+
> LEFT(REPLACE(CONVERT(VARCHAR(25), GETDATE(),108), ':', ''), 4) + '.BAK'''
>
> SET @BackupCommand = 'BACKUP DATABASE ' + @DatabaseName + ' TO DISK = ' +
> @FileName + ' WITH INIT, NOSKIP'
>
> END
>
> IF @BackupMode = 2
>
> BEGIN
>
> SET @FileName = '''C:\SQL2005Backups\'+ @DatabaseName + '\Differential'+
> LEFT(REPLACE(CONVERT(VARCHAR(25), GETDATE(),108), ':', ''), 4) + '.BAK'''
>
> SET @BackupCommand = 'BACKUP DATABASE ' + @DatabaseName + ' TO DISK = ' +
> @FileName + ' WITH DIFFERENTIAL, INIT, NOSKIP'
>
> END
>
> IF @BackupMode = 3
>
> BEGIN
>
> SET @FileName = '''C:\SQL2005Backups\'+ @DatabaseName + '\Log'+
> LEFT(REPLACE(CONVERT(VARCHAR(25), GETDATE(),108), ':', ''), 4) + '.BAK'''
>
> SET @BackupCommand = 'BACKUP LOG ' + @DatabaseName + ' TO DISK = ' +
> @FileName + ' WITH INIT, NOSKIP'
>
> END
>
>
> --PRINT @BackupCommand
>
> EXECUTE sp_executesql @BackupCommand
>
> END
>
>
>

Uri Dimant

3/8/2007 7:33:00 AM

0

JJ
http://vyaskn.tripod.com/sql_server_administration_best_practices...
--administaiting best practices





"JJ" <nospam@nospam.com> wrote in message
news:upuM5fNYHHA.4772@TK2MSFTNGP05.phx.gbl...
> If that is the case, then what is the point of doing the differential
> backup?
> Thanks
>
>
> "Uri Dimant" <urid@iscar.co.il> wrote in message
> news:Oh64WdMYHHA.688@TK2MSFTNGP03.phx.gbl...
>> JJ
>> What if you add a second step to the job which does FULL backup database
>> to do DIFFERENTIAL database backup.
>>
>>
>>
>>
>>
>> "JJ" <nospam@nospam.com> wrote in message
>> news:OTeRCJMYHHA.5044@TK2MSFTNGP05.phx.gbl...
>>> SQL Server 2005 express.
>>>
>>> To automate the backup process, I am using SQLCMD along with windows
>>> schedulers to do log/differential/full backup. At 12:00 AM, I do full
>>> backup, differential backup at every 4 hours (excluding 12:00 am when I
>>> do
>>> full backup), and log backup every 30 minutes.
>>>
>>> Everything works fine (both full and log backup) but the problem is that
>>> when differential backup happens (say 12:00 pm), I am getting the error
>>>
>>> Msg 3035, Level 16, State 1, Line 1
>>> Cannot perform a differential backup for database "testdatabase",
>>> because a
>>> current database backup does not exist. Perform a full database backup
>>> by
>>> reissuing BACKUP DATABASE, omitting the WITH DIFFERENTIAL option.
>>> Msg 3013, Level 16, State 1, Line 1
>>> BACKUP DATABASE is terminating abnormally
>>>
>>> I think the database is thinking that I am doing the differential backup
>>> without doing the full backup first. However, full backup was succeeded
>>> at
>>> 12:00 am.....
>>> If I manually do the full backup, say 2:00 pm, then the scheduled
>>> differential backup succeeds at 4:00 pm, 8:00 pm, but fails 4:00 am
>>> onward.
>>>
>>> What is the problem? Any help would be appreciated.
>>> Thanks
>>>
>>>
>>>
>>
>>
>
>