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. 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...
> 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 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...
>> 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 Morris
>|||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...
>> 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 Morris
>|||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...
>> 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...
>> 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 Morris
>>
>|||> 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 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...
> 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...
>> 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 Morris
>>
>|||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...
>> 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
> 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...
>> 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...
>> 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 Morris
>>|||"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...
>> 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
>> 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...
>> 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...
>> 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 Morris
>>
>
--
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'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...
>> 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 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...
>> 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...
>> 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 Morris
>>
>>
> --
> Greg Moore
> SQL Server DBA Consulting Remote and Onsite available!
> Email: sql (at) greenms.com
> http://www.greenms.com/sqlserver.html
>|||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...
> 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'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...
>> 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 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...
>> 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...
>>> 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 Morris
>>>
>>
>>
>> --
>> 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:%23HbumBueHHA.4128@.TK2MSFTNGP05.phx.gbl...
> 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.
Hmm. In general though that shouldn't be a huge problem. (Yes, it's a
problem, but...).
The next backup will backup the data. (Unless you're saying you have a
backup reported as complete, but no actual file.)
One thing you can do to is have a dedicated network connection. That may be
faster and less prone to network glitches.
Or, if you really want, plug in a nice generic IDE drive to the existing box
and do backups to that.
In worse case scenario, open box, grab drive and put into another machine.
> 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'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...
>> 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 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...
>> 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...
>>> 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 Morris
>>>
>>
>>
>> --
>> Greg Moore
>> SQL Server DBA Consulting Remote and Onsite available!
>> Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html
>>
>
--
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...
>> 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'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...
>> 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 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...
>> 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...
>>> 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 Morris
>>>
>>>
>>
>> --
>> 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.
All log backups since the last full or last DIFF, yes.
> 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?
Yes. That's called preserving the backup chain. VERY important to do.
But again, with gives you far more possibilities in terms of recovering
data.
Imagine that you're in FULL RECOVERY mode.
10 Rows added before 11:30 AM
Transaction backup made at 11:30 AM
90 Rows added between 11:30 AM and NOON
Someone deletes 100 rows by mistake at 11:50 AM.
Your next transaction log backup is at Noon.
You need those rows;
Restore FULL backup WITH NORECOVERY
Restore most recent (if any) DIFF backup WITH NORECOVERY
Restore transaction logs up to one BEFORE the Noon backup. (use WITH
NORECOVERY)
Now, restore the NOON transaction log backup with STANDBY specifying STOPAT
and the time.
Now since you won't know the exact minute, play with it until the 100 rows
show what you want...
You've got your data back.
If you had only the DIFF backups and no log backups you'd be out of luck.
> Any way around this?
> Thanks
> Brian Morris
--
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html|||OK, you've got me there, I was not even thinking about recovering from human
problems (which actually occur more often than machine ones!). I'm now sold
on FULL.
Thanks Greg
Brian Morris
"Greg D. Moore (Strider)" <mooregr_deleteth1s@.greenms.com> wrote in message
news:ermwWkueHHA.4136@.TK2MSFTNGP02.phx.gbl...
>
> "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.
> All log backups since the last full or last DIFF, yes.
>> 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?
> Yes. That's called preserving the backup chain. VERY important to do.
> But again, with gives you far more possibilities in terms of recovering
> data.
> Imagine that you're in FULL RECOVERY mode.
> 10 Rows added before 11:30 AM
> Transaction backup made at 11:30 AM
> 90 Rows added between 11:30 AM and NOON
> Someone deletes 100 rows by mistake at 11:50 AM.
> Your next transaction log backup is at Noon.
> You need those rows;
> Restore FULL backup WITH NORECOVERY
> Restore most recent (if any) DIFF backup WITH NORECOVERY
> Restore transaction logs up to one BEFORE the Noon backup. (use WITH
> NORECOVERY)
> Now, restore the NOON transaction log backup with STANDBY specifying
> STOPAT and the time.
> Now since you won't know the exact minute, play with it until the 100 rows
> show what you want...
> You've got your data back.
> If you had only the DIFF backups and no log backups you'd be out of luck.
>
>> Any way around this?
>> Thanks
>> Brian Morris
> --
> 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:e7uHoJweHHA.4868@.TK2MSFTNGP06.phx.gbl...
> OK, you've got me there, I was not even thinking about recovering from
> human problems (which actually occur more often than machine ones!). I'm
> now sold on FULL.
Ayup, same here.
> Thanks Greg
> Brian Morris
>
--
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html|||I'll ask the h/ware guy about dedicated network connection - I don't know
how to do that.
You mean like a USB drive? I like those and use them to backup other stuff
on the pc's but they fail too so it's still a matter of making sure I've got
a good copy of everything in at least one place.
I forgot to ask, to 'preserving the backup chain', if a log backup job fails
and I have it set to attempt retry will the second try capture the proper
data?
Thanks
Brian
"Greg D. Moore (Strider)" <mooregr_deleteth1s@.greenms.com> wrote in message
news:%230c1sfueHHA.4704@.TK2MSFTNGP06.phx.gbl...
>
> "Brian" <s@.y> wrote in message
> news:%23HbumBueHHA.4128@.TK2MSFTNGP05.phx.gbl...
>> 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.
> Hmm. In general though that shouldn't be a huge problem. (Yes, it's a
> problem, but...).
> The next backup will backup the data. (Unless you're saying you have a
> backup reported as complete, but no actual file.)
> One thing you can do to is have a dedicated network connection. That may
> be faster and less prone to network glitches.
> Or, if you really want, plug in a nice generic IDE drive to the existing
> box and do backups to that.
> In worse case scenario, open box, grab drive and put into another machine.
>
>> 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'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...
>> 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 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...
>> 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...
>>> 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 Morris
>>>
>>>
>>
>> --
>> Greg Moore
>> SQL Server DBA Consulting Remote and Onsite available!
>> Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html
>>
>>
> --
> 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:%23ptarbweHHA.1252@.TK2MSFTNGP04.phx.gbl...
> I'll ask the h/ware guy about dedicated network connection - I don't know
> how to do that.
Generally I've never really found one necessary, but it can be useful.
> You mean like a USB drive?
No, I meant normal PATA or SATA drive. USB 2.0 will work in a pinch but is
definitely slower.
(now a thumb drive, if your backups are fairly small might just work,
haven't tried that though.)
> I like those and use them to backup other stuff on the pc's but they fail
> too so it's still a matter of making sure I've got a good copy of
> everything in at least one place.
Oh definitely. What I actually used a lot were Snap Servers, some of the
newer ones are quite nice.
> I forgot to ask, to 'preserving the backup chain', if a log backup job
> fails and I have it set to attempt retry will the second try capture the
> proper data?
Yes.
What I really like to do given hardware, etc, is setup a log-shipping
secondary (even if I roll my own) and apply the backups as I go. Both
allows me to "test" my backup all along and in the event I do have a
problem, 1/2 the work of recovery is already done.
(and if you delay applying the logs by about say 4 hours, when the developer
says, "oops" your chances of having whatever he deleted available on the
log-shipping secondary is generally pretty high.
> Thanks
> Brian
> "Greg D. Moore (Strider)" <mooregr_deleteth1s@.greenms.com> wrote in
> message news:%230c1sfueHHA.4704@.TK2MSFTNGP06.phx.gbl...
--
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html|||I have a 4GB flash drive but it somehow seems slower than my SATA USB drive.
I've seen the snap server adverts, I'll take a closer look.
I'm going to see if I can test the retry option to make sure I have to set
correctly.
Okay, you just passed my level on the log-shipping. I see it's something
about automatically maintaining a backup server. When I get a spare machine
I'll take a read about that.
Thanks a lot for the advice,
Brian Morris
"Greg D. Moore (Strider)" <mooregr_deleteth1s@.greenms.com> wrote in message
news:e7EOXxxeHHA.3948@.TK2MSFTNGP03.phx.gbl...
> "Brian" <s@.y> wrote in message
> news:%23ptarbweHHA.1252@.TK2MSFTNGP04.phx.gbl...
>> I'll ask the h/ware guy about dedicated network connection - I don't know
>> how to do that.
> Generally I've never really found one necessary, but it can be useful.
>> You mean like a USB drive?
> No, I meant normal PATA or SATA drive. USB 2.0 will work in a pinch but
> is definitely slower.
> (now a thumb drive, if your backups are fairly small might just work,
> haven't tried that though.)
>
>> I like those and use them to backup other stuff on the pc's but they fail
>> too so it's still a matter of making sure I've got a good copy of
>> everything in at least one place.
> Oh definitely. What I actually used a lot were Snap Servers, some of the
> newer ones are quite nice.
>> I forgot to ask, to 'preserving the backup chain', if a log backup job
>> fails and I have it set to attempt retry will the second try capture the
>> proper data?
> Yes.
> What I really like to do given hardware, etc, is setup a log-shipping
> secondary (even if I roll my own) and apply the backups as I go. Both
> allows me to "test" my backup all along and in the event I do have a
> problem, 1/2 the work of recovery is already done.
> (and if you delay applying the logs by about say 4 hours, when the
> developer says, "oops" your chances of having whatever he deleted
> available on the log-shipping secondary is generally pretty high.
>
>> Thanks
>> Brian
>> "Greg D. Moore (Strider)" <mooregr_deleteth1s@.greenms.com> wrote in
>> message news:%230c1sfueHHA.4704@.TK2MSFTNGP06.phx.gbl...
> --
> Greg Moore
> SQL Server DBA Consulting Remote and Onsite available!
> Email: sql (at) greenms.com
> http://www.greenms.com/sqlserver.html
>
