how to transfer stored procedures between SQL Server 2005 databases

Question

I'm creating web site on local computer. i'm using SQL Server 2005 management studio. I need to copy all data to destination server. Destination server is SQL Server 2005. My problem is

1. when i using import/Export data for management studio, this only copy tables.
2. when i using backup and restore, tables and stored procedure shows like this

myuser.aspnet_application

myuser.aspnet_Membership ... etc. I need to create like this

dbo.aspnet_application

dbo.aspnet_Membership

How to copy stored procedures and views to destination server?

Answer 1

If this is a one-time job you can script them all easily.

Open SQL Management studio, and browse to the Stored Procedures node for your database. Open the Object Explorer if it isn't open already (click F7) and select all stored procedures you want to copy. Right click the list and select Script Stored Procedure as -> Drop and Create -> To new query window.

This will give you a script that drops the procedures if they exist and then creates them.

If you get the myuser schema or you get use [databasename] statements in your script you can turn these of by doing the following: Select Tools -> Options in the menus. Navigate to SQL Server Query Explorer -> Scripting and set the following to false: "Script USE " and "Schema qualify object names".

The script you get can be run on your new database and should create all the stored procedures you need.

Answer 2

In SQL Server Management Studio navigate to your database.

Right click it and select "Tasks" -> "Generate Scripts"
"Next"
Select your database from the list
"Next" Select "Stored Procedures" "Next"
"Select All" "Next"
"Script to new Query Window" "Next"
"Finish"

Give it a while. Then when complete, at the very top of the script put "use (yourdatabase)"

Execute the use statement.
Execute the whole script.

Answer 3

Looks like you are using the ASP.NET membership provider. You can easily recreate the stored procedures used by the ASP.NET provider by running the tool aspnet_regsql.exe installed with the .NET framework.

Answer 4

You could also use tools like RedGate SQLCompare and SQLDataCompare to easily move data and objects between databases

Answer 5

Try DBSourceTools.
http://dbsourcetools.codeplex.com
Its open source, and will script an entire database
- tables, views, procs and data to disk, and then allow you to re-create that database through a deployment target.
It's specifically designed to help developers get their databases under source code control.