1

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?

Recipe Draft

  1. 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...

  2. In the pop-up window, choose Backup Type: Full. Under Destination, choose Back up to: Disk and add a folder.

  3. 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.

  4. 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.

References

Community
  • 1
  • 1
B--rian
  • 5,578
  • 10
  • 38
  • 89
  • Ahem ..... just **(1)** do a `BACKUP` of your database on the source server, **(2)** copy that resulting `.bak` file onto a USB stick or copy it to wherever it needs to be, and **(3)** do a `RESTORE` on the target server...... I don't see what is wrong with this approach?!?!? You can script out everything in T-SQL using SSMS .... – marc_s Dec 11 '19 at 10:25
  • @marc_s: Nothing wrong, that's exactly what I am trying to do, I am just asking about the details. Why? Well, I have to write a documentation for something I have done for somebody who has never done that, plus I cannot test my recipe, that's why I am asking for help here. – B--rian Dec 11 '19 at 10:28
  • @B--rian Do you need transfer data From SQLSERVER To MONGODB ? please say , what do you? in content you write correct way for restore backup in SSMS. pleas upload your error message by picture or text. – Amirhossein Mar 02 '20 at 12:37

1 Answers1

1

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).

Cedersved
  • 1,015
  • 1
  • 7
  • 21
  • So you are saying if there are multiple backups running (automatically), I can can safely make a one-off snapshot (with `copy_only`) which does not interfer with the other backup processes? Cool! What about my 2nd issue (of detailing out step 4 in my recipe) – B--rian Dec 11 '19 at 11:01
  • @B--rian Do you need mirror of sql server data in mongoDB ? – Amirhossein Mar 02 '20 at 12:38
  • 1
    @Amirhossein I want to transfer data from on SQL-server to another one. I suggest you ask a new question - I might be able to help you on that. – B--rian Mar 02 '20 at 13:07
  • @B--rian tank you , this my question https://stackoverflow.com/questions/60490433/i-want-to-transfer-data-from-on-sql-server-to-nosql-structure – Amirhossein Mar 02 '20 at 14:00