April 3, 2007 at 7:40 am
Hi,
we are currently using sql server 2000 in production. we have purchased the sql server 2005 64 bit edition and installed it on one of our new servers. we are planning to keep the sql server 2000 as the backup server so that if there is a problem with sql server 2005 fails then we can detach the files from sql server 2005 and re-attach them to sql server 2000. But we are not sure if that works. Please let us know.
Thanks,
sridhar.
April 3, 2007 at 10:20 am
Sridhar
That will not work, unless you run SQL Server 2005 in 8.0 mode. Which basically means that you will not be taking advantage of the new functions of SQL Server 2005.
Marvin
Marvin Dillard
Senior Consultant
Claraview Inc
April 3, 2007 at 10:40 am
If I run sql server 2005 at compatability level 80 then can I detach a database from sql server 2005 and re-attach it to sql server 2000? do I have to run any conversion tool before detaching it from sql server 2005?
Thanks,
sridhar.
April 3, 2007 at 10:13 pm
Once you attach your files to SQL 2005 you will not be able to attach them to earlier versions on SQL. You could take a back up copy prior to attaching to SQL 2005 that you could revert to if needed in case of a roll back to the earlier version. When you attach to the files to the SQL 2005 system, SQL makes changes to the data file that mean it can only work in SQL 2005 from then on.
Hope that helps,
Catherine
Catherine Eibner
cybner.com.au
April 4, 2007 at 7:05 am
Other than resorting to the last SQL 2000 backup, as mentioned above, you cannot go back to SQL 2000.
The only other technique is to export the data from SQL 2005 and import it back into the SQL 2000 database.
April 4, 2007 at 10:14 am
changing cmptlevel from 90 to 80 or vice versa is not going to help, as the moment when SQL server 2000 Database is been attached or restored to SQL Server 2005 the version of that database changes from 539 to 611. In SQL Server 2000 go to SYSTEM DB = Master & open table "sysdatabases" where you will see Database version & on SQL Server 2005 opne view "sys.sysdatabases" you will see DB version(611). so before upgrade\deattach\restore SQL server 2000 DB to SQL 2005, it is always recommend to have backup copy. i hope this helps.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply