I tried searching online for an answer to the above question but all I see are the long documentations on performing these operations. What I'm looking for is a brief description of how do the following operations differ and when one is preferred (needed) over the other?
This question will be helpful.
Copy database allows you to do the following :
- Pick a source and destination server.
- Select databases to move, copy or upgrade.
- Specify the file location for the databases.
- Create logins on the destination server.
- Copy additional supporting objects, jobs, user-defined stored procedures, and error messages.
- Schedule when to move or copy the databases.
In my own word, I would say that copy database allows you to easily move a database from an instance of SQL server to another one.
Export database save the database to a file in the OS file system (like a backup). You then can copy those files and import them whenever and wherever you want.
In my opinion, backing up your SQL Server database is essential for protecting your data. For database, there are many method, you could copy your data, like copy your instance, export all your database.
This Microsoft Azure blog post provides some information:
Backup: Provides database backups, including transactional consistent backups and supporting disaster recovery, but does not support on premise restores
Export: Makes a point in time copy of a database, can be restored on premise, was formerly used for backups before the Backup service was available. A copy is required before you can do the "backup."
Copy: Per this article, Copy does exactly what its name implies, making an exact copy of the database in question to either the same server or a different server.
Depending on your needs, you may be using some combination of all of the operations above. For example, if you need on premise backups, you would not use backup, you'd use a combination of Copy and Export. Talk to your system architect to get a better idea of which operations you should use.