Is it possible to take a snapshot of a database in Azure?

Solution 1:

Based on my knowledge, for now, Azure SQL database(Paas) does not support snapshot. There are many ways that you could choose to backup SQL database, please refer to this blog:Different ways to Backup your Windows Azure SQL Database.

But for SQL server on Azure VM(Iaas), Azure VM supports create snapshot. You could do it easily on Azure Portal.

More information about the difference between SQL database and SQL server on Azure VM, please refer to this link.

Solution 2:

UPDATE:

In April 2020, Microsoft updated their documentation regarding Azure SQL's Database Copy feature to clarify that a database copy in Azure SQL is not an instantaneous snapshot.

While the Database Copy feature does create a transactionally consistent snapshot, the point-in-time at which the snapshot is taken is not instantaneous and could be at any point in time between making the Database Copy request and an undefined point in time, possibly hours later (though in my experience .

So while a database copy is a snapshot - you must wait (possibly for hours) for sys.databases, sys.dm_database_copies and sys.dm_operation_status to confirm that the copy has been completed successfully before you make any changes to your source database.

Alternative approach:

An alternative, and safer, option is to use Azure SQL's built-in point-in-time recovery to perform a "restore" of the database to a new separate database - this way you'll be sure of the exact date and time of the database - but it still isn't an instantaneous snapshot, however.

Original (and incorrect) answer:

Yes you can - although Microsoft refers to it as the process of duplicating or copying an existing Azure SQL database. The source database is snapshotted while the copy is created - so any further writes to the source database made before the database copy is completed won't be included in the final copy, just like how local SQL Server snapshots using Volume Shadow Copy work.

This is documented here: https://docs.microsoft.com/en-us/azure/sql-database/sql-database-copy

A database copy is a snapshot of the source database as of the time of the copy request.

A database copy is a transactionally consistent snapshot of the source database as of a point in time after the copy request is initiated.

You can accomplish this in different ways:

Note that as a copy of a database is a separate database instance you will have to pay for the copy - but you can use a different pricing tier for the database copy, at least.

Using the Azure Portal:

Using PowerShell:

New-AzureRmSqlDatabaseCopy -ResourceGroupName "myResourceGroup" `
    -ServerName $sourceserver `
    -DatabaseName "MySampleDatabase" `
    -CopyResourceGroupName "myResourceGroup" `
    -CopyServerName $targetserver `
    -CopyDatabaseName "CopyOfMySampleDatabase"

Using T-SQL:

1. First, run this command to initiate the copy operation:

   CREATE DATABASE Database2 AS COPY OF Database1;
   

   The above command will create the copy in the same Azure SQL "Server" as the source database. In my experience this step completes in around 40-50 seconds regardless of the size of pricing tier of the database.

   You can also create the copy on a different Azure SQL Server by instead connecting to the target destination server and running this command:

   CREATE DATABASE Database2 AS COPY OF server1.Database1;
   

   ...where server1 is a configured external data source. See this article for more details and notes, e.g. remapping of logins.

2. While the above command will complete within ~40-50 seconds this only means the copy has started: it does not mean that the copy has completed.

   You will need to babysit SSMS and run this query every few minutes to get a status update for when the copy operation has completed:

   USE [master];
   
   SELECT * FROM sys.databases;
   SELECT * FROM sys.dm_database_copies;
   SELECT * FROM sys.dm_operation_status;
   

   In the third query results (SELECT * FROM sys.dm_operation_status) you'll see operation: CREATE DATABASE COPY, stat_desc: IN_PROGRESS, percent_complete: 50. The the sys.dm_operation_status.percent_complete column will only ever show either 50 or 100.

   To get slightly more accurate progress information, use sys.dm_database_copies.percent_complete (it's still not exactly accurate though). Note that databases with a higher pricing tier will perform their copy operation faster. The new database copy will have the same pricing tier as the source database - so to speed things up I recommend cranking your DTO or vCore numbers really high (this won't cost you more than a couple of USD provided you restore the DTO or vCore number immediately afterwards). If the copy database won't be used you can safely set it to S1 tier or lower after the copy operation has completed.

   In my experience, with a 50GB sized database using Gen5 4x vCores on a same-server copy the copy operation completes within 7 minutes - of course Your Mileage May Vary.

   Note that you'll see two sys.dm_operation_status rows:

   • CREATE DATABASE COPY
     • with a start_time of the CREATE DATABASE commandand alast_modify_timeabout 40-50 seconds afterstart_time`.
   • TERMINATE CONTINUOUS DATABASE COPY
     • with a start_time and last_modify_time within seconds of each other, when the copy was completed, which will be about 6-7 minutes after the CREATE DATABASE COPY's start_time.