How to create a database copy in SQL Azure for backup

You can back up a SQL Azure database by copying it to a new database in SQL Azure. The new database that is created from the copy process is a fully functioning database, independent of the source database once the copy is complete. The new database will have the same edition and maximum size as the source database.

Here's the syntax.

CREATEDATABASE destination_database_name
AS COPY OF [source_server_name.]source_database_name

Copying a database using the CREATE DATABASE statement is an asynchronous operation. Therefore, a connection to the SQL Azure server is not needed for the full duration of the copy process. The CREATE DATABASE statement will return control to the user before the database copy operation is complete. In other words, the CREATE DATABASE statement returns successfully when the database copy is still in progress. You can monitor the copy process with the sys.dm_database_copies and sys.databases views. After the copy process completes successfully, the destination database is transactionally consistent with the source database

Here's the code to monitor the copy process.

SELECT* FROM SYS.DM_DATABASE_COPIES
SELECT* FROM SYS.DATABASES WHERE NAME ='DESTINATION_DATABASE_NAME'

Read the complete MSDN article here: http://msdn.microsoft.com/en-us/library/ff951624.aspx

How to: Calculate SQL Azure DB/Database Size (sp_spaceused unavailable)

The good old sp_spaceused is unavailable in SQL Azure. But fortunately we have the underlying data view available. Its called sys.dm_db_partition_stats.

Here's a simple query to get the total "reserved space" of a database.

SELECT SUM(reserved_page_count) * 8.0 / 1024 AS 'Total Reserved Space (MB)' FROM sys.dm_db_partition_stats

You might also want to get the size of each table seperately. You just have to join it with the sys.objects table and remove the summation.

SELECT Name, reserved_page_count * 8.0 / 1024 AS 'Total Reserved Space (MB)' FROM sys.dm_db_partition_stats PS
JOIN sys.objects SO ON PS.object_id=SO.object_id

Hope this helps!