Copying a database from Sql2005 to Sql2000

You sometimes need to copy the exact structure of a database to an Sql2000 server, even if the source server is 2005.

Follow these steps:

• open 2005 Sql Server Management Studio


• right click on the offending database and go to Tasks -> Generate Scripts


• do NOT check Script all objects in the selected database


• click Next


• set Include if NOT EXISTS to False


• set Script for Server Version to SQL Server 2000


• try to check only the objects and types of objects you actually need


• create the script


• delete all occurences of "WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)" from the generated script

Now the script should work on an SQL 2000 Server..
For the copying of data, the Server Management Studio has an option called Copy DatabaseExport Data, also in Tasks, that now accepts an Sql 2000 Server as destination.