Can't find NOT FOR REPLICATION option

Create the subscription table manually using the IDENTITY property and
the NOT FOR REPLICATION option.
Dan,
it looks like your post is a reply to an earlier post/thread, and all I have
to go on is the title but if you need to script out a table with this
attribute it should look something like this:
CREATE TABLE [dbo].[TestIdent] (
[ID] [int] IDENTITY (1, 1) NOT FOR REPLICATION NOT NULL ,
[descr] [varchar] (50) COLLATE Latin1_General_CI_AS NULL ,
[rowguid] uniqueidentifier ROWGUIDCOL NOT NULL
) ON [PRIMARY]
GO
BTW, if this is a transactional nosync initialization, used with a view to
using the identity property on the subscriber in a failover situation, you
should consider initializing with queued updating subscribers instead, as
the internal identity number will not get incremented on the subscriber and
DBCC CHECKIDENT can't be used on columns set with this attribute to reseed
it.
HTH,
Paul Ibison
|||Thanks Paul, this is actually my first post about this, I just copied
the message SQL gave me when trying to setup a snapshot replication. I
looked all over Enterprise Manager but couldn't find an option "NOT FOR
REPLICATION". I guess you can only do it via SQL script.
FYI, I am just trying to reset a test DB back to production state every
night at midnight (after they play in the test DB all day). Nothing
should replicate to the production server......only from the
productions server to the test server. Hope I am headed in the right
direction.
Thanks again,
Dan
*** Sent via Devdex http://www.devdex.com ***
Don't just participate in USENET...get rewarded for it!
|||Dan,
you are on the right track. What you are doing is called a nosync
initialization. You need to script out the tables and create them on the
subscriber before initializing and change the article properties so as to
not drop the table on the subscriber during a name conflict. The setting
"NOT FOR
REPLICATION" can be done in EM. It is on an identity's column in table
design, but can equally be done in a script.
Snapshot replication is good for this, but 'Database Shipping' can equally
be used and also takes users and permissions that your application might
require. It also saves you from adding articles as the application develops.
HTH,
Paul Ibison
|||Can I just create a blank database on the test server and restore the
last backup over top of the blank DB? Then just change each IDENITY
column to NOT FOR REPLICATION?
Thanks for all the help,
Dan
*** Sent via Devdex http://www.devdex.com ***
Don't just participate in USENET...get rewarded for it!
|||Dan,
if you mean a nosync initialization, this doesn't work, as when your
subscriber is ultimately being used as a test machine, your identity values
will clash. If the identity columns are used for PKs then you will end up
getting PK violations. This is because replication will not increment the
identity value when records are added to the subscriber and DBCC CHECKIDENT
cannot be used to reseed the identity value. I'd consider shipping database
backups for your scenario. You could alternatively avoid these problems by
using merge or transactional with queued updating subscribers, but there
will be a lot of unnecessary work going on behind the scenes on your
production server which you really don't want.
HTH,
Paul Ibison
|||So replication will not increment the identity value when records are
added to the subscriber? What value, if any, gets put in the identity
field during replication?
How does the shipping database option work?
*** Sent via Devdex http://www.devdex.com ***
Don't just participate in USENET...get rewarded for it!
|||Dan,
true - replication doesn't increment the internal identity value - it stays
as the initial seed. Don't confuse this with the population of the column
during replication which works OK, essentially doing an identity insert.
This setting is really used for updating subscribers - snapshot or
transactional, or merge.
By database shipping I was thinking of doing a backup of the production
database, copying it over to the test server and restoring it there. You'll
need to create your own jobs to implement this, but it is not difficult. In
fact if you do a search for log-shipping scripts you can hack these to do
what you want, which is essentially very similar.
HTH,
Paul Ibison
|||Thanks Paul, I will Google log-shipping scripts and see if I can get
that working.
Thanks again for all the help,
Dan
*** Sent via Devdex http://www.devdex.com ***
Don't just participate in USENET...get rewarded for it!