You may have missed the recent enhancements to Azure SQL Database service tiers, which Microsoft announced last month. There are three new service tiers (or “editions” if you prefer) of SQL Database that are currently available in preview – basic, standard, and premium. That’s currently in addition to the Web and Business service tiers that were available previously, though these are being retired over the next year. The performance and scalability options that go with these new tiers are pretty compelling, and may make a good subject for a future post. However, the other key new feature that comes with these features is a self-service restore feature that provides a basic disaster recovery capability.
When you create a database with one of the new service tiers, Azure automatically maintains backups that you can use to back-out unintentional changes or recover an accidentally deleted database. The specific options for restoring a database depends on the service tier:
- Basic tier databases can be restored to the most recent daily backup. Backups are retained for 24 hours.
- Standard tier databases can be restored to a specific point in time, and backups are retained for 7 days.
- Premium databases can be restored to a specific point in time in the last 35 days.
You can restore databases using the Azure management portal, or with PowerShell. Restoring an existing database creates a new database of the same service tier with a name that reflects the date and time to which the database has been recovered. For example, suppose you executed a Transact-SQL command that accidentally deleted the contents of a table in your database. Depending on the service tier, you can restore the database to the most recently available recovery point before the data was deleted. After you’ve verified that the recovered database contains the required data, you can delete the original database and the use ALTER DATABASE statement to rename the restored database to match the original name.
If you delete an entire database, it remains listed in the portal until its retention period has expired. If you accidentally delete a database, you can immediately restore it to the most recently available recovery point (again, depending on the service tier).
Of course, this self-service recovery feature isn’t a replacement for a properly planned disaster recovery solution. However, it’s a nice addition to SQL Database that could potentially save you a lot of pain and stress one day!