SQL Server - Copy stored procedure's from one database to other

Question

I am trying to copy a database with huge data from SQL Server 2005 to SQL Server 2008. I tried using the Copy Database Wizard but could not use the wizard because they are two different server 2005 and 2008. So I copied all the tables using Import data wizard from SQL Server 2008.

I had about 1120 stored procedures to be copied, so on SQL Server 2005 I used the Generate Script wizard and created script(for SP) which I could run on SQL Server 2008. I ran that script on SQL Server 2008, but only 1082 procedures were created, most of them didn't get created because of dependency issues. Now I am not sure out of 1120 SP's which were not created.

Can you suggest anything on how to fix this situation?

Answer 1

SQL Server 2008 can restore from a SQL Server 2005 backup file. Why don't you just do a quick backup and restore operation? If you don't want the whole thing, just backup and restore to a temporary SQL 2008 database and then copy the objects you need from there.

Answer 2

What kind of dependency issues? Most of the time it will just complain about dependency issues, for example if you try to execute a SP from a SP that is not yet created. But it will still be created.

It might be logins or such?

Why dont you just make a backup and restore the database?

Answer 3

Would it not be simpler to just Detach the SQL 2005 DB, copy the .mdf and .ldf files for the DB to the SQL 2008 Data folder, and then Attach the DB?

Answer 4

In order to find out which stored procedures are missing and need to be re-created, you should use a SQL diff tools, something like:

• Red-Gate SQL Compare
• ApexSQL SQL Diff

Those do cost a bit of money to license, but they're well worth their purchase price and can save you countless hours of work trying to figure out what needs to be done.

Both those tools can compare two databases (even different versions) against one another, and they can create either update scripts to make them have the same objects, or you can even do the synchronisation directly in those tools, if you wish to do so.