SQL Server Performance Forum – Threads Archive

Copying DB from 2005 to 2000, + Encrypted Objects

Hoping someone can point me in the right direction on this. My situation is: We are currently running on a 2000 server, but have a 2005 server we will be moving our production databases to. Backup & restore from 2000 to 2005 seems to work fine (with the exception of having to reset user passwords). However, we also want to be able to go back to the 2000 server, in the event of a major failure on the 2005 server. (This doesn’t have to be an automatic failover; it can be a manual process.) We would need to make regular, somewhat frequent backups to the 2000 server. Backup & restore does not work from 2005 to 2000. Most of the searching I’ve done suggests that scripting out all your objects and copying data over (either with SSIS or BCP) is probably the way to go. However, we have a number of encrypted stored procedures that can’t be scripted out. (I don’t care about seeing the procedures myself, just about transferring them from one server to the other.) I’ve found a couple scripts online for decrypting objects, but they don’t seem to work for a 2000 compatibility database on a 2005 server. On another note, I’ve read a couple places that replication is possible from 2005 to 2000, but don’t have much details on the process. Any suggestions on the best method to pursue in this situation? I’m fairly new to server 2005, and haven’t gotten to work with replication yet. Thanks in advance.

---

Are you doing inplace upgrade? What is the cutoff period after upgrade it 2005? Immediately after upgrading…if you want to go backup you can restore the 2000 backup on 2000 server… why do need to go through this pain?
As you know SQL 2000 to 2005 is easy… backup restore… you can copy the logins with any problems by using sp_help_revlogin procedure can get from the following link.. How to transfer logins and passwords between instances of SQL Server http://support.microsoft.com/kb/246133 However going backp from 2005 to 2000 is pain… Either you have use BCP or DTS…
And I believe replication should also works but I don’t how it is going work with encrypted objects…
You can use third party tools to decrypt… http://www.elitude.net/ Mohammed U.

---

They’re on two separate physical servers. The 2000 server is what we’re running everything on now. Production databases will be copied over to the 2005 server (much better hardware as well), and the 2000 server will stick around for non-production databases. We just want to be able to use a backup of the production databases on the old 2000 server in case of a disaster with the new 2005 server. We may be able to have both systems running sql server 2005 at some point, but not in the immediate future.

---

Hi,
Here are some article to be refer while recplicating between SQL 2005 and SQL 2000 regarding some feature which are discontinued
Using Multiple Version Replication/Supported Versions
http://msdn2.microsoft.com/en-us/library/ms143241.aspx Discontinued Features in SQL 2005 Replication
http://msdn2.microsoft.com/en-us/library/ms143470.aspx
http://msdn2.microsoft.com/en-us/library/ms143723.aspx Setting up Replication (refer BOL also)
http://www.informit.com/articles/article.asp?p=599700&rl=1 Articles related to Replication
http://www.replicationanswers.com/Articles.asp and a third party Decryptor toolhttp://decryptor.orbitaltechnologies.org/Trial.aspx Regards Hemantgiri S. Goswami
"Humans don’t have Caliber to PASS TIME , Time it self Pass or Fail Humans" – by Hemantgiri S. Goswami
————————
http://hemantgirisgoswami.blogspot.com

---

]]>