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.
migrated from stackoverflow.com Nov 23 '09 at 12:36
This question came from our site for professional and enthusiast programmers.
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.
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
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.