Copying databases

Hello,

I'm attempting to launch a new ASP .NET site and I'm having problems moving my database, caused I'm sure by my own ignorance.

I built the database in SQL Server 2005 Express but found that I couldn't use it to copy the database to the deployment server, so I uninstalled it and now I'm using a 6 month trial version of SQL Server 2005 Enterprise Edition.

The server to which I want to copy my database is SQL Server 2003 (version 8.0.760).

My first attempt succeeded in uploading the tables and their data, I then used the "Generate Scripts" command to produce a large query detailing how to create my stored procedures, I ran this script on the destination server and it eventually worked ok.

However, for some reason my Primary Keys, Foreign Keys, Constraints and Identities were not copied to the database on the deployment server at all. The columns all arrived, with the correct datatypes, but the other objects weren't defined at all.

Obviously, these are crucial, so what went wrong and how do I fix it please?

Many thanks

Ben

Are you using "Generate Scripts" or the "Import/Export Data"?

If you're using Generate Scripts, make sure all the options under Table/View Options are true.

If you're using Import/Export Data, It doesn't work so well. Use the Generate Scripts option to actually create the tables/Stored Procedures/Views, etc., then copy the data using the Import/Export option.

Another option is to backup the database, then restore it to the other server.

|||

Thank you for responding.

I initially used "import/export data" but when I found out that it hadn't done my stored procedures I used the "generate scripts" to get the stored procedures as well.

I've tried using generate scripts the way you suggest, and I think it would work IF I didn't already have a load of tables and procedures in the target database. Is there a quick way to drop everything in the database, without deleting the database itself so that I can have a second go at copying my objects?

I daren't delete the database and recreate it because it's on a remote host that I can't get to and only have permissions to alter my own database.

|||

BenCh1: "Is there a quick way to drop everything in the database, without deleting the database itself so that I can have a second go at copying my objects?"

I believe this is enabled with SP2 - November CTP. You can script a "DROP" statement before each create statement. You can obtain a CTP of Service Pack 2 here: http://www.microsoft.com/sql/ctp.mspx.

Paul A. Mestemaker II
Program Manager
Microsoft SQL Server Manageability
http://blogs.msdn.com/sqlrem/