Create and Monitor a Backup of a Database in SQL Azure

SQL Azure provides a scriptable, easy way to create a backup of a database from within SQL Management Studio. This is very useful if you need to create a backup before making a large production change, or if you just need an archive/snapshot of your current data.

Additionally, SQL Azure gives you the ability to check on the status of the backup via a SQL query. Using that, we can create a small script to monitor the status of the backup and let us know when it has finished:

CREATE DATABASE TheNameOfYourDatabaseBackup AS COPY OF TheNameOfYourDatabaseToBeBackedUp

GO

PRINT 'Backup started at ' + CAST(DATEADD(hh, -5, GETDATE()) AS VARCHAR(100))

WHILE (SELECT state\_desc FROM sys.databases WHERE Name = 'TheNameOfYourDatabaseBackup') = 'COPYING' BEGIN -- Check every minute to see if the backup has completed WAITFOR DELAY '00:01' END

PRINT 'Backup completed at ' + CAST(DATEADD(hh, -5, GETDATE()) AS VARCHAR(100))

GO

A few notes:

• Azure enforces the CREATE DATABASE X AS COPY OF Y to run as its own batch. The GO statements should take care of that, but I have not tested it. If you get an error, just run that line on its own.
• I am shifting the GETDATE() function to my timezone. Change -5 to whatever your GMT offset is.
• WAITFOR DELAY '00:01' delays the check for one minute. You can change this to whatever suits your needs: '1:00' for every hour, '00:00:01' for every second, and so on.