Cant append multiple Diff backups to same file?..

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

SQL Server Administration (2005)

Cant append multiple Diff backups to same file?..

Author

Topic

methodology
Starting Member

31 Posts

Posted - 2007-08-09 : 04:25:58

I run two different types of backups on the same database.

A monthly full and nightly diffs appending to the same full file - file 'A'

A weekly full with 10 min trans log backups appending to same file - file 'B'- during working hours.

2 strategies, 2 backup files.

These are new strats that have gone live this week - tuesday in fact. The monthly and weekly both ran fine on tuesday as did the nightly diff and all the TS backups througout the day. last night - Weds - the nightly diff failed with the following error:

"

Executed as user: <USERNAME>. Cannot perform a differential backup for database "objectstore", because a current database backup does not exist. Perform a full database backup by reissuing BACKUP DATABASE, omitting the WITH DIFFERENTIAL option. [SQLSTATE 42000] (Error 3035) BACKUP DATABASE is terminating abnormally. [SQLSTATE 42000] (Error 3013). The step failed.

"

Whilst the error is perfectly legible I dont understand what its implying - cant I add multiple Diff backups to the same full backup OR is my weekly / 10 mins on the same database (but to a diff backup file) ballsing things up for me somehow? If this is the case how come everything ran fine on tuesday night?

Any help gets beers.

THanks

Alastair Jones.

"A computer once beat me at chess - but it was no match for me at kick boxing" - Emo Phillips.

nr
SQLTeam MVY

12543 Posts

Posted - 2007-08-09 : 04:33:01

It sounds like it doesn't think a full backup has been taken.
I never append backups to a file - partly because if you lose the file then you lose everything.
I backup to
<dbname>_FULL_yyyymmdd_hhmmss.bak
<dbname>_DIFF_yyyymmdd_hhmmss.bak
<dbname>_LOG_yyyymmdd_hhmmss.bak

Also makes it easy to process the files using the filename.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

methodology
Starting Member

31 Posts

Posted - 2007-08-09 : 04:49:01

NR

thanks for your reply.

is it possible to wotrk out WHY it doesnt think a full has been taken?

the chronology of the backups is as follows:

monthly init @ 7am first tues of month to file A
weekly init @ 7.30 Am tuesdays to file B

nightly diff to file A @ 2215 nightly
TL backups to file B every 10 mins

if its doesnt think there s a full backup (which I agree with) why did it work OK on Tues night?

cheers

alastair

"A computer once beat me at chess - but it was no match for me at kick boxing" - Emo Phillips.

nr
SQLTeam MVY

12543 Posts

Posted - 2007-08-09 : 04:57:03

Could have a look at
msdb.dbo.backupset

Not sure what the server uses to detect changed data - it's probably in "Inside SQL Server" though.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

PeterNeo
Constraint Violating Yak Guru

357 Posts

Posted - 2007-08-09 : 08:59:42

Hi,

we encountered similar problem while giving same name for both FULL and DIFF backups.
Then we changed the names for FULL and DIFF.

Thanks,
Peter

methodology
Starting Member

31 Posts

Posted - 2007-08-09 : 09:01:29

sorry peter - dont quite follow you there

can you explain with specifics please?

Cheers

Alastair Jones

"A computer once beat me at chess - but it was no match for me at kick boxing" - Emo Phillips.

methodology
Starting Member

31 Posts

Posted - 2007-08-09 : 09:04:29

oh - sorry. got you now. ive renamed the diff, but i get the same error. should i relinitalise the whole thing now with my two differently named jobs?

"A computer once beat me at chess - but it was no match for me at kick boxing" - Emo Phillips.

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2007-08-09 : 22:52:42

Did you change any property in the db that requires full backup?

PeterNeo
Constraint Violating Yak Guru

357 Posts

Posted - 2007-08-10 : 00:42:38

Hi Jones,

sorry for my delayed reply.
Yes u have to perform all the things right from the beginning.

Thanks,
Peter

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources