differential backup question

Question

differential backup question

chewychewy

Hall of Fame

Points: 3126
More actions
November 7, 2012 at 2:36 am

#264484

Hi Guys,
For example only.
I have a daily full db backup via SQL maintenance plan to file system at 10pm.
I also have a daily direct backup of database via arcserve SQL agent to tape at 11pm:
Monday to Saturday - differential
Sunday - Full
In this case, if let's say thursday afternoon have data issue, can i restore with the differential backup taken on wed night <arcserve> + the full backup taken on sunday <arcserve>?
Not supposed to work right?
thanks

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

sanket kokane Hall of Fame Points: 3465 More actions · Answer 1

chewychewy (11/7/2012)
Hi Guys,
For example only.
I have a daily full db backup via SQL maintenance plan to file system at 10pm.
I also have a daily direct backup of database via arcserve SQL agent to tape at 11pm:
Monday to Saturday - differential
Sunday - Full
In this case, if let's say thursday afternoon have data issue, can i restore with the differential backup taken on wed night <arcserve> + the full backup taken on sunday <arcserve>?
Not supposed to work right?
thanks

No,

then you need to restored full backup taken on wed night (10 PM) + differntial backup taken on wed night using arcserv (11 PM) .

-----------------------------------------------------------------------------
संकेत कोकणे

Bhuvnesh SSC Guru Points: 59351 More actions · Answer 2

@sanket why tuesday full back which is not there (for arcserve )

see

I also have a daily direct backup of database via arcserve SQL agent to tape at 11pm:
Monday to Saturday - differential
Sunday - Full

i think OP needs to go for sunday FULL and last diif backup taekn (whether on tuesday or wednesday)

-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

sanket kokane Hall of Fame Points: 3465 More actions · Answer 3

Bhuvnesh (11/7/2012)
@sanket why tuesday full back which is not there (for arcserve )
see
I also have a daily direct backup of database via arcserve SQL agent to tape at 11pm:
Monday to Saturday - differential
Sunday - Full
i think OP needs to go for sunday FULL and last diif backup taekn (whether on tuesday or wednesday)

I mean

full backup taken on wed night (10 PM) using maintenance plan + differntial backup taken on wed night using arcserv (11 PM)

-----------------------------------------------------------------------------
संकेत कोकणे

sanket kokane Hall of Fame Points: 3465 More actions · Answer 4

i think OP needs to go for sunday FULL and last diif backup taekn (whether on tuesday or wednesday)

will it work ?

diff backup should be applied to its earliest full backup , which is Wednesday's full backup .

-----------------------------------------------------------------------------
संकेत कोकणे

Bhuvnesh SSC Guru Points: 59351 More actions · Answer 5

i had impression that there are two options

I have a daily full db backup via SQL maintenance plan to file system at 10pm.

AND

I also have a daily direct backup of database via arcserve SQL agent to tape at 11pm:
Monday to Saturday - differential
Sunday - Full

and thats the case then why there is duplicate backup for sunday

-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

Perry Whittle SSC Guru Points: 234013 More actions · Answer 6

chewychewy (11/7/2012)
Hi Guys,
For example only.
I have a daily full db backup via SQL maintenance plan to file system at 10pm.
I also have a daily direct backup of database via arcserve SQL agent to tape at 11pm:
Monday to Saturday - differential
Sunday - Full
In this case, if let's say thursday afternoon have data issue, can i restore with the differential backup taken on wed night <arcserve> + the full backup taken on sunday <arcserve>?
Not supposed to work right?
thanks

Each time a full backup is taken the Differential_Base_LSN is incremented. Failure on thursday would require Wednesday 10pm full and Wednesday 11pm differential

-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs" 😉

chewychewy Hall of Fame Points: 3126 More actions · Answer 7

thanks.

So conclusion is regardless of whether the full backup is perform via sql maintenance plan to disk <option 1> or the tape <eg arcserve> sql agent, a marker will be set on the DB indicating a full backup has been performed. The differential backup will track changes after the last full backup <be it via sql maintenance or tape sql agent> right?

thanks

Perry Whittle SSC Guru Points: 234013 More actions · Answer 8

Unless arcserve is doing something different like snapshotting disk files.

Differential bitmaps are reset by a full backup in SQL server.

-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs" 😉