When it comes to database backups for IaaS based Azure SQL Servers you have 3 options.
Question, can you take a built-in backup ( a backup that Microsoft takes as part of the service) and create a new database from it on a NEW server in a different region?
Let’s find out.
I think many have covered how you should backup your SQL Server database to Azure storage (also known as backup to URL) but what about restoring? Lets assume you have setup backups and they are working, this is what I usually do.
In the previous blog post I did a quick overview building a SQL VM (imaged) in Azure. It is now time to clarify some backup techniques because it can get confusing.
At a high level there are 3 techniques.
- Automated backup.
- Azure backup for SQL VM (that’s what MS call it).
- Manual backup, for example backup to URL.
I prefer not setting up manual backups to storage accounts, I have done it, I just find it painful to setup/support/fix. So my choice would be automated backup vs “Azure backup” for SQL VM. What’s the difference?
Checking out the transaction log in Azure SQL Database. If you are curious like me, you will want to know about what your transaction log is doing in the cloud. The following queries have been tested and run okay within Azure SQL Database it gives you some great insight. First up, the classic log_reuse_wait_desc. You can’t exactly do much with this output, more so, just to fulfill curiosity.
I seem to be writing solely about Azure so to shake things up a bit I am going back to my “roots”. In SQL Server your differential backup is cumulative and NOT incremental and a differential will contain the data that has changed since the last full backup.
Let’s dig in using DBCC PAGE.
Once again I would like to share with you an article I wrote about backing up (and restoring) a SQL Server database to Azure Blob Storage. In this article I write about some important concepts and show you the code needed to do this. This includes all the lovely components such as SQL Server credentials, secrets and SAS etc.
Just because the cloud movement is strong doesn’t mean the end of “DBA’s”, it does mean a change in skills and no doubt you will (one day) create Azure SQL Data Warehouse (DW) in Azure. If you are from an operational background like me then backups will be on your mind for this product. The question is how are backups done with Azure SQL DW?
I have decided to do a summary blog post on backup and recovery options for Azure SQL Database. If you have a DBA background, you will know the importance of securing backups for your SQL Server databases. We have many options to do such a thing; being full, differential, log and filegroup backups. The common question that is asked is how do you backup (and restore) Azure SQL Databases? Or, what are the options available?
Let’s work through some code to do an encrypted backup. This feature is available to you if you are using SQL Server 2014 onwards but I decided to use SQL Server 2017.
To encrypt during backup, you must specify an encryption algorithm, and an “encryptor” to secure the encryption key. I have decided to use the following options:
- Encryption Algorithm: AES 256
- Encryptor: A certificate