I'm relatively new to Perf Mon and am trying to figure out
the best disk objects to use. I've been watching our
servers and I've noticed that sometimes in the evening the
Avg Disk Q Length goes over acceptable levels. But during
the entire night (or most of it) the Total MBytes/Second
is very high due to backups apparently.
How can I interpret these two numbers? One says the
server is getting slammed all night and the ohter says
it's only having troubles occasionally? Which do I go by?
Dan,
Having high values for Total MBytes/Second is not necessarily bad, all it
indicates is that you're getting good I/O throughput.
However, Avg Disk Queue Length can be a sign of problems, especially when it
gets into the hundreds, because it is indicating that the I/O system cannot
keep up with requests.
For advice on the best SQL Server PerfMon counters for disk I/O, see Tom
Davidson's articles in SQL Magazine.
Hope this helps,
Ron
Ron Talmage
SQL Server MVP
"Dan" <anonymous@.discussions.microsoft.com> wrote in message
news:017601c4a00a$51af0c00$a401280a@.phx.gbl...
> I'm relatively new to Perf Mon and am trying to figure out
> the best disk objects to use. I've been watching our
> servers and I've noticed that sometimes in the evening the
> Avg Disk Q Length goes over acceptable levels. But during
> the entire night (or most of it) the Total MBytes/Second
> is very high due to backups apparently.
> How can I interpret these two numbers? One says the
> server is getting slammed all night and the ohter says
> it's only having troubles occasionally? Which do I go by?
2012年3月29日星期四
2012年3月11日星期日
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, ChrisRYou 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...
>> 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
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...
> > 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
> >>
> >>
> >
> >
>
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, ChrisRYou 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...
>> 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
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...
> > 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
> >>
> >>
> >
> >
>
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
>
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
>
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, ChrisRYou 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 yo
u 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 d
o
> 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 whethe
r
> 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...
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[vbcol=seagreen]
>
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, ChrisRYou 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 yo
u 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 d
o
> 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 whethe
r
> 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...
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[vbcol=seagreen]
>
Disaster Recovery Planning
I've never seen a disaster recovery plan for SQL Server. Can anyone one
direct me to an example. This would be immensley helpful to understanding
how to put one together. Thanx. -Cqlboy
How long is a piece of string?
DR plans are pretty specific to the company/environment in question.
I've set up an active/active SQL cluster in our DR site and am using log
shipping to keep the DR databases in sync with the production DBs;
combined with MDAC aliases, non-standard TCP ports for SQL & DNS aliases
for the server names to allow to very quick, transparent redirection of
client connections to the DR servers in the event of a disaster. I also
wrote a stored proc (that resides on our log shipping monitor box) to
automate all the log shipping role changes (150+ databases is a bit too
time consuming to do manually). Works pretty well too (we had a
"disaster" in August this year when our SAN vendor stuffed up an upgrade
on our production SAN - what a crappy weekend that was!)
My only revision to my SQL DR plan/environment would be more RAM & CPU
for the DR servers as I'm consolidating 4 production SQL instances down
to the 2 DR instances on the active/active cluster (plus a few other DBs
that don't live on our production clusters). The cluster nodes were
pretty stressed for a week back in August but it's hard to justify
spending the extra $$$ when the servers are really only used (hopefully)
very infrequently (like one week in a year).
Cheers,
Mike.
Cqlboy wrote:
> I've never seen a disaster recovery plan for SQL Server. Can anyone one
> direct me to an example. This would be immensley helpful to understanding
> how to put one together. Thanx. -Cqlboy
|||Hi
http://vyaskn.tripod.com/sql_server_...ices.htm#Step1 --administaiting
best practices
"Cqlboy" <Cqlboy@.discussions.microsoft.com> wrote in message
news:8DA906E5-C877-4078-8F89-9A5A1787BE3E@.microsoft.com...
> I've never seen a disaster recovery plan for SQL Server. Can anyone one
> direct me to an example. This would be immensley helpful to understanding
> how to put one together. Thanx. -Cqlboy
|||Thanx, this was helpful. But I was hoping to have something more explicit
since it appears I'll have to create my DR document from scratch. A template
or example to help guide my thinking and writing would be ideal. I'm not a
technical write, eh?
-Cqlboy
"Mike Hodgson" wrote:
> How long is a piece of string?
> DR plans are pretty specific to the company/environment in question.
> I've set up an active/active SQL cluster in our DR site and am using log
> shipping to keep the DR databases in sync with the production DBs;
> combined with MDAC aliases, non-standard TCP ports for SQL & DNS aliases
> for the server names to allow to very quick, transparent redirection of
> client connections to the DR servers in the event of a disaster. I also
> wrote a stored proc (that resides on our log shipping monitor box) to
> automate all the log shipping role changes (150+ databases is a bit too
> time consuming to do manually). Works pretty well too (we had a
> "disaster" in August this year when our SAN vendor stuffed up an upgrade
> on our production SAN - what a crappy weekend that was!)
> My only revision to my SQL DR plan/environment would be more RAM & CPU
> for the DR servers as I'm consolidating 4 production SQL instances down
> to the 2 DR instances on the active/active cluster (plus a few other DBs
> that don't live on our production clusters). The cluster nodes were
> pretty stressed for a week back in August but it's hard to justify
> spending the extra $$$ when the servers are really only used (hopefully)
> very infrequently (like one week in a year).
> Cheers,
> Mike.
> Cqlboy wrote:
>
|||This isn't a DR plan per say but does cover a lot of the areas you need to
be aware of.
http://www.microsoft.com/technet/pro...n/sqlops0.mspx
Operations Guide
Andrew J. Kelly SQL MVP
"Cqlboy" <Cqlboy@.discussions.microsoft.com> wrote in message
news:3ECD364A-14D7-42D5-B8B6-B44D3F6A95A9@.microsoft.com...[vbcol=seagreen]
> Thanx, this was helpful. But I was hoping to have something more explicit
> since it appears I'll have to create my DR document from scratch. A
> template
> or example to help guide my thinking and writing would be ideal. I'm not
> a
> technical write, eh?
> -Cqlboy
>
> "Mike Hodgson" wrote:
|||Requires free registration:
http://www.sqlservercentral.com/colu...eframework.asp
Other example links:
http://www.sql-server-performance.co...r_examples.asp
http://www.disasterrecoverysurvival...eryScript.html
http://sqljunkies.com/HowTo/F30B1E5F...CEAD46C79.scuk
And someone published a book, but I am not finding a reference to it at
the moment. Might check http://www.amazon.com or
http://www.techrepublic.com
Hope this helps,
Michelle
|||Hi Cqlboy,
For DR you have many options, and like in life, how much many so much music
: ) .
Important thing is how fast your server must be online?
If your business cannot wait, implement failover clustering, but this is
most expensive and redundant solution.
Also you have option of standby SQL server whish is connected to working SQL
server in the means of LOG shipping so if you have spare server, go for it.
If your business can wait some time you can use some sort off RAID to
prevent lose of data in case of disk failure and if you cannot afford RAID
you better have good backup strategy, which depends on how often your data
are changed.
So you can have only Full db backups, Full and Differential backups and
Backups off transaction log.
I tried to enumerate what I could remember.
Regards,
Daniel
"Cqlboy" <Cqlboy@.discussions.microsoft.com> wrote in message
news:8DA906E5-C877-4078-8F89-9A5A1787BE3E@.microsoft.com...
> I've never seen a disaster recovery plan for SQL Server. Can anyone one
> direct me to an example. This would be immensley helpful to understanding
> how to put one together. Thanx. -Cqlboy
|||Most of that is more fault tolerance than DR.
For example, a failover cluster is not a DR solution as both nodes need
to share the disk resources. Plus both nodes need to be pretty close to
each other (restricted by limitations of the IO transport protocols -
FC, SCSI - and also short latency of the heartbeat network between
nodes). If there is a real disaster (eg. a fire in your data
centre/computer room or a plane flies into the building where your
cluster is located) then you lose your production environment AND your
DR environment (and probably your job...if you're still alive). A DR
environment really needs to be shared-nothing (like log shipping to a
remote site or some 3rd party replication/geo-clustering technology, or
even standard MSSQL replication for the more budget conscious); you have
to assume you'll lose the entire production environment.
RAID will only protect disk loss (not CPU, RAM, NIC, switches and a
myriad of other potential hardware issues) and minimal loss at that; 1
disk in a RAID 5 array dies then fine, hotswap it; if 2 disks in a RAID
5 array die, or the whole drive cage or RAID controlled then you're up
the creek and need to have an outage (potentially significant (how many
people keep a spare drive cage or RAID controller handy?)) to replace
hardware).
The 3 most significant factors to consider in devising your DR strategy are:
1) maximum acceptable service disruption (down time)
2) maximum acceptable data loss (5min? 1hr? 1day?)
3) budget (the all important $$$)
The solution will always be a compromise in those 3 areas.
But this is getting off the topic a bit. The original question was
about documentation for pre-devised DR plans, which is a bit of big
call. While there are some guides (as people have provided in this
thread) to get you thinking about a solution, factors to consider and
how to go about implementing/documenting it, the solution itself will
require mostly your own brain (and probably several specific questions
posted to newsgroups to overcome potential issues that may arise).
Cheers,
Mike
Daniel Joskovski wrote:
> Hi Cqlboy,
> For DR you have many options, and like in life, how much many so much music
> : ) .
> Important thing is how fast your server must be online?
> If your business cannot wait, implement failover clustering, but this is
> most expensive and redundant solution.
> Also you have option of standby SQL server whish is connected to working SQL
> server in the means of LOG shipping so if you have spare server, go for it.
> If your business can wait some time you can use some sort off RAID to
> prevent lose of data in case of disk failure and if you cannot afford RAID
> you better have good backup strategy, which depends on how often your data
> are changed.
> So you can have only Full db backups, Full and Differential backups and
> Backups off transaction log.
> I tried to enumerate what I could remember.
> Regards,
> Daniel
> "Cqlboy" <Cqlboy@.discussions.microsoft.com> wrote in message
> news:8DA906E5-C877-4078-8F89-9A5A1787BE3E@.microsoft.com...
>
>
|||Here is the 'book' I had mentioned:
Disaster Recovery Toolkit for the SQL DBA
Written by SQL Server expert, Brian Knight, this handy, "how-to"
toolkit contains comprehensive first-hand advice and scripts for
SQL Server DBAs that need to build and implement a successful
disaster recovery plan. With his tips and quips, Brian walks the
DBA through real-world scenarios using an easy, step-by-step
approach. And as part of the download, you'll receive four
scripts, which will greatly speed your recovery time!
Download it today, compliments of Lumigent:
http://www.lumigent.com/go/sd19
Michelle
|||Hi Mike,
Thanks for your time, but from my experience, every time when I have
"disaster" it was connected with some hardware problem, and in 60% of cases
was a disk failure, using plain software mirror "saves" my life many times.
So building some anti nuclear shelter, and not having simple mirror this
days seems ridiculous to me. And nobody will blame me if plane flies in
computer room, but somebody can kill me if I don't have fresh copy of his
data. ;-)
Regards,
Daniel
"Mike Hodgson" <mwh_junk@.hotmail.com> wrote in message
news:evnIwUI6EHA.3124@.TK2MSFTNGP11.phx.gbl...
> Most of that is more fault tolerance than DR.
> For example, a failover cluster is not a DR solution as both nodes need
> to share the disk resources. Plus both nodes need to be pretty close to
> each other (restricted by limitations of the IO transport protocols -
> FC, SCSI - and also short latency of the heartbeat network between
> nodes). If there is a real disaster (eg. a fire in your data
> centre/computer room or a plane flies into the building where your
> cluster is located) then you lose your production environment AND your
> DR environment (and probably your job...if you're still alive). A DR
> environment really needs to be shared-nothing (like log shipping to a
> remote site or some 3rd party replication/geo-clustering technology, or
> even standard MSSQL replication for the more budget conscious); you have
> to assume you'll lose the entire production environment.
> RAID will only protect disk loss (not CPU, RAM, NIC, switches and a
> myriad of other potential hardware issues) and minimal loss at that; 1
> disk in a RAID 5 array dies then fine, hotswap it; if 2 disks in a RAID
> 5 array die, or the whole drive cage or RAID controlled then you're up
> the creek and need to have an outage (potentially significant (how many
> people keep a spare drive cage or RAID controller handy?)) to replace
> hardware).
> The 3 most significant factors to consider in devising your DR strategy
are:[vbcol=seagreen]
> 1) maximum acceptable service disruption (down time)
> 2) maximum acceptable data loss (5min? 1hr? 1day?)
> 3) budget (the all important $$$)
> The solution will always be a compromise in those 3 areas.
> But this is getting off the topic a bit. The original question was
> about documentation for pre-devised DR plans, which is a bit of big
> call. While there are some guides (as people have provided in this
> thread) to get you thinking about a solution, factors to consider and
> how to go about implementing/documenting it, the solution itself will
> require mostly your own brain (and probably several specific questions
> posted to newsgroups to overcome potential issues that may arise).
> Cheers,
> Mike
> Daniel Joskovski wrote:
music[vbcol=seagreen]
SQL[vbcol=seagreen]
it.[vbcol=seagreen]
RAID[vbcol=seagreen]
data[vbcol=seagreen]
understanding[vbcol=seagreen]
direct me to an example. This would be immensley helpful to understanding
how to put one together. Thanx. -Cqlboy
How long is a piece of string?
DR plans are pretty specific to the company/environment in question.
I've set up an active/active SQL cluster in our DR site and am using log
shipping to keep the DR databases in sync with the production DBs;
combined with MDAC aliases, non-standard TCP ports for SQL & DNS aliases
for the server names to allow to very quick, transparent redirection of
client connections to the DR servers in the event of a disaster. I also
wrote a stored proc (that resides on our log shipping monitor box) to
automate all the log shipping role changes (150+ databases is a bit too
time consuming to do manually). Works pretty well too (we had a
"disaster" in August this year when our SAN vendor stuffed up an upgrade
on our production SAN - what a crappy weekend that was!)
My only revision to my SQL DR plan/environment would be more RAM & CPU
for the DR servers as I'm consolidating 4 production SQL instances down
to the 2 DR instances on the active/active cluster (plus a few other DBs
that don't live on our production clusters). The cluster nodes were
pretty stressed for a week back in August but it's hard to justify
spending the extra $$$ when the servers are really only used (hopefully)
very infrequently (like one week in a year).
Cheers,
Mike.
Cqlboy wrote:
> I've never seen a disaster recovery plan for SQL Server. Can anyone one
> direct me to an example. This would be immensley helpful to understanding
> how to put one together. Thanx. -Cqlboy
|||Hi
http://vyaskn.tripod.com/sql_server_...ices.htm#Step1 --administaiting
best practices
"Cqlboy" <Cqlboy@.discussions.microsoft.com> wrote in message
news:8DA906E5-C877-4078-8F89-9A5A1787BE3E@.microsoft.com...
> I've never seen a disaster recovery plan for SQL Server. Can anyone one
> direct me to an example. This would be immensley helpful to understanding
> how to put one together. Thanx. -Cqlboy
|||Thanx, this was helpful. But I was hoping to have something more explicit
since it appears I'll have to create my DR document from scratch. A template
or example to help guide my thinking and writing would be ideal. I'm not a
technical write, eh?
-Cqlboy
"Mike Hodgson" wrote:
> How long is a piece of string?
> DR plans are pretty specific to the company/environment in question.
> I've set up an active/active SQL cluster in our DR site and am using log
> shipping to keep the DR databases in sync with the production DBs;
> combined with MDAC aliases, non-standard TCP ports for SQL & DNS aliases
> for the server names to allow to very quick, transparent redirection of
> client connections to the DR servers in the event of a disaster. I also
> wrote a stored proc (that resides on our log shipping monitor box) to
> automate all the log shipping role changes (150+ databases is a bit too
> time consuming to do manually). Works pretty well too (we had a
> "disaster" in August this year when our SAN vendor stuffed up an upgrade
> on our production SAN - what a crappy weekend that was!)
> My only revision to my SQL DR plan/environment would be more RAM & CPU
> for the DR servers as I'm consolidating 4 production SQL instances down
> to the 2 DR instances on the active/active cluster (plus a few other DBs
> that don't live on our production clusters). The cluster nodes were
> pretty stressed for a week back in August but it's hard to justify
> spending the extra $$$ when the servers are really only used (hopefully)
> very infrequently (like one week in a year).
> Cheers,
> Mike.
> Cqlboy wrote:
>
|||This isn't a DR plan per say but does cover a lot of the areas you need to
be aware of.
http://www.microsoft.com/technet/pro...n/sqlops0.mspx
Operations Guide
Andrew J. Kelly SQL MVP
"Cqlboy" <Cqlboy@.discussions.microsoft.com> wrote in message
news:3ECD364A-14D7-42D5-B8B6-B44D3F6A95A9@.microsoft.com...[vbcol=seagreen]
> Thanx, this was helpful. But I was hoping to have something more explicit
> since it appears I'll have to create my DR document from scratch. A
> template
> or example to help guide my thinking and writing would be ideal. I'm not
> a
> technical write, eh?
> -Cqlboy
>
> "Mike Hodgson" wrote:
|||Requires free registration:
http://www.sqlservercentral.com/colu...eframework.asp
Other example links:
http://www.sql-server-performance.co...r_examples.asp
http://www.disasterrecoverysurvival...eryScript.html
http://sqljunkies.com/HowTo/F30B1E5F...CEAD46C79.scuk
And someone published a book, but I am not finding a reference to it at
the moment. Might check http://www.amazon.com or
http://www.techrepublic.com
Hope this helps,
Michelle
|||Hi Cqlboy,
For DR you have many options, and like in life, how much many so much music
: ) .
Important thing is how fast your server must be online?
If your business cannot wait, implement failover clustering, but this is
most expensive and redundant solution.
Also you have option of standby SQL server whish is connected to working SQL
server in the means of LOG shipping so if you have spare server, go for it.
If your business can wait some time you can use some sort off RAID to
prevent lose of data in case of disk failure and if you cannot afford RAID
you better have good backup strategy, which depends on how often your data
are changed.
So you can have only Full db backups, Full and Differential backups and
Backups off transaction log.
I tried to enumerate what I could remember.
Regards,
Daniel
"Cqlboy" <Cqlboy@.discussions.microsoft.com> wrote in message
news:8DA906E5-C877-4078-8F89-9A5A1787BE3E@.microsoft.com...
> I've never seen a disaster recovery plan for SQL Server. Can anyone one
> direct me to an example. This would be immensley helpful to understanding
> how to put one together. Thanx. -Cqlboy
|||Most of that is more fault tolerance than DR.
For example, a failover cluster is not a DR solution as both nodes need
to share the disk resources. Plus both nodes need to be pretty close to
each other (restricted by limitations of the IO transport protocols -
FC, SCSI - and also short latency of the heartbeat network between
nodes). If there is a real disaster (eg. a fire in your data
centre/computer room or a plane flies into the building where your
cluster is located) then you lose your production environment AND your
DR environment (and probably your job...if you're still alive). A DR
environment really needs to be shared-nothing (like log shipping to a
remote site or some 3rd party replication/geo-clustering technology, or
even standard MSSQL replication for the more budget conscious); you have
to assume you'll lose the entire production environment.
RAID will only protect disk loss (not CPU, RAM, NIC, switches and a
myriad of other potential hardware issues) and minimal loss at that; 1
disk in a RAID 5 array dies then fine, hotswap it; if 2 disks in a RAID
5 array die, or the whole drive cage or RAID controlled then you're up
the creek and need to have an outage (potentially significant (how many
people keep a spare drive cage or RAID controller handy?)) to replace
hardware).
The 3 most significant factors to consider in devising your DR strategy are:
1) maximum acceptable service disruption (down time)
2) maximum acceptable data loss (5min? 1hr? 1day?)
3) budget (the all important $$$)
The solution will always be a compromise in those 3 areas.
But this is getting off the topic a bit. The original question was
about documentation for pre-devised DR plans, which is a bit of big
call. While there are some guides (as people have provided in this
thread) to get you thinking about a solution, factors to consider and
how to go about implementing/documenting it, the solution itself will
require mostly your own brain (and probably several specific questions
posted to newsgroups to overcome potential issues that may arise).
Cheers,
Mike
Daniel Joskovski wrote:
> Hi Cqlboy,
> For DR you have many options, and like in life, how much many so much music
> : ) .
> Important thing is how fast your server must be online?
> If your business cannot wait, implement failover clustering, but this is
> most expensive and redundant solution.
> Also you have option of standby SQL server whish is connected to working SQL
> server in the means of LOG shipping so if you have spare server, go for it.
> If your business can wait some time you can use some sort off RAID to
> prevent lose of data in case of disk failure and if you cannot afford RAID
> you better have good backup strategy, which depends on how often your data
> are changed.
> So you can have only Full db backups, Full and Differential backups and
> Backups off transaction log.
> I tried to enumerate what I could remember.
> Regards,
> Daniel
> "Cqlboy" <Cqlboy@.discussions.microsoft.com> wrote in message
> news:8DA906E5-C877-4078-8F89-9A5A1787BE3E@.microsoft.com...
>
>
|||Here is the 'book' I had mentioned:
Disaster Recovery Toolkit for the SQL DBA
Written by SQL Server expert, Brian Knight, this handy, "how-to"
toolkit contains comprehensive first-hand advice and scripts for
SQL Server DBAs that need to build and implement a successful
disaster recovery plan. With his tips and quips, Brian walks the
DBA through real-world scenarios using an easy, step-by-step
approach. And as part of the download, you'll receive four
scripts, which will greatly speed your recovery time!
Download it today, compliments of Lumigent:
http://www.lumigent.com/go/sd19
Michelle
|||Hi Mike,
Thanks for your time, but from my experience, every time when I have
"disaster" it was connected with some hardware problem, and in 60% of cases
was a disk failure, using plain software mirror "saves" my life many times.
So building some anti nuclear shelter, and not having simple mirror this
days seems ridiculous to me. And nobody will blame me if plane flies in
computer room, but somebody can kill me if I don't have fresh copy of his
data. ;-)
Regards,
Daniel
"Mike Hodgson" <mwh_junk@.hotmail.com> wrote in message
news:evnIwUI6EHA.3124@.TK2MSFTNGP11.phx.gbl...
> Most of that is more fault tolerance than DR.
> For example, a failover cluster is not a DR solution as both nodes need
> to share the disk resources. Plus both nodes need to be pretty close to
> each other (restricted by limitations of the IO transport protocols -
> FC, SCSI - and also short latency of the heartbeat network between
> nodes). If there is a real disaster (eg. a fire in your data
> centre/computer room or a plane flies into the building where your
> cluster is located) then you lose your production environment AND your
> DR environment (and probably your job...if you're still alive). A DR
> environment really needs to be shared-nothing (like log shipping to a
> remote site or some 3rd party replication/geo-clustering technology, or
> even standard MSSQL replication for the more budget conscious); you have
> to assume you'll lose the entire production environment.
> RAID will only protect disk loss (not CPU, RAM, NIC, switches and a
> myriad of other potential hardware issues) and minimal loss at that; 1
> disk in a RAID 5 array dies then fine, hotswap it; if 2 disks in a RAID
> 5 array die, or the whole drive cage or RAID controlled then you're up
> the creek and need to have an outage (potentially significant (how many
> people keep a spare drive cage or RAID controller handy?)) to replace
> hardware).
> The 3 most significant factors to consider in devising your DR strategy
are:[vbcol=seagreen]
> 1) maximum acceptable service disruption (down time)
> 2) maximum acceptable data loss (5min? 1hr? 1day?)
> 3) budget (the all important $$$)
> The solution will always be a compromise in those 3 areas.
> But this is getting off the topic a bit. The original question was
> about documentation for pre-devised DR plans, which is a bit of big
> call. While there are some guides (as people have provided in this
> thread) to get you thinking about a solution, factors to consider and
> how to go about implementing/documenting it, the solution itself will
> require mostly your own brain (and probably several specific questions
> posted to newsgroups to overcome potential issues that may arise).
> Cheers,
> Mike
> Daniel Joskovski wrote:
music[vbcol=seagreen]
SQL[vbcol=seagreen]
it.[vbcol=seagreen]
RAID[vbcol=seagreen]
data[vbcol=seagreen]
understanding[vbcol=seagreen]
2012年2月24日星期五
disabling awe
hi,
I've disabled AWE in a server using :
sp_configure 'awe enabled', 0
RECONFIGURE
GO
The server has 4G in RAM, after disabling AWE how much is it ? 2 Gor 3G ?,
Where could I see it ?
I think I have to delete from boot.ini the "/3GB", how could I check that AWE
is disabled ?
Another question:
for sql server standar , is it possible to have AWE ?, how could I have my
server with 3G ? - my server has 4G in RAM.
thank
Hi
Standard Edition does not support AWE, so at best, it will use 1.7Gb of the
RAM on the machine. It will not use 2GB as it needs to leave memory for
"MemToLeave" (search Google if you want to know what that is)
Regards
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"ritta via droptable.com" <forum@.droptable.com> wrote in message
news:54C2A8986769C@.droptable.com...
> hi,
> I've disabled AWE in a server using :
> sp_configure 'awe enabled', 0
> RECONFIGURE
> GO
> The server has 4G in RAM, after disabling AWE how much is it ? 2 Gor 3G
> ?,
> Where could I see it ?
> I think I have to delete from boot.ini the "/3GB", how could I check that
> AWE
> is disabled ?
> --
> Another question:
> for sql server standar , is it possible to have AWE ?, how could I have my
> server with 3G ? - my server has 4G in RAM.
> thank
I've disabled AWE in a server using :
sp_configure 'awe enabled', 0
RECONFIGURE
GO
The server has 4G in RAM, after disabling AWE how much is it ? 2 Gor 3G ?,
Where could I see it ?
I think I have to delete from boot.ini the "/3GB", how could I check that AWE
is disabled ?
Another question:
for sql server standar , is it possible to have AWE ?, how could I have my
server with 3G ? - my server has 4G in RAM.
thank
Hi
Standard Edition does not support AWE, so at best, it will use 1.7Gb of the
RAM on the machine. It will not use 2GB as it needs to leave memory for
"MemToLeave" (search Google if you want to know what that is)
Regards
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"ritta via droptable.com" <forum@.droptable.com> wrote in message
news:54C2A8986769C@.droptable.com...
> hi,
> I've disabled AWE in a server using :
> sp_configure 'awe enabled', 0
> RECONFIGURE
> GO
> The server has 4G in RAM, after disabling AWE how much is it ? 2 Gor 3G
> ?,
> Where could I see it ?
> I think I have to delete from boot.ini the "/3GB", how could I check that
> AWE
> is disabled ?
> --
> Another question:
> for sql server standar , is it possible to have AWE ?, how could I have my
> server with 3G ? - my server has 4G in RAM.
> thank
2012年2月19日星期日
Disable Replication, remove rowguide-column?
I've notice that when I disable replication the rowguide column remains on
each table.
Is there an easy way to remove it?
Robert,
you'll need to do this manually. This is by design as your T-SQL code may
refer to the column, either directly or indirectly, so until the dependency
is removed, the column has to remain.
Regards,
Paul Ibison
|||You will have to do a Alter table drop column, You can create a script to
make this easier.
thanks
gopal
|||here is something. It will destroy all active publications and
subscriptions, make sure you drop them before running this.
exec sp_configure N'allow updates', 1
go
reconfigure with override
go
DECLARE @.name varchar(129)
DECLARE @.username varchar(129)
DECLARE @.insname varchar(129)
DECLARE @.delname varchar(129)
DECLARE @.updname varchar(129)
set @.insname=''
set @.updname=''
set @.delname=''
DECLARE list_triggers CURSOR FOR
select distinct replace(artid,'-',''), sysusers.name from
sysmergearticles,sysobjects, sysusers where
sysmergearticles.objid=sysobjects.id
and sysusers.uid=sysobjects.uid
OPEN list_triggers
FETCH NEXT FROM list_triggers INTO @.name, @.username
WHILE @.@.FETCH_STATUS = 0
BEGIN
PRINT 'dropping trigger ins_' +@.name
select @.insname='drop trigger ' +@.username+'.ins_'+@.name
exec (@.insname)
PRINT 'dropping trigger upd_' +@.name
select @.updname='drop trigger ' +@.username+'.upd_'+@.name
exec (@.delname)
PRINT 'dropping trigger del_' +@.name
select @.delname='drop trigger ' +@.username+'.del_'+@.name
exec (@.updname)
FETCH NEXT FROM list_triggers INTO @.name, @.username
END
CLOSE list_triggers
DEALLOCATE list_triggers
go
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[syspublications]') and OBJECTPROPERTY(id, N'IsUserTable')
= 1) begin DECLARE @.name varchar(129)
DECLARE list_pubs CURSOR FOR
SELECT name FROM syspublications
OPEN list_pubs
FETCH NEXT FROM list_pubs INTO @.name
WHILE @.@.FETCH_STATUS = 0
BEGIN
PRINT 'dropping publication ' +@.name
EXEC sp_dropsubscription @.publication=@.name, @.article='all', @.subscriber
='all'
EXEC sp_droppublication @.name
FETCH NEXT FROM list_pubs INTO @.name
END
CLOSE list_pubs
DEALLOCATE list_pubs
end
GO
DECLARE @.name varchar(129)
DECLARE list_replicated_tables CURSOR FOR
SELECT name FROM sysobjects WHERE replinfo <>0
UNION
SELECT name FROM sysmergearticles
OPEN list_replicated_tables
FETCH NEXT FROM list_replicated_tables INTO @.name
WHILE @.@.FETCH_STATUS = 0
BEGIN
PRINT 'unmarking replicated table ' +@.name
--select @.name='drop Table ' + @.name
EXEC sp_msunmarkreplinfo @.name
FETCH NEXT FROM list_replicated_tables INTO @.name
END
CLOSE list_replicated_tables
DEALLOCATE list_replicated_tables
GO
UPDATE syscolumns set colstat = colstat & ~4096 WHERE colstat &4096 <>0
GO
UPDATE sysobjects set replinfo=0
GO
DECLARE @.name nvarchar(129)
DECLARE list_views CURSOR FOR
SELECT name FROM sysobjects WHERE type='V' and (name like 'syncobj_%' or
name
like 'ctsv_%' or name like 'tsvw_%' or name like 'ms_bi%')
OPEN list_views
FETCH NEXT FROM list_views INTO @.name
WHILE @.@.FETCH_STATUS = 0
BEGIN
PRINT 'dropping View ' +@.name
select @.name='drop View ' + @.name
EXEC sp_executesql @.name
FETCH NEXT FROM list_views INTO @.name
END
CLOSE list_views
DEALLOCATE list_views
GO
DECLARE @.name nvarchar(129)
DECLARE list_procs CURSOR FOR
SELECT name FROM sysobjects WHERE type='p' and (name like 'sp_ins_%' or
name
like 'sp_MSdel_%' or name like 'sp_MSins_%'or name like 'sp_MSupd_%' or name
like 'sp_sel_%' or name like 'sp_upd_%')
OPEN list_procs
FETCH NEXT FROM list_procs INTO @.name
WHILE @.@.FETCH_STATUS = 0
BEGIN
PRINT 'dropping procs ' +@.name
select @.name='drop procedure ' + @.name
EXEC sp_executesql @.name
FETCH NEXT FROM list_procs INTO @.name
END
CLOSE list_procs
DEALLOCATE list_procs
GO
DECLARE @.name nvarchar(129)
DECLARE list_conflict_tables CURSOR FOR
SELECT name From sysobjects WHERE type='u' and name like '_onflict%'
OPEN list_conflict_tables
FETCH NEXT FROM list_conflict_tables INTO @.name
WHILE @.@.FETCH_STATUS = 0
BEGIN
PRINT 'dropping conflict_tables ' +@.name
select @.name='drop Table ' + @.name
EXEC sp_executesql @.name
FETCH NEXT FROM list_conflict_tables INTO @.name
END
CLOSE list_conflict_tables
DEALLOCATE list_conflict_tables
GO
UPDATE syscolumns set colstat=2 WHERE name='rowguid'
GO
Declare @.name nvarchar(200), @.constraint nvarchar(200)
DECLARE list_rowguid_constraints CURSOR FOR
select sysusers.name+'.'+object_name(sysobjects.parent_ob j), sysobjects.name
from sysobjects, syscolumns,sysusers where sysobjects.type ='d' and
syscolumns.id=sysobjects.parent_obj
and sysusers.uid=sysobjects.uid
and syscolumns.name='rowguid'
OPEN list_rowguid_constraints
FETCH NEXT FROM list_rowguid_constraints INTO @.name, @.constraint WHILE
@.@.FETCH_STATUS = 0 BEGIN
PRINT 'dropping rowguid constraints ' +@.name
select @.name='ALTER TABLE ' + rtrim(@.name) + ' DROP CONSTRAINT '
+@.constraint
print @.name
EXEC sp_executesql @.name
FETCH NEXT FROM list_rowguid_constraints INTO @.name, @.constraint END
CLOSE list_rowguid_constraints
DEALLOCATE list_rowguid_constraints
GO
Declare @.name nvarchar(129), @.constraint nvarchar(129)
DECLARE list_rowguid_indexes CURSOR FOR
select sysusers.name+'.'+object_name(sysindexes.id), sysindexes.name from
sysindexes, sysobjects,sysusers where sysindexes.name like 'index%' and
sysobjects.id=sysindexes.id and sysusers.uid=sysobjects.uid
OPEN list_rowguid_indexes
FETCH NEXT FROM list_rowguid_indexes INTO @.name, @.constraint WHILE
@.@.FETCH_STATUS = 0 BEGIN
PRINT 'dropping rowguid indexes ' +@.name
select @.name='drop index ' + rtrim(@.name ) + '.' +@.constraint
EXEC sp_executesql @.name
FETCH NEXT FROM list_rowguid_indexes INTO @.name, @.constraint END
CLOSE list_rowguid_indexes
DEALLOCATE list_rowguid_indexes
GO
Declare @.name nvarchar(129), @.constraint nvarchar(129)
DECLARE list_ms_bidi_tables CURSOR FOR
select sysusers.name+'.'+sysobjects.name from
sysobjects,sysusers where sysobjects.name like 'ms_bi%'
and sysusers.uid=sysobjects.uid
and sysobjects.type='u'
OPEN list_ms_bidi_tables
FETCH NEXT FROM list_ms_bidi_tables INTO @.name
WHILE @.@.FETCH_STATUS = 0
BEGIN
PRINT 'dropping ms_bidi ' +@.name
select @.name='drop table ' + rtrim(@.name )
EXEC sp_executesql @.name
FETCH NEXT FROM list_ms_bidi_tables INTO @.name
END
CLOSE list_ms_bidi_tables
DEALLOCATE list_ms_bidi_tables
GO
Declare @.name nvarchar(129)
DECLARE list_rowguid_columns CURSOR FOR
select sysusers.name+'.'+object_name(syscolumns.id) from syscolumns,
sysobjects,sysusers where syscolumns.name like 'rowguid' and
object_Name(sysobjects.id) not like 'msmerge%'
and sysobjects.id=syscolumns.id
and sysusers.uid=sysobjects.uid
and sysobjects.type='u' order by 1
OPEN list_rowguid_columns
FETCH NEXT FROM list_rowguid_columns INTO @.name
WHILE @.@.FETCH_STATUS = 0
BEGIN
PRINT 'dropping rowguid columns ' +@.name
select @.name='Alter Table ' + rtrim(@.name ) + ' drop column rowguid'
print @.name
EXEC sp_executesql @.name
FETCH NEXT FROM list_rowguid_columns INTO @.name
END
CLOSE list_rowguid_columns
DEALLOCATE list_rowguid_columns
go
Declare @.name nvarchar(129)
DECLARE list_views CURSOR FOR
select name From sysobjects where type ='v' and status =-1073741824 and name
<>'sysmergeextendedarticlesview'
OPEN list_views
FETCH NEXT FROM list_views INTO @.name
WHILE @.@.FETCH_STATUS = 0
BEGIN
PRINT 'dropping replication views ' +@.name
select @.name='drop view ' + rtrim(@.name )
print @.name
EXEC sp_executesql @.name
FETCH NEXT FROM list_views INTO @.name
END
CLOSE list_views
DEALLOCATE list_views
go
Declare @.name nvarchar(129)
DECLARE list_procs CURSOR FOR
select name From sysobjects where type ='p' and status = -536870912
OPEN list_procs
FETCH NEXT FROM list_procs INTO @.name
WHILE @.@.FETCH_STATUS = 0
BEGIN
PRINT 'dropping replication procedure ' +@.name
select @.name='drop procedure ' + rtrim(@.name )
print @.name
EXEC sp_executesql @.name
FETCH NEXT FROM list_procs INTO @.name
END
CLOSE list_procs
DEALLOCATE list_procs
go
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[sysmergepublications]') and OBJECTPROPERTY(id,
N'IsUserTable') = 1)
DELETE FROM sysmergepublications
GO
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[sysmergesubscriptions]') and OBJECTPROPERTY(id,
N'IsUserTable') = 1)
DELETE FROM sysmergesubscriptions
GO
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[syssubscriptions]') and OBJECTPROPERTY(id,
N'IsUserTable') = 1)
DELETE FROM syssubscriptions
GO
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[sysarticleupdates]') and OBJECTPROPERTY(id,
N'IsUserTable') = 1)
DELETE FROM sysarticleupdates
GO
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[systranschemas]') and OBJECTPROPERTY(id, N'IsUserTable')
= 1)
DELETE FROM systranschemas
GO
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[sysmergearticles]') and OBJECTPROPERTY(id,
N'IsUserTable') = 1)
DELETE FROM sysmergearticles
GO
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[sysmergeschemaarticles]') and OBJECTPROPERTY(id,
N'IsUserTable') = 1)
DELETE FROM sysmergeschemaarticles
GO
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[sysmergesubscriptions]') and OBJECTPROPERTY(id,
N'IsUserTable') = 1)
DELETE FROM sysmergesubscriptions
GO
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[sysarticles]') and OBJECTPROPERTY(id, N'IsUserTable') =
1)
DELETE FROM sysarticles
GO
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[sysschemaarticles]') and OBJECTPROPERTY(id,
N'IsUserTable') = 1)
DELETE FROM sysschemaarticles
GO
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[syspublications]') and OBJECTPROPERTY(id, N'IsUserTable')
= 1)
DELETE FROM syspublications
GO
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[sysmergeschemachange]') and OBJECTPROPERTY(id,
N'IsUserTable') = 1)
DELETE FROM sysmergeschemachange
GO
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[sysmergesubsetfilters]') and OBJECTPROPERTY(id,
N'IsUserTable') = 1)
DELETE FROM sysmergesubsetfilters
GO
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[MSdynamicsnapshotjobs]') and OBJECTPROPERTY(id,
N'IsUserTable') = 1)
DELETE FROM MSdynamicsnapshotjobs
GO
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[MSdynamicsnapshotviews]') and OBJECTPROPERTY(id,
N'IsUserTable') = 1)
DELETE FROM MSdynamicsnapshotviews
GO
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[MSmerge_altsyncpartners]') and OBJECTPROPERTY(id,
N'IsUserTable') = 1)
DELETE FROM MSmerge_altsyncpartners
GO
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[MSmerge_contents]') and OBJECTPROPERTY(id,
N'IsUserTable') = 1)
DELETE FROM MSmerge_contents
GO
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[MSmerge_delete_conflicts]') and OBJECTPROPERTY(id,
N'IsUserTable') = 1)
DELETE FROM MSmerge_delete_conflicts
GO
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[MSmerge_errorlineage]') and OBJECTPROPERTY(id,
N'IsUserTable') = 1)
DELETE FROM MSmerge_errorlineage
GO
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[MSmerge_genhistory]') and OBJECTPROPERTY(id,
N'IsUserTable') = 1)
DELETE FROM MSmerge_genhistory
GO
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[MSmerge_replinfo]') and OBJECTPROPERTY(id,
N'IsUserTable') = 1)
DELETE FROM MSmerge_replinfo
GO
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[MSmerge_tombstone]') and OBJECTPROPERTY(id,
N'IsUserTable') = 1)
DELETE FROM MSmerge_tombstone
GO
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[MSpub_identity_range]') and OBJECTPROPERTY(id,
N'IsUserTable') = 1)
DELETE FROM MSpub_identity_range
GO
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[MSrepl_identity_range]') and OBJECTPROPERTY(id,
N'IsUserTable') = 1)
DELETE FROM MSrepl_identity_range
GO
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[MSreplication_subscriptions]') and OBJECTPROPERTY(id,
N'IsUserTable') = 1)
DELETE FROM MSreplication_subscriptions
GO
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[MSsubscription_agents]') and OBJECTPROPERTY(id,
N'IsUserTable') = 1)
DELETE FROM MSsubscription_agents
GO
if not exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[syssubscriptions]') and OBJECTPROPERTY(id,
N'IsUserTable') = 1)
create table syssubscriptions (artid int, srvid smallint, dest_db sysname,
status tinyint, sync_type tinyint, login_name sysname, subscription_type
int, distribution_jobid binary, timestamp timestamp,update_mode tinyint,
loopback_detection tinyint, queued_reinit bit)
CREATE TABLE [dbo].[syspublications] (
[description] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[name] [sysname] NOT NULL ,
[pubid] [int] IDENTITY (1, 1) NOT NULL ,
[repl_freq] [tinyint] NOT NULL ,
[status] [tinyint] NOT NULL ,
[sync_method] [tinyint] NOT NULL ,
[snapshot_jobid] [binary] (16) NULL ,
[independent_agent] [bit] NOT NULL ,
[immediate_sync] [bit] NOT NULL ,
[enabled_for_internet] [bit] NOT NULL ,
[allow_push] [bit] NOT NULL ,
[allow_pull] [bit] NOT NULL ,
[allow_anonymous] [bit] NOT NULL ,
[immediate_sync_ready] [bit] NOT NULL ,
[allow_sync_tran] [bit] NOT NULL ,
[autogen_sync_procs] [bit] NOT NULL ,
[retention] [int] NULL ,
[allow_queued_tran] [bit] NOT NULL ,
[snapshot_in_defaultfolder] [bit] NOT NULL ,
[alt_snapshot_folder] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[pre_snapshot_script] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[post_snapshot_script] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[compress_snapshot] [bit] NOT NULL ,
[ftp_address] [sysname] NULL ,
[ftp_port] [int] NOT NULL ,
[ftp_subdirectory] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[ftp_login] [sysname] NULL ,
[ftp_password] [nvarchar] (524) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[allow_dts] [bit] NOT NULL ,
[allow_subscription_copy] [bit] NOT NULL ,
[centralized_conflicts] [bit] NULL ,
[conflict_retention] [int] NULL ,
[conflict_policy] [int] NULL ,
[queue_type] [int] NULL ,
[ad_guidname] [sysname] NULL ,
[backward_comp_level] [int] NOT NULL
) ON [PRIMARY]
GO
create view sysextendedarticlesview
as
SELECT *
FROM sysarticles
UNION ALL
SELECT artid, NULL, creation_script, NULL, description, dest_object,
NULL, NULL, NULL, name, objid, pubid, pre_creation_cmd, status, NULL, type,
NULL,
schema_option, dest_owner
FROM sysschemaarticles go
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[sysarticles]') and OBJECTPROPERTY(id, N'IsUserTable') =
1)
drop table [dbo].[sysarticles]
GO
CREATE TABLE [dbo].[sysarticles] (
[artid] [int] IDENTITY (1, 1) NOT NULL ,
[columns] [varbinary] (32) NULL ,
[creation_script] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
[del_cmd] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[description] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[dest_table] [sysname] NOT NULL ,
[filter] [int] NOT NULL ,
[filter_clause] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ins_cmd] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[name] [sysname] NOT NULL ,
[objid] [int] NOT NULL ,
[pubid] [int] NOT NULL ,
[pre_creation_cmd] [tinyint] NOT NULL ,
[status] [tinyint] NOT NULL ,
[sync_objid] [int] NOT NULL ,
[type] [tinyint] NOT NULL ,
[upd_cmd] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[schema_option] [binary] (8) NULL ,
[dest_owner] [sysname] NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[sysschemaarticles]') and OBJECTPROPERTY(id,
N'IsUserTable') = 1)
drop table [dbo].[sysschemaarticles]
GO
CREATE TABLE [dbo].[sysschemaarticles] (
[artid] [int] NOT NULL ,
[creation_script] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
[description] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[dest_object] [sysname] NOT NULL ,
[name] [sysname] NOT NULL ,
[objid] [int] NOT NULL ,
[pubid] [int] NOT NULL ,
[pre_creation_cmd] [tinyint] NOT NULL ,
[status] [int] NOT NULL ,
[type] [tinyint] NOT NULL ,
[schema_option] [binary] (8) NULL ,
[dest_owner] [sysname] NULL
) ON [PRIMARY]
GO
declare @.dbname varchar(130)
select @.dbname ='sp_replicationdboption
'+char(39)+db_name()+char(39)+',''merge publish'',''false'''
exec (@.dbname)
select @.dbname ='sp_replicationdboption
'+char(39)+db_name()+char(39)+',''publish'',''fals e'''
exec (@.dbname)
reconfigure with override
go
select db_name()
Hilary
973 254-8140
732 687-2264 (cell)
"Robert A. DiFrancesco" <bob.difrancesco@.comcash.com> wrote in message
news:#YDThPpEEHA.3976@.TK2MSFTNGP12.phx.gbl...
> I've notice that when I disable replication the rowguide column remains on
> each table.
> Is there an easy way to remove it?
>
|||Very impressive! - like sp_removedbreplication but a bit more comprehensive.
Regards,
Paul
|||this sp not clear the column guids... ;-)
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> escribi en el mensaje
news:e8Qb2JqEEHA.3096@.TK2MSFTNGP11.phx.gbl...
> Very impressive! - like sp_removedbreplication but a bit more
comprehensive.
> Regards,
> Paul
>
|||The script has a cursor to do this:
Declare @.name nvarchar(129)
DECLARE list_rowguid_columns CURSOR FOR
select sysusers.name+'.'+object_name(syscolumns.id) from syscolumns,
sysobjects,sysusers where syscolumns.name like 'rowguid' and
object_Name(sysobjects.id) not like 'msmerge%'
and sysobjects.id=syscolumns.id
and sysusers.uid=sysobjects.uid
and sysobjects.type='u' order by 1
OPEN list_rowguid_columns
FETCH NEXT FROM list_rowguid_columns INTO @.name
WHILE @.@.FETCH_STATUS = 0
BEGIN
PRINT 'dropping rowguid columns ' +@.name
select @.name='Alter Table ' + rtrim(@.name ) + ' drop column rowguid'
print @.name
EXEC sp_executesql @.name
FETCH NEXT FROM list_rowguid_columns INTO @.name
END
CLOSE list_rowguid_columns
DEALLOCATE list_rowguid_columns
go
I guess it's limited in the sense that the GUID column could be called
something else and it assumes merge replication, but that apart, it should
work.
Regards,
Paul Ibison
each table.
Is there an easy way to remove it?
Robert,
you'll need to do this manually. This is by design as your T-SQL code may
refer to the column, either directly or indirectly, so until the dependency
is removed, the column has to remain.
Regards,
Paul Ibison
|||You will have to do a Alter table drop column, You can create a script to
make this easier.
thanks
gopal
|||here is something. It will destroy all active publications and
subscriptions, make sure you drop them before running this.
exec sp_configure N'allow updates', 1
go
reconfigure with override
go
DECLARE @.name varchar(129)
DECLARE @.username varchar(129)
DECLARE @.insname varchar(129)
DECLARE @.delname varchar(129)
DECLARE @.updname varchar(129)
set @.insname=''
set @.updname=''
set @.delname=''
DECLARE list_triggers CURSOR FOR
select distinct replace(artid,'-',''), sysusers.name from
sysmergearticles,sysobjects, sysusers where
sysmergearticles.objid=sysobjects.id
and sysusers.uid=sysobjects.uid
OPEN list_triggers
FETCH NEXT FROM list_triggers INTO @.name, @.username
WHILE @.@.FETCH_STATUS = 0
BEGIN
PRINT 'dropping trigger ins_' +@.name
select @.insname='drop trigger ' +@.username+'.ins_'+@.name
exec (@.insname)
PRINT 'dropping trigger upd_' +@.name
select @.updname='drop trigger ' +@.username+'.upd_'+@.name
exec (@.delname)
PRINT 'dropping trigger del_' +@.name
select @.delname='drop trigger ' +@.username+'.del_'+@.name
exec (@.updname)
FETCH NEXT FROM list_triggers INTO @.name, @.username
END
CLOSE list_triggers
DEALLOCATE list_triggers
go
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[syspublications]') and OBJECTPROPERTY(id, N'IsUserTable')
= 1) begin DECLARE @.name varchar(129)
DECLARE list_pubs CURSOR FOR
SELECT name FROM syspublications
OPEN list_pubs
FETCH NEXT FROM list_pubs INTO @.name
WHILE @.@.FETCH_STATUS = 0
BEGIN
PRINT 'dropping publication ' +@.name
EXEC sp_dropsubscription @.publication=@.name, @.article='all', @.subscriber
='all'
EXEC sp_droppublication @.name
FETCH NEXT FROM list_pubs INTO @.name
END
CLOSE list_pubs
DEALLOCATE list_pubs
end
GO
DECLARE @.name varchar(129)
DECLARE list_replicated_tables CURSOR FOR
SELECT name FROM sysobjects WHERE replinfo <>0
UNION
SELECT name FROM sysmergearticles
OPEN list_replicated_tables
FETCH NEXT FROM list_replicated_tables INTO @.name
WHILE @.@.FETCH_STATUS = 0
BEGIN
PRINT 'unmarking replicated table ' +@.name
--select @.name='drop Table ' + @.name
EXEC sp_msunmarkreplinfo @.name
FETCH NEXT FROM list_replicated_tables INTO @.name
END
CLOSE list_replicated_tables
DEALLOCATE list_replicated_tables
GO
UPDATE syscolumns set colstat = colstat & ~4096 WHERE colstat &4096 <>0
GO
UPDATE sysobjects set replinfo=0
GO
DECLARE @.name nvarchar(129)
DECLARE list_views CURSOR FOR
SELECT name FROM sysobjects WHERE type='V' and (name like 'syncobj_%' or
name
like 'ctsv_%' or name like 'tsvw_%' or name like 'ms_bi%')
OPEN list_views
FETCH NEXT FROM list_views INTO @.name
WHILE @.@.FETCH_STATUS = 0
BEGIN
PRINT 'dropping View ' +@.name
select @.name='drop View ' + @.name
EXEC sp_executesql @.name
FETCH NEXT FROM list_views INTO @.name
END
CLOSE list_views
DEALLOCATE list_views
GO
DECLARE @.name nvarchar(129)
DECLARE list_procs CURSOR FOR
SELECT name FROM sysobjects WHERE type='p' and (name like 'sp_ins_%' or
name
like 'sp_MSdel_%' or name like 'sp_MSins_%'or name like 'sp_MSupd_%' or name
like 'sp_sel_%' or name like 'sp_upd_%')
OPEN list_procs
FETCH NEXT FROM list_procs INTO @.name
WHILE @.@.FETCH_STATUS = 0
BEGIN
PRINT 'dropping procs ' +@.name
select @.name='drop procedure ' + @.name
EXEC sp_executesql @.name
FETCH NEXT FROM list_procs INTO @.name
END
CLOSE list_procs
DEALLOCATE list_procs
GO
DECLARE @.name nvarchar(129)
DECLARE list_conflict_tables CURSOR FOR
SELECT name From sysobjects WHERE type='u' and name like '_onflict%'
OPEN list_conflict_tables
FETCH NEXT FROM list_conflict_tables INTO @.name
WHILE @.@.FETCH_STATUS = 0
BEGIN
PRINT 'dropping conflict_tables ' +@.name
select @.name='drop Table ' + @.name
EXEC sp_executesql @.name
FETCH NEXT FROM list_conflict_tables INTO @.name
END
CLOSE list_conflict_tables
DEALLOCATE list_conflict_tables
GO
UPDATE syscolumns set colstat=2 WHERE name='rowguid'
GO
Declare @.name nvarchar(200), @.constraint nvarchar(200)
DECLARE list_rowguid_constraints CURSOR FOR
select sysusers.name+'.'+object_name(sysobjects.parent_ob j), sysobjects.name
from sysobjects, syscolumns,sysusers where sysobjects.type ='d' and
syscolumns.id=sysobjects.parent_obj
and sysusers.uid=sysobjects.uid
and syscolumns.name='rowguid'
OPEN list_rowguid_constraints
FETCH NEXT FROM list_rowguid_constraints INTO @.name, @.constraint WHILE
@.@.FETCH_STATUS = 0 BEGIN
PRINT 'dropping rowguid constraints ' +@.name
select @.name='ALTER TABLE ' + rtrim(@.name) + ' DROP CONSTRAINT '
+@.constraint
print @.name
EXEC sp_executesql @.name
FETCH NEXT FROM list_rowguid_constraints INTO @.name, @.constraint END
CLOSE list_rowguid_constraints
DEALLOCATE list_rowguid_constraints
GO
Declare @.name nvarchar(129), @.constraint nvarchar(129)
DECLARE list_rowguid_indexes CURSOR FOR
select sysusers.name+'.'+object_name(sysindexes.id), sysindexes.name from
sysindexes, sysobjects,sysusers where sysindexes.name like 'index%' and
sysobjects.id=sysindexes.id and sysusers.uid=sysobjects.uid
OPEN list_rowguid_indexes
FETCH NEXT FROM list_rowguid_indexes INTO @.name, @.constraint WHILE
@.@.FETCH_STATUS = 0 BEGIN
PRINT 'dropping rowguid indexes ' +@.name
select @.name='drop index ' + rtrim(@.name ) + '.' +@.constraint
EXEC sp_executesql @.name
FETCH NEXT FROM list_rowguid_indexes INTO @.name, @.constraint END
CLOSE list_rowguid_indexes
DEALLOCATE list_rowguid_indexes
GO
Declare @.name nvarchar(129), @.constraint nvarchar(129)
DECLARE list_ms_bidi_tables CURSOR FOR
select sysusers.name+'.'+sysobjects.name from
sysobjects,sysusers where sysobjects.name like 'ms_bi%'
and sysusers.uid=sysobjects.uid
and sysobjects.type='u'
OPEN list_ms_bidi_tables
FETCH NEXT FROM list_ms_bidi_tables INTO @.name
WHILE @.@.FETCH_STATUS = 0
BEGIN
PRINT 'dropping ms_bidi ' +@.name
select @.name='drop table ' + rtrim(@.name )
EXEC sp_executesql @.name
FETCH NEXT FROM list_ms_bidi_tables INTO @.name
END
CLOSE list_ms_bidi_tables
DEALLOCATE list_ms_bidi_tables
GO
Declare @.name nvarchar(129)
DECLARE list_rowguid_columns CURSOR FOR
select sysusers.name+'.'+object_name(syscolumns.id) from syscolumns,
sysobjects,sysusers where syscolumns.name like 'rowguid' and
object_Name(sysobjects.id) not like 'msmerge%'
and sysobjects.id=syscolumns.id
and sysusers.uid=sysobjects.uid
and sysobjects.type='u' order by 1
OPEN list_rowguid_columns
FETCH NEXT FROM list_rowguid_columns INTO @.name
WHILE @.@.FETCH_STATUS = 0
BEGIN
PRINT 'dropping rowguid columns ' +@.name
select @.name='Alter Table ' + rtrim(@.name ) + ' drop column rowguid'
print @.name
EXEC sp_executesql @.name
FETCH NEXT FROM list_rowguid_columns INTO @.name
END
CLOSE list_rowguid_columns
DEALLOCATE list_rowguid_columns
go
Declare @.name nvarchar(129)
DECLARE list_views CURSOR FOR
select name From sysobjects where type ='v' and status =-1073741824 and name
<>'sysmergeextendedarticlesview'
OPEN list_views
FETCH NEXT FROM list_views INTO @.name
WHILE @.@.FETCH_STATUS = 0
BEGIN
PRINT 'dropping replication views ' +@.name
select @.name='drop view ' + rtrim(@.name )
print @.name
EXEC sp_executesql @.name
FETCH NEXT FROM list_views INTO @.name
END
CLOSE list_views
DEALLOCATE list_views
go
Declare @.name nvarchar(129)
DECLARE list_procs CURSOR FOR
select name From sysobjects where type ='p' and status = -536870912
OPEN list_procs
FETCH NEXT FROM list_procs INTO @.name
WHILE @.@.FETCH_STATUS = 0
BEGIN
PRINT 'dropping replication procedure ' +@.name
select @.name='drop procedure ' + rtrim(@.name )
print @.name
EXEC sp_executesql @.name
FETCH NEXT FROM list_procs INTO @.name
END
CLOSE list_procs
DEALLOCATE list_procs
go
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[sysmergepublications]') and OBJECTPROPERTY(id,
N'IsUserTable') = 1)
DELETE FROM sysmergepublications
GO
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[sysmergesubscriptions]') and OBJECTPROPERTY(id,
N'IsUserTable') = 1)
DELETE FROM sysmergesubscriptions
GO
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[syssubscriptions]') and OBJECTPROPERTY(id,
N'IsUserTable') = 1)
DELETE FROM syssubscriptions
GO
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[sysarticleupdates]') and OBJECTPROPERTY(id,
N'IsUserTable') = 1)
DELETE FROM sysarticleupdates
GO
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[systranschemas]') and OBJECTPROPERTY(id, N'IsUserTable')
= 1)
DELETE FROM systranschemas
GO
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[sysmergearticles]') and OBJECTPROPERTY(id,
N'IsUserTable') = 1)
DELETE FROM sysmergearticles
GO
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[sysmergeschemaarticles]') and OBJECTPROPERTY(id,
N'IsUserTable') = 1)
DELETE FROM sysmergeschemaarticles
GO
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[sysmergesubscriptions]') and OBJECTPROPERTY(id,
N'IsUserTable') = 1)
DELETE FROM sysmergesubscriptions
GO
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[sysarticles]') and OBJECTPROPERTY(id, N'IsUserTable') =
1)
DELETE FROM sysarticles
GO
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[sysschemaarticles]') and OBJECTPROPERTY(id,
N'IsUserTable') = 1)
DELETE FROM sysschemaarticles
GO
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[syspublications]') and OBJECTPROPERTY(id, N'IsUserTable')
= 1)
DELETE FROM syspublications
GO
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[sysmergeschemachange]') and OBJECTPROPERTY(id,
N'IsUserTable') = 1)
DELETE FROM sysmergeschemachange
GO
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[sysmergesubsetfilters]') and OBJECTPROPERTY(id,
N'IsUserTable') = 1)
DELETE FROM sysmergesubsetfilters
GO
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[MSdynamicsnapshotjobs]') and OBJECTPROPERTY(id,
N'IsUserTable') = 1)
DELETE FROM MSdynamicsnapshotjobs
GO
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[MSdynamicsnapshotviews]') and OBJECTPROPERTY(id,
N'IsUserTable') = 1)
DELETE FROM MSdynamicsnapshotviews
GO
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[MSmerge_altsyncpartners]') and OBJECTPROPERTY(id,
N'IsUserTable') = 1)
DELETE FROM MSmerge_altsyncpartners
GO
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[MSmerge_contents]') and OBJECTPROPERTY(id,
N'IsUserTable') = 1)
DELETE FROM MSmerge_contents
GO
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[MSmerge_delete_conflicts]') and OBJECTPROPERTY(id,
N'IsUserTable') = 1)
DELETE FROM MSmerge_delete_conflicts
GO
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[MSmerge_errorlineage]') and OBJECTPROPERTY(id,
N'IsUserTable') = 1)
DELETE FROM MSmerge_errorlineage
GO
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[MSmerge_genhistory]') and OBJECTPROPERTY(id,
N'IsUserTable') = 1)
DELETE FROM MSmerge_genhistory
GO
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[MSmerge_replinfo]') and OBJECTPROPERTY(id,
N'IsUserTable') = 1)
DELETE FROM MSmerge_replinfo
GO
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[MSmerge_tombstone]') and OBJECTPROPERTY(id,
N'IsUserTable') = 1)
DELETE FROM MSmerge_tombstone
GO
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[MSpub_identity_range]') and OBJECTPROPERTY(id,
N'IsUserTable') = 1)
DELETE FROM MSpub_identity_range
GO
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[MSrepl_identity_range]') and OBJECTPROPERTY(id,
N'IsUserTable') = 1)
DELETE FROM MSrepl_identity_range
GO
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[MSreplication_subscriptions]') and OBJECTPROPERTY(id,
N'IsUserTable') = 1)
DELETE FROM MSreplication_subscriptions
GO
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[MSsubscription_agents]') and OBJECTPROPERTY(id,
N'IsUserTable') = 1)
DELETE FROM MSsubscription_agents
GO
if not exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[syssubscriptions]') and OBJECTPROPERTY(id,
N'IsUserTable') = 1)
create table syssubscriptions (artid int, srvid smallint, dest_db sysname,
status tinyint, sync_type tinyint, login_name sysname, subscription_type
int, distribution_jobid binary, timestamp timestamp,update_mode tinyint,
loopback_detection tinyint, queued_reinit bit)
CREATE TABLE [dbo].[syspublications] (
[description] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[name] [sysname] NOT NULL ,
[pubid] [int] IDENTITY (1, 1) NOT NULL ,
[repl_freq] [tinyint] NOT NULL ,
[status] [tinyint] NOT NULL ,
[sync_method] [tinyint] NOT NULL ,
[snapshot_jobid] [binary] (16) NULL ,
[independent_agent] [bit] NOT NULL ,
[immediate_sync] [bit] NOT NULL ,
[enabled_for_internet] [bit] NOT NULL ,
[allow_push] [bit] NOT NULL ,
[allow_pull] [bit] NOT NULL ,
[allow_anonymous] [bit] NOT NULL ,
[immediate_sync_ready] [bit] NOT NULL ,
[allow_sync_tran] [bit] NOT NULL ,
[autogen_sync_procs] [bit] NOT NULL ,
[retention] [int] NULL ,
[allow_queued_tran] [bit] NOT NULL ,
[snapshot_in_defaultfolder] [bit] NOT NULL ,
[alt_snapshot_folder] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[pre_snapshot_script] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[post_snapshot_script] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[compress_snapshot] [bit] NOT NULL ,
[ftp_address] [sysname] NULL ,
[ftp_port] [int] NOT NULL ,
[ftp_subdirectory] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[ftp_login] [sysname] NULL ,
[ftp_password] [nvarchar] (524) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[allow_dts] [bit] NOT NULL ,
[allow_subscription_copy] [bit] NOT NULL ,
[centralized_conflicts] [bit] NULL ,
[conflict_retention] [int] NULL ,
[conflict_policy] [int] NULL ,
[queue_type] [int] NULL ,
[ad_guidname] [sysname] NULL ,
[backward_comp_level] [int] NOT NULL
) ON [PRIMARY]
GO
create view sysextendedarticlesview
as
SELECT *
FROM sysarticles
UNION ALL
SELECT artid, NULL, creation_script, NULL, description, dest_object,
NULL, NULL, NULL, name, objid, pubid, pre_creation_cmd, status, NULL, type,
NULL,
schema_option, dest_owner
FROM sysschemaarticles go
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[sysarticles]') and OBJECTPROPERTY(id, N'IsUserTable') =
1)
drop table [dbo].[sysarticles]
GO
CREATE TABLE [dbo].[sysarticles] (
[artid] [int] IDENTITY (1, 1) NOT NULL ,
[columns] [varbinary] (32) NULL ,
[creation_script] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
[del_cmd] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[description] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[dest_table] [sysname] NOT NULL ,
[filter] [int] NOT NULL ,
[filter_clause] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ins_cmd] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[name] [sysname] NOT NULL ,
[objid] [int] NOT NULL ,
[pubid] [int] NOT NULL ,
[pre_creation_cmd] [tinyint] NOT NULL ,
[status] [tinyint] NOT NULL ,
[sync_objid] [int] NOT NULL ,
[type] [tinyint] NOT NULL ,
[upd_cmd] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[schema_option] [binary] (8) NULL ,
[dest_owner] [sysname] NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[sysschemaarticles]') and OBJECTPROPERTY(id,
N'IsUserTable') = 1)
drop table [dbo].[sysschemaarticles]
GO
CREATE TABLE [dbo].[sysschemaarticles] (
[artid] [int] NOT NULL ,
[creation_script] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
[description] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[dest_object] [sysname] NOT NULL ,
[name] [sysname] NOT NULL ,
[objid] [int] NOT NULL ,
[pubid] [int] NOT NULL ,
[pre_creation_cmd] [tinyint] NOT NULL ,
[status] [int] NOT NULL ,
[type] [tinyint] NOT NULL ,
[schema_option] [binary] (8) NULL ,
[dest_owner] [sysname] NULL
) ON [PRIMARY]
GO
declare @.dbname varchar(130)
select @.dbname ='sp_replicationdboption
'+char(39)+db_name()+char(39)+',''merge publish'',''false'''
exec (@.dbname)
select @.dbname ='sp_replicationdboption
'+char(39)+db_name()+char(39)+',''publish'',''fals e'''
exec (@.dbname)
reconfigure with override
go
select db_name()
Hilary
973 254-8140
732 687-2264 (cell)
"Robert A. DiFrancesco" <bob.difrancesco@.comcash.com> wrote in message
news:#YDThPpEEHA.3976@.TK2MSFTNGP12.phx.gbl...
> I've notice that when I disable replication the rowguide column remains on
> each table.
> Is there an easy way to remove it?
>
|||Very impressive! - like sp_removedbreplication but a bit more comprehensive.
Regards,
Paul
|||this sp not clear the column guids... ;-)
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> escribi en el mensaje
news:e8Qb2JqEEHA.3096@.TK2MSFTNGP11.phx.gbl...
> Very impressive! - like sp_removedbreplication but a bit more
comprehensive.
> Regards,
> Paul
>
|||The script has a cursor to do this:
Declare @.name nvarchar(129)
DECLARE list_rowguid_columns CURSOR FOR
select sysusers.name+'.'+object_name(syscolumns.id) from syscolumns,
sysobjects,sysusers where syscolumns.name like 'rowguid' and
object_Name(sysobjects.id) not like 'msmerge%'
and sysobjects.id=syscolumns.id
and sysusers.uid=sysobjects.uid
and sysobjects.type='u' order by 1
OPEN list_rowguid_columns
FETCH NEXT FROM list_rowguid_columns INTO @.name
WHILE @.@.FETCH_STATUS = 0
BEGIN
PRINT 'dropping rowguid columns ' +@.name
select @.name='Alter Table ' + rtrim(@.name ) + ' drop column rowguid'
print @.name
EXEC sp_executesql @.name
FETCH NEXT FROM list_rowguid_columns INTO @.name
END
CLOSE list_rowguid_columns
DEALLOCATE list_rowguid_columns
go
I guess it's limited in the sense that the GUID column could be called
something else and it assumes merge replication, but that apart, it should
work.
Regards,
Paul Ibison
disable publishing and distribution error
Ok, which database are you creating sysmergepublications and
sysmergesubscriptions in ?
I've created sysmergesubscriptions in distribution , master , msdb ...
I still receive an error when I run
use master
exec sp_dropdistributor @.no_checks = 1
go
I receive :
Msg 208, Level 16, State 1, Procedure sp_MSmergepublishdb, Line 103
Invalid object name 'dbo.sysmergesubscriptions'.
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:%233PUslE$GHA.896@.TK2MSFTNGP03.phx.gbl...
> create table sysmergepublications
> (
> publisher sysname,
> publisher_db sysname,
> name sysname,
> description nvarchar(510),
> retention int,
> publication_type tinyint,
> pubid uniqueidentifier,
> designmasterid uniqueidentifier,
> parentid uniqueidentifier,
> sync_mode tinyint,
> allow_push int,
> allow_pull int,
> allow_anonymous int,
> centralized_conflicts int,
> status tinyint,
> snapshot_ready tinyint,
> enabled_for_internet bit,
> dynamic_filters bit,
> snapshot_in_defaultfolder bit,
> alt_snapshot_folder nvarchar(510),
> pre_snapshot_script nvarchar(510),
> post_snapshot_script nvarchar(510),
> compress_snapshot bit,
> ftp_address sysname,
> ftp_port int,
> ftp_subdirectory nvarchar(510),
> ftp_login sysname,
> ftp_password nvarchar(1048),
> conflict_retention int,
> keep_before_values int,
> allow_subscription_copy bit,
> allow_synctoalternate bit,
> validate_subscriber_info nvarchar(1000),
> ad_guidname sysname,
> backward_comp_level int,
> max_concurrent_merge int,
> max_concurrent_dynamic_snapshots int,
> use_partition_groups smallint,
> dynamic_filters_function_list nvarchar(1000),
> partition_id_eval_proc sysname,
> publication_number smallint,
> replicate_ddl int,
> allow_subscriber_initiated_snapshot bit,
> distributor sysname,
> snapshot_jobid binary(16),
> allow_web_synchronization bit,
> web_synchronization_url nvarchar(1000),
> allow_partition_realignment bit,
> retention_period_unit tinyint,
> decentralized_conflicts int,
> generation_leveling_threshold int,
> automatic_reinitialization_policy bit
> )
> --
> Hilary Cotter
> Director of Text Mining and Database Strategy
> RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
> This posting is my own and doesn't necessarily represent RelevantNoise's
> positions, strategies or opinions.
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
>
> "John A Grandy" <johnagrandy-at-yahoo-dot-com> wrote in message
> news:%23exvteE$GHA.4196@.TK2MSFTNGP03.phx.gbl...
>
Publication database. Check which databases are published for merge
replication and put it there.
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"John A Grandy" <johnagrandy-at-yahoo-dot-com> wrote in message
news:uyEq9AQBHHA.4672@.TK2MSFTNGP02.phx.gbl...
> Ok, which database are you creating sysmergepublications and
> sysmergesubscriptions in ?
> I've created sysmergesubscriptions in distribution , master , msdb ...
> I still receive an error when I run
> use master
> exec sp_dropdistributor @.no_checks = 1
> go
> I receive :
> Msg 208, Level 16, State 1, Procedure sp_MSmergepublishdb, Line 103
> Invalid object name 'dbo.sysmergesubscriptions'.
>
> "Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
> news:%233PUslE$GHA.896@.TK2MSFTNGP03.phx.gbl...
>
|||My database is no longer being published for replication because I used
exec sp_replicationdboption 'databasename','merge publish',false
Nonetheless, I added a dbo.sysmergesubscriptions table to this database and
ran
use master
exec sp_dropdistributor @.no_checks = 1
go
but I still receive the error :
Msg 208, Level 16, State 1, Procedure sp_MSmergepublishdb, Line 103
Invalid object name 'dbo.sysmergesubscriptions'.
Could it be because I need to add dbo.sysmergesubscriptions as a system
object ? How do I do this ?
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:eZifFJQBHHA.2304@.TK2MSFTNGP02.phx.gbl...
> Publication database. Check which databases are published for merge
> replication and put it there.
> --
> Hilary Cotter
> Director of Text Mining and Database Strategy
> RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
> This posting is my own and doesn't necessarily represent RelevantNoise's
> positions, strategies or opinions.
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
>
> "John A Grandy" <johnagrandy-at-yahoo-dot-com> wrote in message
> news:uyEq9AQBHHA.4672@.TK2MSFTNGP02.phx.gbl...
>
|||these are the lines around 103
if not exists (select * from dbo.sysmergesubscriptions
where UPPER(subscriber_server) =
UPPER(publishingservername()) and db_name = db_name() and subid <> pubid)
begin
select @.ignore_merge_metadata = 1
end
It is complaining about the database you are running the command in. Is the
table there? Does it have the owner dbo?
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"John A Grandy" <johnagrandy-at-yahoo-dot-com> wrote in message
news:ePkGbNQBHHA.1220@.TK2MSFTNGP04.phx.gbl...
> My database is no longer being published for replication because I used
> exec sp_replicationdboption 'databasename','merge publish',false
> Nonetheless, I added a dbo.sysmergesubscriptions table to this database
> and ran
> use master
> exec sp_dropdistributor @.no_checks = 1
> go
> but I still receive the error :
> Msg 208, Level 16, State 1, Procedure sp_MSmergepublishdb, Line 103
> Invalid object name 'dbo.sysmergesubscriptions'.
> Could it be because I need to add dbo.sysmergesubscriptions as a system
> object ? How do I do this ?
>
> "Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
> news:eZifFJQBHHA.2304@.TK2MSFTNGP02.phx.gbl...
>
sysmergesubscriptions in ?
I've created sysmergesubscriptions in distribution , master , msdb ...
I still receive an error when I run
use master
exec sp_dropdistributor @.no_checks = 1
go
I receive :
Msg 208, Level 16, State 1, Procedure sp_MSmergepublishdb, Line 103
Invalid object name 'dbo.sysmergesubscriptions'.
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:%233PUslE$GHA.896@.TK2MSFTNGP03.phx.gbl...
> create table sysmergepublications
> (
> publisher sysname,
> publisher_db sysname,
> name sysname,
> description nvarchar(510),
> retention int,
> publication_type tinyint,
> pubid uniqueidentifier,
> designmasterid uniqueidentifier,
> parentid uniqueidentifier,
> sync_mode tinyint,
> allow_push int,
> allow_pull int,
> allow_anonymous int,
> centralized_conflicts int,
> status tinyint,
> snapshot_ready tinyint,
> enabled_for_internet bit,
> dynamic_filters bit,
> snapshot_in_defaultfolder bit,
> alt_snapshot_folder nvarchar(510),
> pre_snapshot_script nvarchar(510),
> post_snapshot_script nvarchar(510),
> compress_snapshot bit,
> ftp_address sysname,
> ftp_port int,
> ftp_subdirectory nvarchar(510),
> ftp_login sysname,
> ftp_password nvarchar(1048),
> conflict_retention int,
> keep_before_values int,
> allow_subscription_copy bit,
> allow_synctoalternate bit,
> validate_subscriber_info nvarchar(1000),
> ad_guidname sysname,
> backward_comp_level int,
> max_concurrent_merge int,
> max_concurrent_dynamic_snapshots int,
> use_partition_groups smallint,
> dynamic_filters_function_list nvarchar(1000),
> partition_id_eval_proc sysname,
> publication_number smallint,
> replicate_ddl int,
> allow_subscriber_initiated_snapshot bit,
> distributor sysname,
> snapshot_jobid binary(16),
> allow_web_synchronization bit,
> web_synchronization_url nvarchar(1000),
> allow_partition_realignment bit,
> retention_period_unit tinyint,
> decentralized_conflicts int,
> generation_leveling_threshold int,
> automatic_reinitialization_policy bit
> )
> --
> Hilary Cotter
> Director of Text Mining and Database Strategy
> RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
> This posting is my own and doesn't necessarily represent RelevantNoise's
> positions, strategies or opinions.
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
>
> "John A Grandy" <johnagrandy-at-yahoo-dot-com> wrote in message
> news:%23exvteE$GHA.4196@.TK2MSFTNGP03.phx.gbl...
>
Publication database. Check which databases are published for merge
replication and put it there.
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"John A Grandy" <johnagrandy-at-yahoo-dot-com> wrote in message
news:uyEq9AQBHHA.4672@.TK2MSFTNGP02.phx.gbl...
> Ok, which database are you creating sysmergepublications and
> sysmergesubscriptions in ?
> I've created sysmergesubscriptions in distribution , master , msdb ...
> I still receive an error when I run
> use master
> exec sp_dropdistributor @.no_checks = 1
> go
> I receive :
> Msg 208, Level 16, State 1, Procedure sp_MSmergepublishdb, Line 103
> Invalid object name 'dbo.sysmergesubscriptions'.
>
> "Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
> news:%233PUslE$GHA.896@.TK2MSFTNGP03.phx.gbl...
>
|||My database is no longer being published for replication because I used
exec sp_replicationdboption 'databasename','merge publish',false
Nonetheless, I added a dbo.sysmergesubscriptions table to this database and
ran
use master
exec sp_dropdistributor @.no_checks = 1
go
but I still receive the error :
Msg 208, Level 16, State 1, Procedure sp_MSmergepublishdb, Line 103
Invalid object name 'dbo.sysmergesubscriptions'.
Could it be because I need to add dbo.sysmergesubscriptions as a system
object ? How do I do this ?
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:eZifFJQBHHA.2304@.TK2MSFTNGP02.phx.gbl...
> Publication database. Check which databases are published for merge
> replication and put it there.
> --
> Hilary Cotter
> Director of Text Mining and Database Strategy
> RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
> This posting is my own and doesn't necessarily represent RelevantNoise's
> positions, strategies or opinions.
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
>
> "John A Grandy" <johnagrandy-at-yahoo-dot-com> wrote in message
> news:uyEq9AQBHHA.4672@.TK2MSFTNGP02.phx.gbl...
>
|||these are the lines around 103
if not exists (select * from dbo.sysmergesubscriptions
where UPPER(subscriber_server) =
UPPER(publishingservername()) and db_name = db_name() and subid <> pubid)
begin
select @.ignore_merge_metadata = 1
end
It is complaining about the database you are running the command in. Is the
table there? Does it have the owner dbo?
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"John A Grandy" <johnagrandy-at-yahoo-dot-com> wrote in message
news:ePkGbNQBHHA.1220@.TK2MSFTNGP04.phx.gbl...
> My database is no longer being published for replication because I used
> exec sp_replicationdboption 'databasename','merge publish',false
> Nonetheless, I added a dbo.sysmergesubscriptions table to this database
> and ran
> use master
> exec sp_dropdistributor @.no_checks = 1
> go
> but I still receive the error :
> Msg 208, Level 16, State 1, Procedure sp_MSmergepublishdb, Line 103
> Invalid object name 'dbo.sysmergesubscriptions'.
> Could it be because I need to add dbo.sysmergesubscriptions as a system
> object ? How do I do this ?
>
> "Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
> news:eZifFJQBHHA.2304@.TK2MSFTNGP02.phx.gbl...
>
订阅：
博文 (Atom)