There are lots of scenarios that require to take the database from a different system than the one you are working on. It is either there is a centralized system from where you need the data, or just you are a lazy developer and want to test your functionality using the same data as the tester/consultant, maybe there is an issue that can’t be reproduced on a set of data and is reproducible on different DB.
You can either directly connect your dev environment to a test machine, or you just can copy the DB from the test env to your dev machine.
Scenario: you have 2 different Azure virtual machines with different DB s, one is a dev machine (target), the other is a test machine (source). You have permissions to the target machine Azure storage subscription. Note: It is not mandatory to work with virtual machines, it is enough to just have 2 different SQL servers and an Azure storage account. To be able to test a functionality / reproduce an issue you need the data from the test machine. Very easy… Steps:
- Go to your Source (Test) machine
- Open SQL Server Management Studio
- In the SQL Server select the DB you want to create a BackUp for. Right Click > Tasks > Back Up…
4. Back Up Database – DB window is opened. Have a look on “Destination” section, and set “Back up to: URL”, press “Add”
5. In “Select Backup Destination”, if you never connected to that storage place before, press “New container”
6. “Sign in” to Microsoft Azure in “Connect to a Microsoft Subscription”
7. Select the Subscription, you want to use, under wish your target storage account is, “Select a subscription to use:”
8. “Select Storage Account”, you will see a list with all the storages you have access, choose your target storage
9. “Select Blob Container”, there you have a couple of containers where you can put your BackUp, but I will recommend just to use “temporary-file”
10. Press “Create Credentials”
11. Press “OK”
12. In “Select Backup Destination” window, press “OK”
13. In “Back up Database” window, a new path is added to the “Destination”, press “OK” and wait
14. The back up was created and now go to your target environment, or target SQL Server, select the DB you want to restore: Right-click > Tasks > Restore > Database…
15. In “Restore Database – DB” window, in “Source” section, choose “Device” and press “…”
16. In “Select backup devices”, choose “URL” and press “Add”
17. In “Connect to a Microsoft Subscription” window “Sign in…” and follow the same steps to connect ass you did in the Source Server. Note: choose the same Storage account and the same Blob container, because that is the place were you saved your backup
18. A new window is opened “Locate Backup File in Microsoft Azure”, expand “Containers” node, select your storage path, and then select your DB backup, press “OK”
19. Press “OK” until you are back to the “Restore Database” window. Please see that a record is added in the “Backup sets to restore” section. In the “Server” colomn you can see the Source server name. Don’t press “OK”, yet. Before that go to “Options” node from the left side of the window.
20. In “Options” node, select “Close existing connections to destination database” in “Server connections” section. If this option is not selected, the restore can’t start because the DB is in use and an error will be displayed.
21. Now you can press “OK” and the restore will start.