I want to downgrade my database from SQL 2005 to SQL 2000

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.

From serverfault $pageUsers[$entry.posts[0].author].name

• 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

  From $pageUsers[$post.author].name

• 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.

  From Sankar Reddy