Differential backup/restore

Hello,
If this sounds like a stupid question I apologize in advance but I'm using
Simple model and do a Full backup 6am daily and then hourly Differentials.
If I need to restore to 9am do I need to restore Full and then all the
Differentials 7, 8, and 9 or do I just need to restore Full and 9am? Does
each Differential include all the changes since the Full backup or from
since the last backup whatever it was, full or differential?
Thank you
Brian MorrisYou restore the most recent db backup and then the most recent dif backup. I
n your example, the 6am
db backup and then the 9am diff backup. You might want to consider doing log
backups instead of or
to complement your diff backups. Log backups has advantages that diff backup
s don't have (point in
time restore, doing backup of a corrupt database etc).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Brian" <s@.y> wrote in message news:%237oGgeoeHHA.3648@.TK2MSFTNGP05.phx.gbl...n">
> Hello,
> If this sounds like a stupid question I apologize in advance but I'm usin
g Simple model and do a
> Full backup 6am daily and then hourly Differentials. If I need to restore
to 9am do I need to
> restore Full and then all the Differentials 7, 8, and 9 or do I just need
to restore Full and 9am?
> Does each Differential include all the changes since the Full backup or fr
om since the last backup
> whatever it was, full or differential?
> Thank you
> Brian Morris
>|||Hi Brian,
To add on to Tibor, If it is a production server it is recommeneded to set
the database recovery model to FULL and have Transaction log backups
in regular frequencies (Say 15 minutes or so). THis will help you to recover
the database POINT_IN_TIME.
Thanks
Hari
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:ujNoRDpeHHA.5044@.TK2MSFTNGP06.phx.gbl...
> You restore the most recent db backup and then the most recent dif backup.
> In your example, the 6am db backup and then the 9am diff backup. You might
> want to consider doing log backups instead of or to complement your diff
> backups. Log backups has advantages that diff backups don't have (point in
> time restore, doing backup of a corrupt database etc).
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "Brian" <s@.y> wrote in message
> news:%237oGgeoeHHA.3648@.TK2MSFTNGP05.phx.gbl...
>|||OK, so it is really only the last diff that I need so that I can use
overwrite instead of append when I'm doing those.
I thought that with Simple recovery you don't do log backups?
Thank you
Brian Morris
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:ujNoRDpeHHA.5044@.TK2MSFTNGP06.phx.gbl...
> You restore the most recent db backup and then the most recent dif backup.
> In your example, the 6am db backup and then the 9am diff backup. You might
> want to consider doing log backups instead of or to complement your diff
> backups. Log backups has advantages that diff backups don't have (point in
> time restore, doing backup of a corrupt database etc).
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "Brian" <s@.y> wrote in message
> news:%237oGgeoeHHA.3648@.TK2MSFTNGP05.phx.gbl...
>|||Someone had suggested that Simple with diff backups every hour or halfhour
would be what I needed. When it came it was set to Full. I was not doing
any log backups, just 2 daily complete db backups and the log was getting
huge. The amount of work that would have to be done over in an hour would
not be too much and the recovery process sounded much more straight forward.
I will look at what you suggest.
Thanks
Brian Morris
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:OjOUr1peHHA.4868@.TK2MSFTNGP06.phx.gbl...
> Hi Brian,
> To add on to Tibor, If it is a production server it is recommeneded to set
> the database recovery model to FULL and have Transaction log backups
> in regular frequencies (Say 15 minutes or so). THis will help you to
> recover the database POINT_IN_TIME.
> Thanks
> Hari
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
> in message news:ujNoRDpeHHA.5044@.TK2MSFTNGP06.phx.gbl...
>|||> OK, so it is really only the last diff that I need so that I can use
> overwrite instead of append when I'm doing those.
Correct.

