2012年3月11日星期日

disaster recovery question

sql2k sp2
Im not in the heat of a disaster but was doing some
testing regarding this topic. Say I experience total
hardward failure. I require a new box or the need to
reinstall the OS and SQL. So Im @. the point that Ive
reinstalled SQL and now Im going to restore my backups.
What Im doing currently is a full backup @. night and TLog
backups every 5 minutes. I restore the Master DB first.
Then my first user db. Now its time to restore the TLogs
for the user db. Since SQL has been reinstalled, SQL no
longer knows where my backups exist. Because of this, I
cant use the defaults in EM for restoring. I need to
switch to "From Device" instead of using "Database" next
to Restore: I then need to click Select device/add/
elipses by "file name"/ select the file/ and hit OK a
couple of times for the first TLog restore. Now I do TLog
backup for 14 hours a day. That totals to 168 backups
total. It would take me hours to specify all these one at
a time. Is there a way to tell a newly installed SQL box
where all the backups are? So that I dont have to follow
this long path for each indiviual TLog restore?
TIA, ChrisRestore msdb. It keeps track of your backup history. EM uses it to
populate the GUI, allowing you to click and go.
HTH
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"chris" <anonymous@.discussions.microsoft.com> wrote in message
news:01f501c3da0c$22667ab0$a401280a@.phx.gbl...
sql2k sp2
Im not in the heat of a disaster but was doing some
testing regarding this topic. Say I experience total
hardward failure. I require a new box or the need to
reinstall the OS and SQL. So Im @. the point that Ive
reinstalled SQL and now Im going to restore my backups.
What Im doing currently is a full backup @. night and TLog
backups every 5 minutes. I restore the Master DB first.
Then my first user db. Now its time to restore the TLogs
for the user db. Since SQL has been reinstalled, SQL no
longer knows where my backups exist. Because of this, I
cant use the defaults in EM for restoring. I need to
switch to "From Device" instead of using "Database" next
to Restore: I then need to click Select device/add/
elipses by "file name"/ select the file/ and hit OK a
couple of times for the first TLog restore. Now I do TLog
backup for 14 hours a day. That totals to 168 backups
total. It would take me hours to specify all these one at
a time. Is there a way to tell a newly installed SQL box
where all the backups are? So that I dont have to follow
this long path for each indiviual TLog restore?
TIA, Chris|||(not quite on point to your question...)
If your database gets much update action, instead of just doing transaction
log backups every 5 minutes and letting them accumulate all day long,
consider doing periodic differential backups, maybe hourly:.
Full
log
log
log...
differential
log
log
log...
differential
log
log
log...
Restore sequence becomes:
most recent full
most recent differential
any t-log backups since most recent differential.
I'm pretty sure this would restore a lot faster than restoring a full and a
day's worth of logs.
"chris" <anonymous@.discussions.microsoft.com> wrote in message
news:01f501c3da0c$22667ab0$a401280a@.phx.gbl...
quote:

> sql2k sp2
> Im not in the heat of a disaster but was doing some
> testing regarding this topic. Say I experience total
> hardward failure. I require a new box or the need to
> reinstall the OS and SQL. So Im @. the point that Ive
> reinstalled SQL and now Im going to restore my backups.
> What Im doing currently is a full backup @. night and TLog
> backups every 5 minutes. I restore the Master DB first.
> Then my first user db. Now its time to restore the TLogs
> for the user db. Since SQL has been reinstalled, SQL no
> longer knows where my backups exist. Because of this, I
> cant use the defaults in EM for restoring. I need to
> switch to "From Device" instead of using "Database" next
> to Restore: I then need to click Select device/add/
> elipses by "file name"/ select the file/ and hit OK a
> couple of times for the first TLog restore. Now I do TLog
> backup for 14 hours a day. That totals to 168 backups
> total. It would take me hours to specify all these one at
> a time. Is there a way to tell a newly installed SQL box
> where all the backups are? So that I dont have to follow
> this long path for each indiviual TLog restore?
> TIA, Chris
|||Thanks Tom.
quote:

>--Original Message--
>Restore msdb. It keeps track of your backup history. EM

uses it to
quote:

>populate the GUI, allowing you to click and go.
>HTH
>--
>Tom
>----

--
quote:

>Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
>SQL Server MVP
>Columnist, SQL Server Professional
>Toronto, ON Canada
>www.pinnaclepublishing.com/sql
>
>"chris" <anonymous@.discussions.microsoft.com> wrote in

message
quote:

>news:01f501c3da0c$22667ab0$a401280a@.phx.gbl...
>sql2k sp2
>Im not in the heat of a disaster but was doing some
>testing regarding this topic. Say I experience total
>hardward failure. I require a new box or the need to
>reinstall the OS and SQL. So Im @. the point that Ive
>reinstalled SQL and now Im going to restore my backups.
>What Im doing currently is a full backup @. night and TLog
>backups every 5 minutes. I restore the Master DB first.
>Then my first user db. Now its time to restore the TLogs
>for the user db. Since SQL has been reinstalled, SQL no
>longer knows where my backups exist. Because of this, I
>cant use the defaults in EM for restoring. I need to
>switch to "From Device" instead of using "Database" next
>to Restore: I then need to click Select device/add/
>elipses by "file name"/ select the file/ and hit OK a
>couple of times for the first TLog restore. Now I do TLog
>backup for 14 hours a day. That totals to 168 backups
>total. It would take me hours to specify all these one at
>a time. Is there a way to tell a newly installed SQL box
>where all the backups are? So that I dont have to follow
>this long path for each indiviual TLog restore?
>TIA, Chris
>

没有评论:

发表评论