Creating development env from a production env on SQL 2005

Question

I have created a new database(development from production DB). These DBs have different names and now insert and update statements in my procedures are prefixed with the production database name. How can i change this rather than doing it mannually.

Answer 1

Script the procedures and functions out and then search and replace in the text file, and then apply to the new db? You can automate all of that easily.

• Use Sql Management studio to grab a script of all the procedures. Or write a powershell script to use the sql SMO API to do this
• Use a sed command line to do search and replace over the file.
• Restore the db using a SQL script (you can use SQL management studio to do the operation and script it at the same time).
• Use SQLCMD to run the amended script in the new DB

Answer 2

I think that Preet has the right approach. Here is a way that should work in theory (although I've not tried it myself).

You could script out all your procedures by clicking on the Stored Procedures node in SSMS, then from the Object Explorer Details pane (View menu) you can select them all. Right click and choose Script Stored Procedure as CREATE To New Query Window (annoyingly you can't choose ALTER). Do a search replace and change "CREATE PROC" to "ALTER PROC". Now do your search and replace to sort out all the incorrect database references. Then run the query...

Good luck!