Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

I want to downgrade my database with all the views and stored procedures from SQL 2005 to SQL 2000 but the problem is that the views are not restored properly.

share|improve this question

migrated from stackoverflow.com Nov 23 '09 at 12:36

This question came from our site for professional and enthusiast programmers.

    
Are you trying to restore your SQL 2005 database SQL 2000? You cannot do that. You should use DTS or something similar to move all objects from one to the other. –  Raj More Nov 23 '09 at 12:35
1  
Please explain what you mean by 'views not restored properly'. If you have used syntax that is not available in SQL 2000, then they cannot be used in it. –  CodeByMoonlight Nov 23 '09 at 12:56

2 Answers 2

Simply use something like RedGate SQL Compare and RedGate SQL DataCompare to synch the structure and database between different versions.

Or altenaively more manual just script all objects in database within enterprise manager and create new db on sql 2005 using this. Then import/export the data between the two databases.

Either method assumes you are not using any features brought in within sql2008.

Thanks

Shane

share|improve this answer

In SQL Server, there are concepts like database compatibility, database internal version and SQL Server version.

SQL Server maintains lot of meta-data about the database (including the version it was created) in the master database's boot file and this information is read when you attach the database onto the server and checks against the version of the server. Note that this internal version is different than the version of the software like 2000, 2005 & 2008. When a lower version database is attached to a higher version (server) then immediately the internal version of the database is bumped and the restore/attach goes fine but when you attach the higher version database onto a lower then SQL Server fails the required validation. One of the reason is, the meta-data and the system internal tables and structures change from one SQL Server version to another and the lower versions can't handle the higher version structures.

You can check this information very easily with the following command.

DBCC DBINFO WITH TABLERESULTS

You need to look for dbi_createVersion DBINFO STRUCTURE: DBINFO @0x467BEEE8 dbi_createVersion 655

SQL Server 7.0 : 515 SQL Server 2000 : 539 SQL Server 2005 : 611/612 SQL Server 2008 : 655

http://sqlblog.com/blogs/jonathan%5Fkehayias/archive/2009/07/28/database-version-vs-database-compatibility-level.aspx

http://technet.microsoft.com/en-us/magazine/2008.08.sqlqa.aspx

http://sankarreddy.spaces.live.com/Blog/cns!1F1B61765691B5CD!463.entry?sa=862852830

Now coming back to your problem, you have to use SSIS or other third party tools to script the objects and data. But I have to ask you why are you planning to go back to 2000? Are you running into any issues? Share your thoughts and problems and it might be easier to fix them.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.