SQL Server Performance Forum – Threads Archive

Quick way to roll back from SQL 2k5 to SQL 2k

I am preparing an upgrade from SQL 2K to SQL 2K5 and like to know if there is quick way to bring the user databases back to the SQL 2K version if SQL 2K5 doesn’t work efficently after go live. As far as I know, once the SQL server version is upgraded to the SQL 2K5 version, the user databases can’t be restored back to a SQL 2K version. Since the backup and restore is usually the quickest way but it won’t work in this case, DTS (or integration service) may be too slow, what other options do I have? Or Am I correct in the above statements? wingman

---

You could try replication from SQL 2005 box to a SQL 2000 box.
***********************
Dinakar Nethi
SQL Server MVP
***********************
http://weblogs.sqlteam.com/dinakar/

---

But is it true that the indentity value can’t be replicated?

---

Unfortunately, this is not as easy. Backed-up databases from SQL Server 2005 instances cannot be restored in SQL Server 2000 instances. Nor can detached SQL Server 2005 databases be reattached to SQL Server 2000. http://searchsqlserver.techtarget.com/expert/KnowledgebaseAnswer/0,289625,sid87_gci1149585_tax301536,00.html MohammedU.
Moderator
SQL-Server-Performance.com All postings are provided “AS IS” with no warranties for accuracy.

---

Hi,
Refer below links http://www.microsoft.com/technet/community/chats/trans/sql/sql0626.mspx http://72.14.205.104/search?q=cache…ted&hl=en&ct=clnk&cd=3&gl=us&client=firefox-a Hemantgiri S. Goswami
MS SQL Server MVP
————————-
"Humans don’t have Caliber to PASS TIME , Time it self Pass or Fail Humans" – by Hemantgiri S. Goswami http://hemantgirisgoswami.blogspot.com

---

No we have successfully replicated a 2005 DB to a 2000 box as a back up for almost a month before we pulled the 2k box out. No issues with Identity columns. Make sure to set the ‘Not for Replication’ for the identity column property (you can see it in table design window) ***********************
Dinakar Nethi
SQL Server MVP
***********************
http://weblogs.sqlteam.com/dinakar/

---

You can do one way transactional replication but your tables have to have PK to participate in transactional replication otherwise you have to replication procedure executions. MohammedU.
Moderator
SQL-Server-Performance.com All postings are provided “AS IS” with no warranties for accuracy.

---

Complete database backups are only the options to backout such changes, if you are really worried about such issues why don’t you test the application on full scale to ensure no issues are expected. Without the testing it is really hard for you to make sure it works or not. See thishttp://sqlserver-qa.net/blogs/tools…-upgrades-of-your-sql-server-environment.aspx fyi. Satya SKJ
Microsoft SQL Server MVP
Writer, Contributing Editor & Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing. The greatest discovery of my generation is that a human being can alter his life by altering his attitudes of mind.

---

Is there a quick way (a script available) to do the following: 1. Find out which primary key is not set for ‘not for replication’
2. set the primary key of all the tables to be ‘not for replication’

---

yes, you can use the CnstIsNotRepl property to check that. check out BOL on how to use it. And to set the property you can do an ALTER TABLE. You need to have sp4 on SQL 2000 for that. ***********************
Dinakar Nethi
SQL Server MVP
***********************
http://weblogs.sqlteam.com/dinakar/

---

select table_name, constraint_name
from information_schema.table_constraints
where constraint_type = ‘primary key’
and objectproperty(object_id(constraint_name),’cnstisnotrepl’) = 0 For second question I think you have to use ALTER TABLE…command.. MohammedU.
Moderator
SQL-Server-Performance.com All postings are provided “AS IS” with no warranties for accuracy.

---

Mohammed I think you should use COLUMNPROPERTY not OBJECTPROPERTY since ‘cnstisnotrepl’ is for column. ***********************
Dinakar Nethi
SQL Server MVP
***********************
http://weblogs.sqlteam.com/dinakar/

---

I have the following questions: 1. Under SQL 2K and SQL 2K5 replication, the primary key and identity features are removed in the subscriber database. Is it that’s why we need to set the ‘Not for replication’ to ‘Yes’? By doing so, the primary key and indentity feature retain on each table in the subscriber db during replication? 2. I should only need to make all the primary keys that are using Indentity ‘not for replication’. All other primiary keys I shouldn’t need to apply ‘not for replication’, right? 3. Say I have done all that and a SQL 2k5 user db is replicating to a SQL 2K db on a seperate server. Say something bad happened and I need to go back to SQL 2K, Can I just shut down the SQL 2K5 replication and maybe even turn off the publisher server and have users use the replicated SQL 2K db as the live db? Obviously I may need to deal with a different db name and need to make sure logins are available, are there any other issues I may need to consider? 4. I have quite a few check contraints, foreign key contraints and triggers are set for ‘Not for replication’ in the publisher db and they don’t exist in the replicated database. If I use the replicated database as the live server, will I need to recreate them before I turn the db to a live one, right? Or If I want to save that step, I can turn on ‘for replication’ for them in the publisher db ahead.

---

If you don’t want to replicate the PK you need to use NOT FOR REPLICATION…
If you want to replicate constraints like PK you have to enable the option in Article properties… Replication is one option but it is very painful when you do it using transactional replication…but you can try snapshot which will take time and its depends on the size of the db… After how many days you want to go back? why can’t you change the compatibility to 80 and run it…
MohammedU.
Moderator
SQL-Server-Performance.com All postings are provided “AS IS” with no warranties for accuracy.

---

Ok, I can see how ‘not for replication’ works now. Will it be a chance that the same table in the publishing db and subscriber db may not have the same identity value during the insert and subsequently replicated insert to the subscribing db? I need them to be the same in order to make the replicated db as a production db in case of serious problem.

---

If you are using the NOT FOR REPLICATION option, the id generated at the publisher will be propagated to the subscriber DB. Unless you are doing INSERTS at the subscriber end, the Id’s should match. ***********************
Dinakar Nethi
SQL Server MVP
***********************
http://weblogs.sqlteam.com/dinakar/

---

I see you point and we are not doing any insert into the subscriber db’s tables. Just for my clarification, if the table in the subscriber db has its identity field and it is on, the incremented value of the identity field should be based on its own increment not from the publisher, right? for example, TableA is in both publisher db and subscribe db and both table should have idenity value at say 1000. For whatever reason, tableA in subscriber db is at 1001. If someone insert a new record in table A in the publisher, the identity field value for table A in publisher would be 1001 and the value in TableA in subscriber db woudl be 1002. Right? Or are you saying the tableA in subscriber db will still be 1001 no matter what identity value it has?

---

No, the ID comes from the publisher like this:
SET IDENTITY_INSERT <table> ON
INSERT INTO <table>
Values <….>
SET IDENTITY_INSERT <table> OFF So whatever ID was generated at the publisher will be the same at the subscriber too.
Replication simply is like copy/paste. so nothing new happens at subscriber end. ***********************
Dinakar Nethi
SQL Server MVP
***********************
http://weblogs.sqlteam.com/dinakar/

---

]]>