SQL Server: Taking a Database Backup using Microsoft Azure

There are two ways of generating a backup for a database that is currently on a server You can do it directly in SQL Server Management Studio (SSMS) through a simple export and import process. Doing it this way, however, requires taking the backup at a time when there are no parties interacting with the database (particularly making write operations) and may result in data integrity issues.

The other way of taking the backup is through a copy process using the Azure console. This way seems to be better as it much faster and will not result in data integrity issues as it is a snapshot of the database when the copy is applied.

In this technical walk thru I will show you these two methods of generating a backup of the Azure Database and restoring to another server.

Method 1: Copy the Database Using the Azure Console

1. Navigate to the database you intend to take a backup of. This can be found in the Azure console (https://portal.azure.com/#home ) under the resources section.

2. Click on the Copy button found at the top.

3. Add the settings as follows. (Note that the highlighted blue parts are meant to be replaced with your own settings.) Furthermore, using an elastic pool versus compute + storage is dependent on your required database performance.

4. Create a Database Name that does not conflict with any of the names of databases on the chosen server.
Make sure that the server is set to the destination server.

5. Select Next: Review + Create and click Create at the bottom

6. It will now move to a deployment page that should say whether the process was successful or not. This may take some time.

7. Once the deployment section is finished, head over to the Resources section and filter for SQL database.

8. You should now see the copy of the Database under the name that you entered from before.

---

Restoring Backup

1. Now that we have our restored backup, we need to go into SQL Server Management Studio and rename the Databases. For this demonstration, we will be restoring our backup to a database named UAT that is located on the same destination server where we have our backup.
2. Navigate to SQL Server Management Studio (SSMS)
3. Connect to the UAT database using the Server Admin Account and rename it as UAT_BCK . You can do this by right clicking on UAT and selecting Rename (renaming it will cause an error to pop up but it’s fine). Wait a few minutes and you can verify the name change in the Azure console.
   
   *Click to Enlarge
4. Connect to the backed-up database using the Server Admin Account. Rename this Database to UAT.
5. Disconnect from this database and reconnect to UAT using the Server Admin Account.
6. The following will apply if you cannot connect to the new database without the Server Admin account. Otherwise skip to step 9.
7. Run the following script below:
   1. First find out what database roles the LoginAccount account has (this will be the account that you try to connect to the database with, usually just db_owner )
   2. Drop and Recreate the user

---- To find out the roles
SELECT 
DP1.name AS DatabaseRoleName,
isnull (DP2.name, 'No members') AS DatabaseUserName
FROM sys.database_role_members AS DRM
RIGHT OUTER JOIN sys.database_principals AS DP1
ON DRM.role_principal_id = DP1.principal_id
LEFT OUTER JOIN sys.database_principals AS DP2
ON DRM.member_principal_id = DP2.principal_id
WHERE DP1.type = 'R'
ORDER BY DP1.name;

---- drop and recreate
begin tran
DROP USER [LoginAccount];
GO

CREATE USER [LoginAccount] FOR LOGIN [LoginAccount] WITH DEFAULT_SCHEMA=[dbo]
GO

ALTER ROLE db_owner ADD MEMBER [LoginAccount];
GO

8. Commit tran
9. You now have successfully backed up your Source database and restored your destination database (UAT in the above example).

---

Method 2: Export/Import of Data

This method is dangerous in that it must be taken at times when no party is interacting with the database. It is also much slower and can take a few hours.

The export builds a DAC (deployed data-tier application) definition in the export file – BACPAC file which will be the backup. More information can be found at Export a Data-tier Application – SQL Server

The import takes a BACPAC file to create the new database. More information can be found at Import a BACPAC file to Create a New User Database

Conclusion

Taking an Azure database backup is very easy to do through the Azure console. Just following these steps will allow you to create a backup within a reasonable time frame and will allow you to keep the data integrity of the database. It is a much cleaner approach to use the Azure console as opposed to the Import/Export method shown in the Microsoft SQL Docs.

Indellient takes a customer-first approach to help you build a modern cloud strategy on Amazon Web Services, Windows Azure, and Google Cloud Platform. Our team can help you build, replatform, migrate and integrate applications, so you can benefit from the scalability, agility, and performance available through cloud technologies.

Indellient is an IT Professional Services Company that specializes in Data Analytics, Cloud Services, DevOps Services, and Business Process Management.

---