After a database corruption or failure, you’ll need a SQL database backup to recover your data. Not only can a backup save you from prolonged downtime and lost data, but it’s also a requirement for regulatory compliance. Azure offers a Backup Center portal for backing up SQL data manually or automatically.
What Is Azure SQL Server and SQL Database?
An Azure SQL Server instance is a serverless solution that gives administrators access to Microsoft SQL Server software with no installation required. Administrators simply spin up an instance from the Azure portal, and the database service is available. The SQL database service could host any engine, including Microsoft SQL Server, PostgreSQL, MySQL, Oracle, or other database provider.
Administrators can also create a virtual machine (VM) to host a SQL database or create a database using the portal dashboard. The VM hosts the SQL server in the same way an on-premises server hosts database services. When you create a serverless SQL server, the system automatically creates a backup and does additional differential backups depending on your database workload.
Can You Back Up Your Azure SQL Database?
While your database will run smoothly most days, you might run into malware incidents or inexplicable data corruption. An Azure backup will solve the problem of deleted or corrupted data from malware and give you the ability to restore data to its original state. The Azure administrator dashboard provides you with the tools to create a backup.
Disaster recovery plans are necessary when a cyber incident affects data integrity, and backups are necessary to satisfy disaster recovery requirements. They’re also necessary for compliance (e.g., HIPAA). Malware such as ransomware can corrupt data and force organizations to shut down production, but a backup can recover data so that downtime is minimized. Also, database administrators occasionally make mistakes and could delete tables or corrupt data, and a backup helps recover from these incidents.
The Azure dashboard has a service named Azure Backup. When you create a VM, the Azure service will automatically set a backup and retention plan for VM backups. Azure installs an agent on the SQL server so that the backup service can discover it and create backups automatically.
To manually create a backup, first go to your Azure portal and type “backup center” to open the Backups dashboard. Click the New button to start the backup process. The process is two steps:
- Step 1: The discovery process displays all databases on your cloud network. Choose the databases that you want to back up.
- Step 2: Configure the backup frequency (e.g., hourly), retention timeframe, and the items that you want to back up.
Click Enable Backup and now you can execute the backup manually. If you choose not to back up manually, a new backup will be created based on the policy configurations.
Backing Up an Azure SQL Database to Local
SQL Server Management Studio (SSMS) has a feature that enables you to export a database to your local storage location. You need SSMS installed on your local machine, and you must have remote access to the SQL database.
SSMS lets you take a full backup in a .bak file format. This file format is used when you want to take a full snapshot of your entire database, and it can be used to restore it if you need to recover data and database objects.
In the SSMS interface, right-click the database name and choose Tasks. In the Tasks submenu, click Back Up. A window opens asking where you want to store your backup and the name of the backup file. Click Add to add a local storage location. You can add multiple locations for your backup. After configuring the backup, click OK and SSMS will take a backup of your database.
Restoring Azure SQL Database Backups
The .bak file created as a backup can be used to restore your database. If you’re using the Azure backup service, the entire VM or database instance will be backed up and can be restored to a specific time of your choice. Serverless SQL Server instances can also be restored to a specific time. For example, suppose that you installed software that corrupted database services. You can roll back changes by restoring the virtual machine or the hosted instance to a previous state.
To restore your database hosted on a virtual machine, go to your Azure portal and search for “backup center” to open it. In the Backup Center, click the Restore tab. Choose the datasource type, which would be Azure virtual machines. Choose a backup instance and click Continue. The next window asks you to choose the restore point, which is the time at which you want to roll back. After you’re finished, click OK to begin the restore.
For serverless databases, type “sql instances” in the Azure portal and open it. Click the database instance that you want to restore, and click the Restore tab in the dashboard. Choose a restore point and a database name and click OK to start the process.
Databases hosted in Azure can also be restored using the .bak file stored on your local storage device. You can restore the database using SSMS. Open SSMS and right-click the Database object in the Explorer panel. In the window that appears, choose the .bak file in the Device section and type the name to give the restored database in the Destination section. Click OK to begin the process.
Free and Open Source Azure Database Backup Tools
Several free and open source Azure database backup tools are available to help you manage servers on Azure or your own on-premises environment. Here are a few popular ones to check out:
- AzCopy: This Azure-specific command line tool can be used to copy to or from any Azure storage location.
- SQLPackage: This command line utility can also extract and update database schemas as well as take backups of a database in Azure.
- DBeaver: For developers using more than one database platform, DBeaver offers cross-platform support for several functions, including development, administration, analytics, and data transformations in MySQL, MariaDB, PostgreSQL, SQLite, SQL Server, and more.
- SQL Server Management Studio (SSMS): The SSMS tool has long been the standard way to manage Microsoft SQL Server databases. It can be used for development, configuration, and management of any SQL Server.
Conclusion
Database backups are essential in business continuity and disaster recovery. Azure has features that enable you to back up databases and store them in the cloud or on a local directory. Make sure you take regular backups of all databases to avoid losing your important data permanently.
