Why doesn't the SQL Server Management Studio "Copy Database Wizard" include user stored procedures?

Question

Coming from MySQL, MariaDB, PostgreSQL, I would expect it to be easy to create a backup/copy of an entire database including the stored procedures.

In Microsoft SQL Server, at first blush, the simple "Copy Database Wizard" seems like the right tool for copying a database. However, I cannot find a way to make it include the user-defined stored procedures. And I wonder what else it must be not including when it performs the copy. I'm guessing all programmability stuff is completely omitted (triggers, etc).

This is totally perplexing. What am I missing?

Answer 1

Coming from MySQL, MariaDB, PostgreSQL, I would expect it to be easy to create a backup/copy of an entire database including the stored procedures.

Sure, it is. Literally the most basic command to backup the database in its entirety would be:

BACKUP DATABASE YourDatabaseName
TO DISK = '\\SomeFolderShare\BackupFileName.bak`

Two lines of code, for the win.

For more information on database backups, please see:

1. Create a Full Database Backup
2. BACKUP (Transact-SQL)
3. Restore a Backup from a Device

I can't speak specifically on the functionality of the Copy Database Wizard as it's not a feature I've ever had to use. But do bear in mind that SSMS and its features, such as this, are written by completely different developers than those who created Microsoft SQL Server, so it's expected not to be perfect for every scenario.

Alternatively, there are native features provided by the SQL Server product developers to accomplish similar goals, with maximum functionality, such as a basic backup like I mentioned.

• JamesHoux: Ah, ok. That sheds a lot of light.

Answer 2

Objects like stored procedures are "only available when the source and destination are different servers" [1].

What "at first blush" seems obvious is however with historical hindsight "a wrong turn".

What follows is my personal perspective and experience from following SQL Server's development since 1998.

Commands were much more prevalent historically and this new approach to use context menus were not a the foremind of programmers. CLI was The Thing originally.

Over the years Microsoft added the Copy Database Wizard, however the commands Backup and Restore were always there. And the best way to copy a whole database.

Where this fails is when only parts of a database need to be copied or just the data as safety copy. Primarily these requirements became the SSIS driven "Copy Database Wizard".

Source / References:

[1] https://learn.microsoft.com/en-us/sql/relational-databases/databases/use-the-copy-database-wizard?view=sql-server-ver16