One of the questions I get a lot from customers is how they can backup their SQL Azure database. You cannot just open SQL Server Management studio, create a maintenance plan and schedule a backup every night.
So what do we have to do instead?
Let me first mention that in Azure your data is always stored on multiple instances on different independent hardware in the data center. At any one time,there are three replicas of data running – one primary replica and two secondary replicas. At the same time Microsoft performs periodic offsite backups of the data in case of a catastrophic failure at the data center. This will already limit the risk of loosing your data in case of a hardware failure. But of course a user can always make a mistake and accidently remove some important data.
So even on SQL Azure a good backup strategy is vital.
To perform a backup in SQL Azure a transactional mechanism is used that copies the data without any downtime to the source database. The database is copied in full to a new database in the same datacenter. You can choose to copy to a different server (in the same data center) or the same server with a different database name.
A new database created from the copy process is what they call ‘transactionally consistent’ with the source database at the point in time when the copy completes. This means that the snapshot time is the end time of the copy, not the start time of the copy. This is important to keep in mind when you plan your backup schedule.
Create a backup
A backup can be created by executing the following T-SQL:
1: CREATE DATABASE destination_database_name
2: AS COPY OF [source_server_name.]source_database_name
This command must be execute when connected to the master database of the destination SQL Azure server.
Monitoring the backup process
You can monitor the currently copying database by querying a new dynamic managed view called sys.dm_database_copies.
1: SELECT * FROM sys.dm_database_copies
Permissions Required
Note: When you copy a database to a different SQL Azure server, the exact same login/password executing the command must exist on the source server and destination server. The login must have db_owner permissions on the source server and dbmanager on the destination server.