> I thought that with Simple recovery you don't do log backups?
Correct. But my thought was that you could run full or bulk logged and do lo
g backups.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Brian" <s@.y> wrote in message news:uhIw8rseHHA.4064@.TK2MSFTNGP03.phx.gbl...
> OK, so it is really only the last diff that I need so that I can use
> overwrite instead of append when I'm doing those.
> I thought that with Simple recovery you don't do log backups?
> Thank you
> Brian Morris
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote i
n
> message news:ujNoRDpeHHA.5044@.TK2MSFTNGP06.phx.gbl...
>|||I'm looking at that now but I'm not sure how much more effective it could
be. We don't have all those fancy fault tolerant setups, so I'm thinking
that if the drive with my log backups fails too I will still have to go to
the tape which would be an hour old.
Our set up is -
db on disk-1
log on disk-2
diff backups to a folder on log disk every hour (or 15 mins for logs as you
suggest with Full)
ntbackup folder to tape every hour
Thanks
Brian Morris
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:uRlLEPteHHA.5044@.TK2MSFTNGP05.phx.gbl...[vbcol=seagreen]
> Correct.
>
> Correct. But my thought was that you could run full or bulk logged and do
> log backups.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "Brian" <s@.y> wrote in message
> news:uhIw8rseHHA.4064@.TK2MSFTNGP03.phx.gbl...|||"Brian" <s@.y> wrote in message news:e8hFzeteHHA.1216@.TK2MSFTNGP03.phx.gbl...
> I'm looking at that now but I'm not sure how much more effective it could
> be. We don't have all those fancy fault tolerant setups, so I'm thinking
> that if the drive with my log backups fails too I will still have to go to
> the tape which would be an hour old.
> Our set up is -
> db on disk-1
> log on disk-2
> diff backups to a folder on log disk every hour (or 15 mins for logs as
> you suggest with Full)
> ntbackup folder to tape every hour
I'd at the very least do your backups to a network share. You have too many
eggs in one basket.
And I think what Tibor is getting at is if you're doing diff's this often,
you might as well go ahead with a FULL recovery model and use transaction
log backups. This gives you more recovery options.
For example, right now if your DB disk dies, the best you can do is replace
and restore the full and diff backups. So you'll lose work.
If you are in full recovery mode and your DB disk dies, you can restore the
FULL, the DIFF and any subsequent log backups. (And if you back up the "tail
of the log", you can restore to the point of failure.
However, right now if your log disk fails, you're out of luck with restoring
because your backups are there.
As for "fancy fault tolerant setups" RAID cards (or even on the mobo) are
getting cheaper and cheaper every day. Might be well worth reconsidering.

> Thanks
> Brian Morris
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
> in message news:uRlLEPteHHA.5044@.TK2MSFTNGP05.phx.gbl...
>
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html|||Hmm, it looks like I've got a lot to think about.
I agree about the all in one basket which is why we wanted the hourly
backups.
I'm not too keen on the backup to network share, I was doing that for
something else and every so often there was a blip in the connection and the
backup failed and we have never been able to figure out the blip.
The Full with log backups does make more sense when you comapre the optins.
I'll have to try it to see how it imapacts things.
Thanks
Brian Morris
"Greg D. Moore (Strider)" <mooregr_deleteth1s@.greenms.com> wrote in message
news:%230IpArteHHA.596@.TK2MSFTNGP06.phx.gbl...
>
> "Brian" <s@.y> wrote in message
> news:e8hFzeteHHA.1216@.TK2MSFTNGP03.phx.gbl...
> I'd at the very least do your backups to a network share. You have too
> many eggs in one basket.
> And I think what Tibor is getting at is if you're doing diff's this often,
> you might as well go ahead with a FULL recovery model and use transaction
> log backups. This gives you more recovery options.
> For example, right now if your DB disk dies, the best you can do is
> replace and restore the full and diff backups. So you'll lose work.
> If you are in full recovery mode and your DB disk dies, you can restore
> the FULL, the DIFF and any subsequent log backups. (And if you back up the
> "tail of the log", you can restore to the point of failure.
> However, right now if your log disk fails, you're out of luck with
> restoring because your backups are there.
> As for "fancy fault tolerant setups" RAID cards (or even on the mobo) are
> getting cheaper and cheaper every day. Might be well worth reconsidering.
>
> --
> Greg Moore
> SQL Server DBA Consulting Remote and Onsite available!
> Email: sql (at) greenms.com
> http://www.greenms.com/sqlserver.html
>|||"Brian" <s@.y> wrote in message news:uEioqPueHHA.4604@.TK2MSFTNGP06.phx.gbl...
> One last question/observation
> I'm reading that I have to restore all the log backups as opposed to just
> the last diff backup.
> If I were to lose one of the in between log backups I would be way worse
> off than if I lost one of the in between diff backups, would I not?
> Any way around this?
> Thanks
> Brian Morris
> "Brian" <s@.y> wrote in message
> news:%23HbumBueHHA.4128@.TK2MSFTNGP05.phx.gbl...
>