Surprisingly, I did not find a concise way on Stackoverflow or elsewhere, so please allow me to ask.
I want transport a complete SQL Server database from one environment to another using the Microsoft SQL Server Management Studio (SSMS).
I guess I need a complete database dump (of model and data) e.g. from the production environment, which I then restore e.g. on the pre-production environment.
I am more the MongoDB guy, and it is straight forward there. But how do I quickly backup, transfer and restore a complete T-SQL database (context) using SSMS? The issue is that I should explain somebody else what to do since I do not have the rights to try it myself.
The official documentation on backup and restore is rather lengthy, but I get the concept, I hope. Except for the copy only backup, maybe. The definition reads:
Copy-only full backups (all recovery models)
A copy-only backup cannot serve as a differential base or differential backup and does not affect the differential base. Restoring a copy-only full backup is the same as restoring any other full backup.
Do I need a copy-only backup for my purpose? If so, why?
Within SSMS on the source server, open the Object Explorer (F8), right click on the DB you want to transport (e.g.myDB) and choose Tasks > Backup...
In the pop-up window, choose Backup Type: Full. Under Destination, choose Back up to: Disk and add a folder.
Navigate to the folder where the .bak file is stored, e.g. C:\Program Files\Microsoft SQL Server\MSSQL 14.MSSQLSERVER\MSSQL\Backups\, transfer it to the destination server onto a similar location.
On the destination server, open the Object Explorer (F8). Make sure you have no database context called myDB, then right click on another context, choose Task > Restore > Database... and then what?
I would also need help with the last step, please. The popup window shows on top No backup set selected to be restored. and keeps freezing if I want to choose the myDB.bak file.
You should use COPY_ONLY if you don't want to mess with the potentially existing backup set. For example, if you have some backup solution that takes differential backups (log backups) in between full backups. You need to use COPY_ONLY if you're manually going to take a FULL backup, otherwise you will make the differential backups useless until the next FULL backup is performed by your backup solution (or you have to use that FULL backup of yours together with the diff backups in case of a point-in-time restore, but you risk having issues using that with your normal backup solution).