June 11, 2008 at 11:08 am
If I have a database running on 2005 that is in mode 80, can I restore a backup of this database to 2000? The database was originally running on 2000 and restored to 2005 but left in 80 mode for application compatibility.
June 11, 2008 at 11:28 am
As far as I understand it - if it is in 80 mode - then the structure of the Database is still 2000 compatible. Meaning that the DB has not had the scripts ran against it to be "upgraded" to 90 and it should be able to be backed up and applied back to a 2000 server.
I will do a bit of checking and add a post once confirmed
Thanks
Kevin
June 11, 2008 at 12:13 pm
I do not believe that you can restore a SQL Server 2005 database backup to SQL Server 2000.
June 11, 2008 at 12:19 pm
Michael Valentine Jones (6/11/2008)
I do not believe that you can restore a SQL Server 2005 database backup to SQL Server 2000.
Not if it is a mode 90 database. My question was for a database that is mode 80?
June 11, 2008 at 2:07 pm
June 11, 2008 at 2:41 pm
Unless someone comes up with a fancy workaround, you should not be able to do this. Once you've restored or attached a SQL Server 2000 DB to a SQL Server 2005 instance, it is upgraded to 2005. The compatibility modes allow for use of different functionality only. Changing compatibility modes does not change the DB version.
June 11, 2008 at 2:48 pm
Once a SQL 2000 databse has been restored or attached to SQL Server 2005, you can't go back to SQL Server 2000 with that database, unless you use DTS/SSIS to transfer all the data from the database on SQL Server 2005 to a database on SQL Server 2000. Even then, you may have to script your object at the 80 compatibility mode and run those on SQL Server 2000.
😎
June 12, 2008 at 12:12 am
I have tested by - taking a 2000 DB - restoring to 2005 and then trying to take it back - and this is the error message:
The backed-up database has on-disk structure version 611. The server supports version 539 and cannot restore or upgrade this database.
RESTORE FILELIST is terminating abnormally. (.Net SqlClient Data Provider)
So it seems the default operations of backup and restore between 2000 - 2005 and back to 2000 do not work once "upgraded".
The SQL 2005 version is SP2 3175 and SQL 2000 is SP4
Thanks
Kevin
June 12, 2008 at 12:42 am
Compat mode 80 just means that the SQL 2005 features are not enabled. It does not mean that the database structure is still that of a SQL 2000 database.
Underneath the covers, recardless of the compat mode, a database attached to a SQL 2005 instance is a SQL 2005 database and cannot be restored or attached to a lower version.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 21, 2008 at 11:01 pm
[font="Verdana"]You can't restore a database that is attached to SQL 2005 in what ever compatibility mode. Compatibility mode is just interpretation of T-SQL code. If its in 80 it will interpret T-SQL statements in SQL 2000 format and so on.
When you attach a db in SQL 2005 its automatically upgraded to SQL 2005 format and you won't be able to restore the db back to SQL 2000. But you can make use of Database publishing wizard to this for you, which will script all the objects in SQL 2005 to SQL000.
Refer "Moving SQL2005 db to SQL2000 db"[/font]
Regards..Vidhya Sagar
SQL-Articles
August 20, 2008 at 9:04 am
No I tried using Database Publishing wizard and it did not work ... the script grew to a huge size and could not be open in Query analyzer as mentioned in the link
September 9, 2008 at 7:40 am
Hi there, what about changing from 90 to 80 while in 2005? Can it be done? Question why is because I'm upgrading a server from 2000 to 2005. Because of the apps running, we need to keep 80 mode. This will be "in place" upgrade. I'm assuming that if I do not detach the databases, they will be automatically changed to 90? Or will the upgrade leave them on 80?
Thanks
September 9, 2008 at 1:11 pm
Dunno if the upgrade will automatically change the compat mode, but you can change that freely from 90 to 80 or even 70 if you wish.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
March 18, 2010 at 3:11 am
I'm in the same predicament as the original post. Though everyone's replies have answered my questions bar one: why, in the db I've got here that is in 80 compatibility mode, has someone been able to create a schema? the schema also owns some objects (2 or 3 tables and some default constraints).
I can freely create new schemas in this db using the create schema schemaname syntax.
Oh, and, the db is also service broker enabled in sys.databases (though there are no user object under the service broker bit of the object explorer)
I thought compatibility mode 80 would stop you using/running 2005 features? Why/how is this allowed to happen?
March 18, 2010 at 4:18 am
The compatibility level affects how the query processor handles some query constructs and whether or not some features are allowed or not. Mostly is disallows features that changes the meaning or behaviour of some keywords. It makes the DB behave in most ways as though it's on SQL 2000, it does not necessarily disable all SQL 2005 features.
All DBs on SQL 2005 will have schemas, even if it's just the default dbo schema. They have to, in SQL 2005 all objects must be in a schema.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply