ROLLING BACK A SQL 2005 DB to SQL 2000

Question

ROLLING BACK A SQL 2005 DB to SQL 2000

Michael Ingram

SSC Enthusiast

Points: 143
More actions
January 3, 2008 at 8:07 am

#180107

My company uses SQL for one of our applications and we are getting ready to move ahead to SQL 2005. We orig were using SQL 7 and there were times when someone put their databases on SQL 2K on another server and once the orig server was fixed, we had to use the DTS function and rollback the 2000 db's to SQL 7.
Is this same functionallity available with SQL 2005.
Thanks

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Steve Jones - SSC Editor SSC Guru Points: 735657 More actions · Answer 1

You can use SSIS to "roll back" a SQL 2005 database, but why would you? Why not just install another instance of 2000 temporarily if you need to?

Once you upgrade a database, it cannot easily be moved back to 2000. The schema and data must be transferred.

Tommy Bollhofer SSChampion Points: 14940 More actions · Answer 2

SQL Server 2005 supports 70, 80 and 90 compatibility modes. Please note that when you attach an 80 database from SQL 2000 to SQL 2005 it upgrades the system tables, meta data views, etc. You cannot reattach to SQL 2000. You will need to script the DML/DDL using SSIS, etc.

Tommy

Follow @sqlscribe

Michael Ingram SSC Enthusiast Points: 143 More actions · Answer 3

Steve,

Thanks. We do work for the Navy and they have their requirements, etc..

We just wanted to make sure that if someone installed SQL 2005 and put our databases in that instance of SQL, would we be able to rollback the data to an instance of 2000 to keep in compliance with what the customer says is the "authorized" version of SQL.

BTW is that info in the SQL 2K5 documentation?

Thanks for the quick reply.

Michael Ingram SSC Enthusiast Points: 143 More actions · Answer 4

Thanks,

Yes, I understand I wouldn't be able to flat out reattach to an instance of 2K, but just wanted to know if the SSIS was the same as the DTS function in SQL 2K.

Steve Jones - SSC Editor SSC Guru Points: 735657 More actions · Answer 5

You can right click a database, select "export data" and then walk through the wizard to copy data and objects back. It's similar to the copy database wizard in DTS.

You can also save this as an SSIS package to examine it.

Michael Ingram SSC Enthusiast Points: 143 More actions · Answer 6

Michael Ingram

SSC Enthusiast

Points: 143

January 3, 2008 at 9:48 am

#764439

Steve,

Thanks again. I'll give it a shot.