SQL Server Move Stored Procedure to Another Database – Best How to Guide

Users often ask about the process for SQL Server move stored procedure to another database without any difficulties. Well, this isn’t as simple as it looks like. Some users want to copy the Stored  Procedures to different databases but within the same server. However, some users want to move their SP to a completely different server. This is what creates confusion as the ways of executing both operations are different.

To understand the entire scenario, let us first go through the user queries. Then only we can proceed further and find out how to copy stored procedure from one database to another without facing any errors.

Below are the most common queries related to this problem that trouble users from all around the world.

Also Read: How to Restore Stored Procedures in SQL Server Database?

Most Common User Queries Regarding SQL Stored Procedures Migration

Here, we have some common queries of users regarding this problem with slight differences. Let’s have a look at these common queries:

Query-1. Combining Two SQL Databases into One to Copy SPs.

Query-2. Move SPs Using SQL Server 2008 Version.

Query-2. Moving SPs to A Different Server

As we saw in the images, some users want to copy the stored procedures to the same server but a different database, whereas some even want to change the server. Therefore, we are going to look at the solutions for both problems. Hence, we can provide a complete solution to users.

Copy Stored Procedure from One Database to Another Using SSMS

Now, we are going to have a look at the manual solution which users might find easy despite being manual. The reason is that it does not use T-SQL commands, but the SSMS (SQL Server Management Studio) to provide them the desired results.

The Step by Step solution for the same is:

Step-1. Launch the SQL Server Management Studio in the system.

Step-2. Right-click on preferred Database and click on the Tasks option.

Step-3. Now, just Select the Generate Scripts option Under the tasks menu.

Step-4. Select the desired Stored Procedures & Create A File of them to copy.

Step-5. Now, Go to the Target Database & then Run the Script from the created file.

This is the best method for users if they are not proficient enough in SQL technicalities. However, there is not much scope in this method for users to customize their operation. Let’s have a look at the drawbacks of this method:

• The execution order of the scripts might not be correct always.
• Errors occur if stored procedures already exist on the target database.
• Reviewing scripts takes time & SQL proficiency that spoils user experience.

Also Read: Difference BetweenTriggers & Stored Procedures of SQL Server in Depth

SQL Move Stored Procedure to Another Database with T-SQL CMDs

Now, as we have already covered the SSMS method, here comes the second method that includes the command line method. To execute this method, users must be proficient in the T-SQL commands. Otherwise, there is a huge chance of facing error.

Here, @sql is defined as nvarchar(max) and @Target_Database is the destination database name. The command is mentioned below:

DECLARE c CURSOR FOR 

   SELECT Definition

   FROM [Test_Database].[sys].[procedures] p

   INNER JOIN [Test_Database].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 [' + @Target_Database + ']; EXEC(''' + @sql + ''')'


   EXEC(@sql)


   FETCH NEXT FROM c INTO @sql

END             


CLOSE c

DEALLOCATE c

After running this command, users can get the desired solution. The following command comes with a major setback. The drawbacks for SQL Server move stored procedures from one database to another are:

• Require professional knowledge of SQL Server
• Chances of data loss & getting critical errors.

Migrating SQL Stored Procedures to a Different Server with T-SQL Commands

Now, in case users want to execute the same task but with a different server rather than a different database, the script changes. 

This command uses the linked server to execute the task & simply migrate the stored procedure to a different server. All that users need to put in here is:

• Name of the Linked Server
• Destination Database
• Source Database

Now, all that users need to do is just execute the below-mentioned T-SQL script:

[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] ‘Source_Server’, ‘Source_DB’, ‘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]

Utility That Works in Both Scenarios – Advanced Method

Now, as we know both the manual solutions have their drawbacks, we must opt for a neutral method that does not possess any negative effects on the results. Therefore, we have the most advanced SQL Server Migration Tool. This utility is free from drawbacks like getting errors, complex procedures, technical scripts, etc. Download the software & then follow the below-mentioned steps:

Download Now Purchase Now

Step 1. Launch the Software to start the Stored Procedures copy task.

Step 2. Now, Select the Online or Offline mode for data migration.

Step 3. Add the MDF Files(Offline) or the SQL Server(Online).

Step 4. Set Destination Database to move Stored Procedures.

Step 5. Finally, Hit the Export button to finish off the task.

By executing these simple, steps, users can easily copy stored procedure from one database to another database or server. The only drawback here is that this isn’t a freeway. Although the software comes at the price of just one-week grocery. Therefore, experts suggest that this utility is the perfect solution for both experts as well as beginners.

SQL Server Move Stored Procedure to Another Database Automatically – Why???

Now, users must be thinking that what is the reason we should opt for this utility, so, here is the list of benefits that users will get with this outstanding software.

• The software can migrate all SQL Server objects like tables, triggers, stored procedures, views, rules, indexes, schema, etc.
• It provides users with a total of four migration options:
  • Online to Online Migration
  • Online to Offline Migration
  • Offline to Offline Migration
  • Offline to Online Migration
• Users can migrate the database with only schema or with schema and data.
• It is also capable of copying & exporting the primary & foreign key constraints.
• Selective data migration feature makes this software flexible for customization.
• Most importantly, it can execute SQL move stored procedure to another database or another server both.

When we compare the drawbacks & benefits of this software, we can see that it outperforms all of the manual ways. Therefore, it is the first choice of users, experts 7 even Microsoft MVPs as well.

Also Read: Restore Stored Procedures from Backup File in Database

The Final Say

Finally, now, users are well aware of the best ways for SQL Server move stored procedure to another database or another server. All the solutions mentioned above work fine but selection depends on users & their understanding of SQL technicalities.

At last, we can say that the ideal solution as per our analysis is the automated utility as it is fast, saves time, and protects data from unwanted corruption, & comes with plenty of other benefits. However, if users are short on budget, & looking for a free solution, they can opt for the manual ways.