copy Stored procedures 1

[shaminda]

I am trying to copy stored procedures from one database(Say DB1) to another database(say DB1_copy) on the same server. Basicaly what i want to do is, use the second database for testing. But when I use DTS to copy the database it copies all the tables but it won't copy the stored procedures. Is there another way to do it? or what am i doing wrong? Here is the error message i get:

Failed to copy objects from Microsoft SQL Server to Microsoft SQL Server.

 

[Makem7]

May not be the most elegant solution, but why not use Database Scripting (Tools) menu - generate a script of the stored procedures and run this to install into your copy db?

 

[SQLSister]

What we use is a tool called SQLCompare. IT is not expensive and worth it's weight in gold. It will look at both datbase and tell you whihc items are diffenrent and which do not exist in the other database and then script the differentces. This is great for moving from development to testing and testing to production. I will warn you now that any stored procedure that uses the full databse name will be a problem if you have another databse of a differnt name on the same server as the testing database because the stored porcedure will affect the deve database, not the test database.

 

[pascalsql]

Another possibility is to clone your database
1) detach it with sp_detach
2) duplicate .mdf and .ldf files in windows file explorer with different names
3) re-attach the original database
4) attach the new one with a different name and the new files.

Be sure you understand how to use sp_attach before (parameters).

You get a perfect clone of your database!

 

[shaminda]

I generated a script, and changed the database name and ran the script. Thank you for your help.