Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

SQL Server 2008 R2 Remove Not for Replication

Posted on 2013-05-20
9
Medium Priority
?
854 Views
Last Modified: 2013-07-10
I initially set up replication temporary and it removed.  

Now we've noticed the id columns have ‘FOR REPLICATION’, immediately we executed sp_removedbreplication ‘dbname’.

After executing the sp, the id column now have ‘NOT FOR REPLICATION’.  

How do you get rid of this NOT FOR REPLICATION from my id columns?


e.g.

(id)     INT       IDENTITY  (1,1)  NOT FOR REPLICATION NOT NULL,
0
Comment
Question by:Favorable
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
9 Comments
 
LVL 29

Assisted Solution

by:QPR
QPR earned 249 total points
ID: 39182561
0
 
LVL 10

Assisted Solution

by:Matt Bowler
Matt Bowler earned 1002 total points
ID: 39182944
Is there an issue with having this there?
0
 

Author Comment

by:Favorable
ID: 39183275
Is an issue when doing schema compare inside of visual studio 2012.

Is there a way to turn if off for all table identity columns in the database?
0
NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

 
LVL 10

Assisted Solution

by:Matt Bowler
Matt Bowler earned 1002 total points
ID: 39183282
alter table tablename alter column columnname drop not for replication
0
 

Author Comment

by:Favorable
ID: 39184326
do you have to repeat for over one thousand objects in the database?
0
 
LVL 75

Assisted Solution

by:Anthony Perkins
Anthony Perkins earned 249 total points
ID: 39185843
do you have to repeat for over one thousand objects in the database?
You don't have to repeat it that many time you just need to execute it that many times.
0
 
LVL 10

Assisted Solution

by:Matt Bowler
Matt Bowler earned 1002 total points
ID: 39185854
Run this in the database. The output will be all of the alters that need to be run...

select
      'alter table ' +
      object_name([object_id]) +
      ' alter column ' +
      name +
      ' drop not for replication'
from sys.identity_columns where is_not_for_replication = 1
0
 
LVL 10

Accepted Solution

by:
Matt Bowler earned 1002 total points
ID: 39272465
Has this been answered?
0
 

Author Closing Comment

by:Favorable
ID: 39314974
thanks
0

Featured Post

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Ready to get certified? Check out some courses that help you prepare for third-party exams.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

609 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question