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

Restore & Replication Question

I think I have seen this question posted before, and I apologize if I am being redundant.
On a test server I 'refresh' the data in a database by restoring it from a production database. The test database that I am refreshing replicates to another database on the same server.
What is the best method for maintaining the replication setting of the test database?
Do I need to drop the replication, do the restore, then set replication back up from scratch?
If there is a scripting method, is it just a matter of right clicking on the publication and selecting generate sql script?
Thanx!
JLS,
if you are restoring a backup of a database which doesn't have the
replication settings on top of your test system, then you'll need to
recreate the publications (from the scripted publications on the test
system) and then reinitialize your subscribers.
HTH,
Paul Ibison
|||Redo the publication - Ok, Thanx!
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:OSk2ctCaEHA.3244@.TK2MSFTNGP12.phx.gbl...
> JLS,
> if you are restoring a backup of a database which doesn't have the
> replication settings on top of your test system, then you'll need to
> recreate the publications (from the scripted publications on the test
> system) and then reinitialize your subscribers.
> HTH,
> Paul Ibison
>

Restore

I have two way transactional replication between Hq and other partner
countries. I want to create same enviornment in a lab for testing for
upgrade from SQL7.0 to 2000. Transactional replication is replicating same
tables and same rows and columns both ways;subscriptions are not updatble.
Now I have to take backup of all databases and restore them in the lab
enviornment and then run replicatioln script to create replication in the lab
enviornment. My question is in which order I should backup databases to keep
them synchonized. Shold I backup the HQ first or partner countries? Both
are publisher as well as subscriners for the same tables.
thanks
unless you can kick all of your users off both databases you can't. If you
are interested in a proof of concept, I would take the publication database,
restore it to the lab publisher and lab subscriber and re-run the
replication scripts.
If you do attemp to do the backups you will start from inconsistent
databases which is not the end of the world, you just have to fight with
the data consistency errors which may occur.
Hilary Cotter
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
"Mintu" <Mintu@.discussions.microsoft.com> wrote in message
news:08C1C282-A281-4E73-8645-FE7DAC1F6899@.microsoft.com...
> I have two way transactional replication between Hq and other partner
> countries. I want to create same enviornment in a lab for testing for
> upgrade from SQL7.0 to 2000. Transactional replication is replicating
same
> tables and same rows and columns both ways;subscriptions are not updatble.
> Now I have to take backup of all databases and restore them in the lab
> enviornment and then run replicatioln script to create replication in the
lab
> enviornment. My question is in which order I should backup databases to
keep
> them synchonized. Shold I backup the HQ first or partner countries? Both
> are publisher as well as subscriners for the same tables.
> thanks
|||What errors are most likely to occur and how do I resolve them? Should I use
sp_restartrepl or sp_resetrepl to let the replication continue even database
are not in consistance state?
thanks,
Mintu
"Hilary Cotter" wrote:

> unless you can kick all of your users off both databases you can't. If you
> are interested in a proof of concept, I would take the publication database,
> restore it to the lab publisher and lab subscriber and re-run the
> replication scripts.
> If you do attemp to do the backups you will start from inconsistent
> databases which is not the end of the world, you just have to fight with
> the data consistency errors which may occur.
> --
> Hilary Cotter
> 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
> "Mintu" <Mintu@.discussions.microsoft.com> wrote in message
> news:08C1C282-A281-4E73-8645-FE7DAC1F6899@.microsoft.com...
> same
> lab
> keep
>
>
|||Is anybody aware of common problems with this approch and how to resolve them?
thanks,
Mintu
"Hilary Cotter" wrote:

> unless you can kick all of your users off both databases you can't. If you
> are interested in a proof of concept, I would take the publication database,
> restore it to the lab publisher and lab subscriber and re-run the
> replication scripts.
> If you do attemp to do the backups you will start from inconsistent
> databases which is not the end of the world, you just have to fight with
> the data consistency errors which may occur.
> --
> Hilary Cotter
> 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
> "Mintu" <Mintu@.discussions.microsoft.com> wrote in message
> news:08C1C282-A281-4E73-8645-FE7DAC1F6899@.microsoft.com...
> same
> lab
> keep
>
>