Sql Server - copy stored procedures from one db to another

sql

sql-server

stored-procedures

database-migration

byNikita Barsukov·Dec 4, 2024

To transport stored procedures between SQL Server databases, use these strategies:

Use Generate Scripts in SQL Server Management Studio (SSMS): Right-click your database > Head to Tasks > Select Generate Scripts > Pick your needed procedures > Determine your Output > Execute this script on the destinination database.
Or, utilize the script method: Execute this in the source database, then run the output in the target database:

-- Gather 'round folks, let's get these procedures on the road!
SELECT 'CREATE PROCEDURE ' + 
    QUOTENAME(SCHEMA_NAME(p.schema_id)) + '.' + 
    QUOTENAME(p.name) + 
    ' ' + 
    m.definition 
FROM sys.procedures p
JOIN sys.sql_modules m ON p.object_id = m.object_id
WHERE p.is_ms_shipped = 0
FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 2, '');
-- And voila! A bunch of procedures ready to go!

This query extracts procedure names and definitions, while sorting out schema names and special characters.

Steps to follow and precautions to take

Perform these vital steps:

Watch out for dependencies. Run scripts in a permitted order that respects relationships between procedures and other objects.
Backup your target database to avoid any Titanic sinking-like scenario.
Make sure there are enough permissions to execute the scripts on the target database.
And always justify the copy action by inspecting the target database to ensure all stored procedures traveled safe and sound.

Advanced methods for batch and automated migrations

For regular or batch migrations, consider these:

Use PowerShell or SQLCMD shell to even automate stored procedure migrations.
Use sp_helptext to bring out the text of individual procedures.
Dynamic SQL script and loop constructs can be used for repetitive tasks, but treat them like diet - handle with care!
Configure Task Scheduler to run your PowerShell scripts regularly for periodic copying tasks.

Utilizing Tools and Services for Successful Migration

In a face-off with complex or large-scale migrations, consider leveraging:

Third-party tools like ApexSQL Diff for Dockers-like operations - pull, push, and pop procedures.
SQL Server Integration Services (SSIS) for Washington-Post-level, solid enterprise-wide data integration and transformation.

And don't forget to ensure all companion tables and functions are in place before running scripts, and be cautious if scripts are dropping and recreating objects like it's a magic show!

explain-codes / Sql / Sql Server - copy stored procedures from one db to another

Linked

Sql Server 2005 - Export table programmatically (run a .sql file to rebuild it)



Copy Data from a table in one Database to another separate database



Export table data from one SQL Server to another



How can I backup a remote SQL Server database to a local drive?



Transfer data from one database to another database

