Steps to Copy Stored Procedure From One Server to Another Server

In this blog, we’re going to learn the different methods that you can use to copy stored procedure from one server to another server. This blog is also suitable for all newbies, as it doesn’t require more technical knowledge. Users with a basic knowledge of SQL Server can easily migrate SQL Server database from one server to another.

SQL Server is the most popular relational database management system (RDBMS) among commercial and corporate sectors. However, there are times when SQL users face the need to move stored procedures from one server to another server for several reasons. This task might be a headache for non-technical or novice users as it requires granular technical knowledge of SQL Server.

Therefore in this guide, we will outline some simple and effective approaches to tackle such issues without any hurdles.

Queries to Copy Stored Procedure from One Database to Another Server

Now, here, users ask two questions upfront. The one is to copy the stored procedures within the same server but different database. Whereas, the second one is about copying it to a different server. Let’s, have a look at this query and then proceed to learn how to copy stored procedure in SQL Server DB.

Now, based on the query, if users only want to change the database and keep the server the same, the solution is a bit simple. Let’s have a look at that:

Here, @sql is denoted with nvarchar(max) and @Name is denoted as target database.
DECLARE c CURSOR FOR
  SELECT Definition
  FROM [SQLMaster].[sys].[procedures] p
  INNER JOIN [SQLMaster].sys.sql_modules m ON p.object_id = m.object_id
OPEN c
FETCH NEXT FROM c INTO @sql
WHILE @@FETCH_STATUS = 0
BEGIN
  SET @sql = REPLACE(@sql,'''','''''')
  SET @sql = 'USE [' + @Name + ']; EXEC(''' + @sql + ''')'
  EXEC(@sql)
  FETCH NEXT FROM c INTO @sql
END            
CLOSE c
DEALLOCATE c

Methods to Copy Stored Procedure From One Server to Another

This can be done either by manual method or by using a professional software solution. Here in this post, you will find both solutions in a detailed manner. Read the complete post and migrate SQL Server database from one server to another effortlessly.

1. Using Generate Scripts Method

Follow the below-mentioned instructions carefully to copy SQL Server database from one server to another.

Step 1. Launch MS SQL Server Management Studio in your system and go to the Object Explorer.

Step 2. Right-click on the database from which you want to move data and then click on Tasks>>Generate Scripts…A Generate and Publish Scripts Wizard will appear on the screen, click on the Next button to proceed.

Step 3. On the Choose Objects page check the select specific database objects option. From there pick the stored procedures as your objects to script out. Now proceed to copy stored procedure from one database to another smartly.

Step 4. On the Set Scripting Options Window, choose Save Scripts to a specific location and then press the Next button.

Step 5.  Now, click the Finish button to close the Save or Publish Scripts Window to finish learning how to copy stored procedure from one database to another.

Step 6. Afterward, run those script files on another SQL Server where you want to move them.

2. SQL Server Copy Stored Procedures from One Server to Another with T-SQL CMD

Now, in case, users wants to undergo the entire procedure manually, they can use this command mentioned below: Although, users often complain that they face multiple issues using the manual T-SQL commands. Therefore, we recommend users trust this method at their own risk.

[cc lang=”sql”]
CREATE PROCEDURE [dba].[pr_refresh_create_procedures]
@linked_server varchar(100),
@source_db varchar(100),
@target_db varchar(100)
AS
–EXEC [dba].[pr_refresh_create_procedures] ‘LOCALSERVER’, ‘database_name’, ‘targetdb_dev’
SET @source_db = ‘[‘ + @linked_server + ‘].[‘ + @source_db + ‘]’;
DECLARE @sql Nvarchar(max)
DECLARE @Name varchar(255) = @target_db
IF OBJECT_ID(N’admindb.tmp.shelldb_copy_procedures’) IS NOT NULL DROP TABLE admindb.tmp.shelldb_copy_procedures
SET @sql =
‘SELECT p.name, m.Definition
INTO admindb.tmp.shelldb_copy_procedures
FROM ‘ + @source_db + ‘.sys.objects p WITH (NOLOCK)
INNER JOIN ‘ + @source_db + ‘.sys.sql_modules m WITH (NOLOCK) ON p.object_id = m.object_id
WHERE type IN (”FN”, ”IF”, ”TF”, ”P”, ”V”, ”TT”)’
exec(@sql)
DECLARE c CURSOR FOR
SELECT Definition
FROM admindb.tmp.shelldb_copy_procedures
OPEN c
FETCH NEXT FROM c INTO @sql
WHILE @@FETCH_STATUS = 0
BEGIN
SET @sql = REPLACE(@sql,””,”””)
set @sql = N’execute ‘ + QUOTENAME(@name) + N’.dbo.sp_executesql N”’ + @sql + ””
EXEC(@sql)
FETCH NEXT FROM c INTO @sql
END
CLOSE c
DEALLOCATE c
[/cc]

Now, if users want as solutions that can do both the tasks, the automated tool is the right choice.

3. Automated Way to Copy Stored Procedure From One Server to Another

The process of copying stored procedures from one SQL Server to another Server is no longer complicated or time-consuming. With the help of SysTools SQL Server Migration Tool, you can migrate all database objects including tables, functions, Stored procedures, views, triggers, etc. from one Server to another server in just a few simple clicks. Also, this tool helps users simply execute SQL export to CSV file format.

Download Now Purchase Now

The software to export stored procedures SQL server provides a clear picture of all the database objects on its intuitive interface. Moreover, the permanently deleted data items are displayed in Red color, so that it can be easily placeable by the users. It also gives an option to transfer the SQL database objects to an existing database or as a new database in the same machine or a server network. Moreover, the graphical user interface of the tool is simple and easy to use that every user can easily use it without any hurdle.

Let’s See the Working of the Tool & Learn How to Export Stored Procedure in SQL Server Database:

Step-1. Download install and run the tool on your system and add your database MDF file by clicking the Open button.

Step-2. After adding the file into the software, choose mode. Also, select the Server version of the .mdf file either manually and automatically and hit the OK button.

Step-3. Now the tool provides the preview of database objects such as tables, stored procedures, views, triggers, functions, indexes, etc. Click on Export to begin to move further & learn how to export stored procedure in SQL server in depth.

Step-4. On the export window, choose the Export to SQL Server Database option. Choose the destination SQL Server Names by clicking on the drop-down arrow and also select the Authentication Mode.

Step-5. Create either a new database or select an existing database to export stored procedures SQL server DB.

Step-6. The software will fetch all the databases and let you select the data items which you want to move. Finally, click the Export button.

Also read: How to Copy Table From One Database to Another Database in SQL Server.

Conclusion

In this post, we have discussed the two most widely used practices to copy stored procedure from one server to another. As per the user’s requirements, they can choose whatever option works best for them. However, in case you have a corrupt or damaged SQL database file (.mdf), you cannot opt for the manual procedure. Then the only way you are bounded to adopt is an automated software solution.

It is a brilliant tool to learn how to copy stored procedure from one database to another even if the browsed MDF file is not accessible. We are sure that users can get the perfect results using this automated tool.