restore a replicated db to a sql server with no replication

Hi,
I want to take my replicated db SQL 7 and put a data only version of
it on to a dev box.
I remember doing this with no problems in the past, but I did it
yesterday and it took forever with lots of fiddling about. The
restore in EM never actually completed.
I am now trying to run my code on this db and I am getting the
following error:
e.g. in Query analyser I ran:
update users set usr_password = 'password'
error:
Server: Msg 18750, Level 16, State 4, Procedure
upd_DA46F6B9F9D348FE9419EB7A8FCCECFD, Line 51
INITCOLVS: The parameter 'nickname' is invalid.
Server: Msg 3621, Level 16, State 1, Procedure
upd_DA46F6B9F9D348FE9419EB7A8FCCECFD, Line 51
The statement has been terminated.
The statement has been terminated.
Is there a way I can back up the db from my production server and not
include any of the replication info so it can operate cleanly on my
dev server?
Thanks in advance.
Install replication, enable the database for replication. Restore the
database using the keep_replication switch. Then drop the publications.
Otherwise run this script.
http://groups.google.com/groups?selm...&output=gplain
--
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"Duncan" <duncan.loxton@.gmail.com> wrote in message
news:7315f62a.0411221556.509f8fcb@.posting.google.c om...
> Hi,
> I want to take my replicated db SQL 7 and put a data only version of
> it on to a dev box.
> I remember doing this with no problems in the past, but I did it
> yesterday and it took forever with lots of fiddling about. The
> restore in EM never actually completed.
> I am now trying to run my code on this db and I am getting the
> following error:
> e.g. in Query analyser I ran:
> update users set usr_password = 'password'
> error:
> Server: Msg 18750, Level 16, State 4, Procedure
> upd_DA46F6B9F9D348FE9419EB7A8FCCECFD, Line 51
> INITCOLVS: The parameter 'nickname' is invalid.
> Server: Msg 3621, Level 16, State 1, Procedure
> upd_DA46F6B9F9D348FE9419EB7A8FCCECFD, Line 51
> The statement has been terminated.
> The statement has been terminated.
> Is there a way I can back up the db from my production server and not
> include any of the replication info so it can operate cleanly on my
> dev server?
> Thanks in advance.
|||SQL Server 7.0 (I am assuming you are restoring to a SQL Server 7.0 server)
does not have a KEEP_REPLICATION option for the restore command. You can
try running sp_removedbreplication in the databse. However, this may not
remove everything. The error is mostly caused by a trigger on the table you
are trying to update. If so you can drop the trigger in Enterprise Manager.
Rand
This posting is provided "as is" with no warranties and confers no rights.
|||Correct - I am trying to restore to a SQL 7 server.
I ran the script and sp_removedbreplication. The script reported the
following error (amongst others:
dropping conflict_tables conflict_MPlus Live_Exchange
Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near 'Live_Exchange'.
dropping rowguid constraints conflict_MPlus Live_Clients
Server: Msg 156, Level 15, State 1, Line 2
Incorrect syntax near the keyword 'DROP'.
dropping rowguid columns conflict_MPlus Live_ListingLibraryType
Alter Table conflict_MPlus Live_ListingLibraryType
drop column rowguid
Server: Msg 156, Level 15, State 1, Line 2
Incorrect syntax near the keyword 'drop'.
and the sp_removedbreplication completed without error.
Using my example from before I am now encountering the following error
when running update users set usr_password = 'password'
Server: Msg 208, Level 16, State 1, Procedure
upd_DA46F6B9F9D348FE9419EB7A8FCCECFD, Line 16
Invalid object name 'dbo.sysmergearticles'.
So it appears the objects are not there, although it thinks that they
are.
There must be a better way to get a backup of a replicated db and
restore it with no replication.....
Any other feedback I might try? Thanks for your help.
|||Right - I have dropped the triggers on a couple of tables and things are
looking up!
I dont want to have to go through 500 tables and drop 3 triggers on each one
manually. How can I get rid of them programatically?
|||Thanks for the clarification Rand! I missed the SQL 7.0 part.
Hilary Cotter
Looking for a SQL Server replication book?
Now available for purchase at:
http://www.nwsu.com/0974973602.html
"Rand Boyd [MSFT]" <rboyd@.onlinemicrosoft.com> wrote in message
news:8QUe5SX0EHA.3436@.cpmsftngxa10.phx.gbl...
> SQL Server 7.0 (I am assuming you are restoring to a SQL Server 7.0
> server)
> does not have a KEEP_REPLICATION option for the restore command. You can
> try running sp_removedbreplication in the databse. However, this may not
> remove everything. The error is mostly caused by a trigger on the table
> you
> are trying to update. If so you can drop the trigger in Enterprise
> Manager.
> Rand
> This posting is provided "as is" with no warranties and confers no rights.
>
|||try this:
http://groups.google.com/groups?selm...&output=gplain
Hilary Cotter
Looking for a SQL Server replication book?
Now available for purchase at:
http://www.nwsu.com/0974973602.html
"Duncan" <duncan.loxton@.gmail.com> wrote in message
news:30htnvF30vspdU1@.uni-berlin.de...
> Right - I have dropped the triggers on a couple of tables and things are
> looking up!
> I dont want to have to go through 500 tables and drop 3 triggers on each
> one manually. How can I get rid of them programatically?
>
|||try this script. Be careful about running it as it will destroy any active
subscriptions or publications you may have in this database.
http://groups.google.com/groups?selm...&output=gplain
Hilary Cotter
Looking for a SQL Server replication book?
Now available for purchase at:
http://www.nwsu.com/0974973602.html
"Duncan" <duncan.loxton@.gmail.com> wrote in message
news:30htnvF30vspdU1@.uni-berlin.de...
> Right - I have dropped the triggers on a couple of tables and things are
> looking up!
> I dont want to have to go through 500 tables and drop 3 triggers on each
> one manually. How can I get rid of them programatically?
>
|||Hilary,
Thanks for this scipt - this is all a bit out of my league now. However I
think that the replication has been removed, its just the triggers on the
individual table I need to root out.
I tried it but here is a portion of the output:
Server: Msg 208, Level 16, State 1, Line 3
Invalid object name 'syspublications'.
Server: Msg 208, Level 16, State 1, Line 1
Invalid object name 'sysmergepublications'.
Server: Msg 208, Level 16, State 1, Line 1
Invalid object name 'sysmergesubscriptions'.
Server: Msg 208, Level 16, State 1, Line 1
~~~~~~~~~~~~
Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near 'Live_MyProspectiveTenants'.
dropping conflict_tables conflict_MPlus Live_object
Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near 'Live_object'.
dropping conflict_tables conflict_MPlus Live_ListingCommissionSplit
Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near 'Live_ListingCommissionSplit'.
dropping conflict_tables conflict_MPlus Live_Age
Server: Msg 170, Level 15, State 1, Line 1
~~~~~~~~~
drop column rowguid
Server: Msg 156, Level 15, State 1, Line 2
Incorrect syntax near the keyword 'drop'.
dropping rowguid columns conflict_MPlus Live_ClientOpenDay
Alter Table conflict_MPlus Live_ClientOpenDay
drop column rowguid
Server: Msg 156, Level 15, State 1, Line 2
Incorrect syntax near the keyword 'drop'.
dropping rowguid columns conflict_MPlus Live_ClientPartner
Alter Table conflict_MPlus Live_ClientPartner
drop column rowguid