December 30, 2008 at 3:28 am
I have a production db which is running on sql 2005 and I have a development/testing system which is in sql 2000.I want to restore the Live(sql 2005) data to sql 2000
what are the available options.
December 30, 2008 at 4:55 am
first of check and tell me what's your current compatibilty level of your database ...
to check that there is two way to do that you can right click on databse goto property and in option you can see compatibility level or
from query analyzer write EXEC sp_dbcmptlevel databasename
in output it will return compatibilty level of database
if it returns 90 than that means it compatible for 2005 and if it returns 80 than it will compatible to 2000
if it return's 80 than take backup of your database and restore that into your test server just do that when you found your compatible level 80 ... and if not than reply me your compatibility level
Raj Acharya
December 30, 2008 at 5:30 am
mathewspsimon (12/30/2008)
I have a production db which is running on sql 2005 and I have a development/testing system which is in sql 2000.I want to restore the Live(sql 2005) data to sql 2000
There is no way to restore a 2005 backup onto SQL 2000, nor can a 2005 database be detached and attached to a SQL 2000 instance. A database on SQL 2005 is a SQL 2005 database, regardless of its compatibility level, and the file structure is specific to SQL 2005.
SQL 2000 does not recognise the 2005 data structure and hence cannot read a 2005 database or backup.
The only way you can downgrade is by scripting out all the tables, views, procedures, foreign keys, constraints, indexes, etc, and then to bcp the data out into text files. Create a new database on SQL 2000, run all of the scripts to recreate the structure of the database and then load all of the data files.
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
December 30, 2008 at 6:22 am
I have a production db which is running on sql 2005 and I have a development/testing system which is in sql 2000
It is recommended to run same version on production and development/testing systems.
December 30, 2008 at 6:28 am
December 30, 2008 at 6:42 am
raj acharya (12/30/2008)
in output it will return compatibilty level of databaseif it returns 90 than that means it compatible for 2005 and if it returns 80 than it will compatible to 2000
All the compatibility level affects is what T-SQL commands will run. It's purely about the behaviour of the DB. It does not affect the underlying physical structure of the database at all.
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
December 30, 2008 at 6:52 am
hi gial,
My meaning to check compatibility level is for... sometimes guys when upgrade to sql server 2005 or restore sql server 2000 database to ,2005 but doesn't changing their compatibility level or don't upgrading that to 2005 if the person has their database in sql server 2005 that doesn't mean that your compatibility level , i think that if it's compatible to 80 then we can restore that to 2000 ... i didn't do that practically but i thought so ... but as per you told in previous point if he has database in 2005 then he must downgrade that to 2000 first...but instead of production server he should try to do this practice on another test server ...
welcome your suggetions.....
Raj Acharya
December 30, 2008 at 10:35 am
a compatibility version of 80 doesnt mean you can restore the database back to sql2000.
as previously pointed out you would be well advised to make sure that both environments use the same version of RDMBS, otherwise you could end up implementing sql2000 functionality that is now removed in sql2005 😉
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply