Disaster Recovery question

Ive been doing a bit of digging lately about Restoring the Master db to a
box with another name and Im a bit confused. Near as I can tell this action
isn't supported. At least not by people in these types of forums. My
Disaster Recovery pobbibilities are very limited @. the moment. I don't have
a spare server. Probably won't have one anytime soon. Yes, yes, Ive brought
this up to everyone but got nowhere. The reply I got was if the server dies
we would resotre from backup over to the Reporting box. So now my question.
I take backups of the Master, MSDB, and user db's regularly. They are put
onto tape. But, since Master cant be restored to a box with another name,
what good is the backup of it for someone like in my scenario? That being
said, how would I ever get back my logins/ passwords, etc. I know I can get
the jobs from MSDB, but not the logins. All ideas appreciated.
sql2k sp3
TIA, ChrisR
You can restore master to another box. Just keep in mind that if you don't
have your app DB's in the exact same folders, you will have a bunch of
suspect DB's. Here, you can do one of two things:
1) Drop the suspect DB's and simply restore from backup. IOW, you've
restored master and the other DB's don't even exist (physically) on your
server yet. When master is restored, it thinks they're there (since they're
in the sysdatabases table) and it then marks them as suspect. Dropping them
sets the record straight and you can just restore them at that point.
2) Restore the app databases first. Make sure they are in the exact same
folders as on your original server. Now, restore master and everything is
in synch.
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com
"ChrisR" <ChrisR@.NoEmails.com> wrote in message
news:eRF8sr2uEHA.228@.TK2MSFTNGP10.phx.gbl...
Ive been doing a bit of digging lately about Restoring the Master db to a
box with another name and Im a bit confused. Near as I can tell this action
isn't supported. At least not by people in these types of forums. My
Disaster Recovery pobbibilities are very limited @. the moment. I don't have
a spare server. Probably won't have one anytime soon. Yes, yes, Ive brought
this up to everyone but got nowhere. The reply I got was if the server dies
we would resotre from backup over to the Reporting box. So now my question.
I take backups of the Master, MSDB, and user db's regularly. They are put
onto tape. But, since Master cant be restored to a box with another name,
what good is the backup of it for someone like in my scenario? That being
said, how would I ever get back my logins/ passwords, etc. I know I can get
the jobs from MSDB, but not the logins. All ideas appreciated.
sql2k sp3
TIA, ChrisR
|||Tom I appreciate your reply. To clarify, you are referring to a box with
another name, correct? Ive made several attempts at this and have yet to do
it successfully. I restore it fine, but then the service doesnt start and
doesnt report an error either. Says it can be an internal Windows problem.
All the KB articles I can find on moving db's makes no reference to whether
or not the box has the same name.
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:OSDMax2uEHA.200@.TK2MSFTNGP11.phx.gbl...
> You can restore master to another box. Just keep in mind that if you
don't
> have your app DB's in the exact same folders, you will have a bunch of
> suspect DB's. Here, you can do one of two things:
> 1) Drop the suspect DB's and simply restore from backup. IOW, you've
> restored master and the other DB's don't even exist (physically) on your
> server yet. When master is restored, it thinks they're there (since
they're
> in the sysdatabases table) and it then marks them as suspect. Dropping
them
> sets the record straight and you can just restore them at that point.
> 2) Restore the app databases first. Make sure they are in the exact
same
> folders as on your original server. Now, restore master and everything is
> in synch.
> --
> Tom
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinnaclepublishing.com
>
> "ChrisR" <ChrisR@.NoEmails.com> wrote in message
> news:eRF8sr2uEHA.228@.TK2MSFTNGP10.phx.gbl...
> Ive been doing a bit of digging lately about Restoring the Master db to a
> box with another name and Im a bit confused. Near as I can tell this
action
> isn't supported. At least not by people in these types of forums. My
> Disaster Recovery pobbibilities are very limited @. the moment. I don't
have
> a spare server. Probably won't have one anytime soon. Yes, yes, Ive
brought
> this up to everyone but got nowhere. The reply I got was if the server
dies
> we would resotre from backup over to the Reporting box. So now my
question.
> I take backups of the Master, MSDB, and user db's regularly. They are put
> onto tape. But, since Master cant be restored to a box with another name,
> what good is the backup of it for someone like in my scenario? That being
> said, how would I ever get back my logins/ passwords, etc. I know I can
get
> the jobs from MSDB, but not the logins. All ideas appreciated.
>
> --
> sql2k sp3
> TIA, ChrisR
>
|||That's odd. The only thing I'd be doing with respect to the name is
sp_dropserver <old name> and sp_addserver <new name., 'local'.
Can you post the SQL Server error log of the dysfunctional server? Have you
tried starting the SQL Server service from the command prompt? Check out
sqlservr.exe in the BOL for the details.
BTW, I've had no problems in DR rehearsals restoring master to another box.
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com
"ChrisR" <ChrisR@.NoEmails.com> wrote in message
news:%23gEtx72uEHA.1448@.TK2MSFTNGP10.phx.gbl...
Tom I appreciate your reply. To clarify, you are referring to a box with
another name, correct? Ive made several attempts at this and have yet to do
it successfully. I restore it fine, but then the service doesnt start and
doesnt report an error either. Says it can be an internal Windows problem.
All the KB articles I can find on moving db's makes no reference to whether
or not the box has the same name.
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:OSDMax2uEHA.200@.TK2MSFTNGP11.phx.gbl...
> You can restore master to another box. Just keep in mind that if you
don't
> have your app DB's in the exact same folders, you will have a bunch of
> suspect DB's. Here, you can do one of two things:
> 1) Drop the suspect DB's and simply restore from backup. IOW, you've
> restored master and the other DB's don't even exist (physically) on your
> server yet. When master is restored, it thinks they're there (since
they're
> in the sysdatabases table) and it then marks them as suspect. Dropping
them
> sets the record straight and you can just restore them at that point.
> 2) Restore the app databases first. Make sure they are in the exact
same
> folders as on your original server. Now, restore master and everything is
> in synch.
> --
> Tom
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinnaclepublishing.com
>
> "ChrisR" <ChrisR@.NoEmails.com> wrote in message
> news:eRF8sr2uEHA.228@.TK2MSFTNGP10.phx.gbl...
> Ive been doing a bit of digging lately about Restoring the Master db to a
> box with another name and Im a bit confused. Near as I can tell this
action
> isn't supported. At least not by people in these types of forums. My
> Disaster Recovery pobbibilities are very limited @. the moment. I don't
have
> a spare server. Probably won't have one anytime soon. Yes, yes, Ive
brought
> this up to everyone but got nowhere. The reply I got was if the server
dies
> we would resotre from backup over to the Reporting box. So now my
question.
> I take backups of the Master, MSDB, and user db's regularly. They are put
> onto tape. But, since Master cant be restored to a box with another name,
> what good is the backup of it for someone like in my scenario? That being
> said, how would I ever get back my logins/ passwords, etc. I know I can
get
> the jobs from MSDB, but not the logins. All ideas appreciated.
>
> --
> sql2k sp3
> TIA, ChrisR
>
|||The name does not prevent SQL Server from starting. Having a different path does, though. Hunt for
error messages in the error log file and in the event log. This will show you the root of the
problem(s).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"ChrisR" <ChrisR@.NoEmails.com> wrote in message news:%23gEtx72uEHA.1448@.TK2MSFTNGP10.phx.gbl...
> Tom I appreciate your reply. To clarify, you are referring to a box with
> another name, correct? Ive made several attempts at this and have yet to do
> it successfully. I restore it fine, but then the service doesnt start and
> doesnt report an error either. Says it can be an internal Windows problem.
> All the KB articles I can find on moving db's makes no reference to whether
> or not the box has the same name.
>
>
> "Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
> news:OSDMax2uEHA.200@.TK2MSFTNGP11.phx.gbl...
> don't
> they're
> them
> same
> action
> have
> brought
> dies
> question.
> get
>
|||> The name does not prevent SQL Server from starting. Having a different
path does, though
Bingo! The Master, Model, and MSDB files must all be in the same path as
they were on the original Server. That was the problem.
Tom and Tibor, thank you both!
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:e0HZ#J3uEHA.1264@.TK2MSFTNGP12.phx.gbl...
> The name does not prevent SQL Server from starting. Having a different
path does, though. Hunt for
> error messages in the error log file and in the event log. This will show
you the root of the
> problem(s).
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "ChrisR" <ChrisR@.NoEmails.com> wrote in message
news:%23gEtx72uEHA.1448@.TK2MSFTNGP10.phx.gbl...[vbcol=seagreen]
do[vbcol=seagreen]
and[vbcol=seagreen]
problem.[vbcol=seagreen]
whether[vbcol=seagreen]
you've[vbcol=seagreen]
your[vbcol=seagreen]
is[vbcol=seagreen]
a[vbcol=seagreen]
put[vbcol=seagreen]
name,[vbcol=seagreen]
being
>