When copying databases using MS-SQL Server 2000 Enterprise Manager, the safest option is to use the “Copy table(s) and view(s) from the source database”, but this abandons stored procedures, triggers and other more complex objects.
If you choose “Copy objects and data between SQL Server databases” using the defaults, you may run into fatal errors that cause the copy to fail. In true Microsoft fashion, the error messages are generally unhelpful. What I have gathered from Googling this problem, the user permissions often cause problems. Here is how to get around that problem:
Right click on the table in the SQL Server Manager tree and choose [Export]
Choose the “Copy objects and data…” option
Uncheck [Include all dependent objects]
Uncheck [Copy all objects]
Uncheck [Use default options]
Click [Select Objects]
Uncheck any objects you are sure that you didn’t use.
Click in the Objects: window and click [select all], then [check]
Uncheck [Copy SQL Server logins…]
Uncheck [Copy object-level permissions]
Autoincrement numbers are still lost, even with this method, so if you rely on that property, be prepared to do some leg work after the export is finished.