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.