July 1, 2009 at 2:25 pm
HI,
We are trying to move an application's database from sql2000 to sql2005. The vendor is asking if the database can remain in SQL 2000 format?
I've never been asked this so not sure what the answer is. Would this have to do with leaving the compatibility level at 80?
Thanks a bunch in advance for any help!
Juanita
July 1, 2009 at 2:32 pm
Yes, you can leave it in compatibility mode 80, but once the database is attached to SQL Server 2005, you won't be able to take it back to a SQL Server 2000 server as the internal structure will be updated.
July 1, 2009 at 2:46 pm
HI Lynn,
So by keeping the level to 80 this is considered 'not converting' to sql 2005?
Thanks,
Juanita
July 1, 2009 at 2:50 pm
That is generally a true statement, you won't be able to do a DB backup and restore back on a 2000 box, but it will generally behave the same.
I would recommend reading the SQL Server 2005 BOL (Nov 2008) article:
sp_dbcmptlevel (Transact-SQL)
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/tsqlref9/html/508c686d-2bd4-41ba-8602-48ebca266659.htm
It covers some things to think about..
CEWII
July 1, 2009 at 2:53 pm
Thanks a bunch Elliot and Lynn...
This helps alot!
Juanita
July 1, 2009 at 2:54 pm
You are welcome.
CEWII
July 1, 2009 at 2:54 pm
Lynn Pettis (7/1/2009)
Yes, you can leave it in compatibility mode 80, but once the database is attached to SQL Server 2005, you won't be able to take it back to a SQL Server 2000 server as the internal structure will be updated.
Nope, this is still considered as upgrading to SQL Server 2005. Compatibility mode just means that newer functionality available in 2005 will not be available, and older functionality that works in SQL Server 2000 will probably still work.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
July 1, 2009 at 3:19 pm
Jeffrey
Are you saying that once you restore to a sql 2005 server, your database is actually 'converted' at that time to sql2005?
Juanita
July 1, 2009 at 3:25 pm
Yes, exactly - once you restore or attach a 2000 database to a 2005 instance, that database is converted to 2005. You cannot then later backup/restore that database to a 2000 instance, nor can you detach/attach that database back to a 2000 instance.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
July 1, 2009 at 3:29 pm
Thank you very much Jeffrey!
Juanita
July 1, 2009 at 4:36 pm
In case if you require to do a rollback of your database from SQL 2005 to SQL 2000 you can follow these tedious steps:
- Script out entire database including all the objects before converting to SQL 2005 format.
- When you need to rollback run this script and create the new blank database.
- Then using DTS, import the data. But this needs to be carried out considering all your constraints...
July 2, 2009 at 8:55 am
Thanks!
Juanita
July 6, 2009 at 7:47 am
Another thought that the original user might be interested in:
use Sql Server 2005 front end tools (SSMS, etc.) against a SS-2000 database.
I'm not fully sure of the value but that might be something that they would be interested in.
Steve
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply