Windows Azure SQL Database Cannot be Set to Simple Recovery Model

February 27, 2013 — 2 Comments

The Recovery Model is set when the master database is created and cannot be modified because the ‘master’ database is read only for anyone but Microsoft. Furthermore, keep in mind that  SQL Databases are continuously being replicated. Windows Azure keeps at least 3 copies of your SQL Database at all times.

To verify the current Recovery Model settings of your SQL Databases, log into the ‘master’ database and execute the following statement:

SELECT name, recovery_model_desc
   FROM sys.databases
GO

In my case I got the following result

2013-02-27_01h20_35

Both of my SQL Databases are set to FULL Recovery Model

 

Full Recovery Model

Requires log backups. No work is lost due to a lost or damaged data file.Can recover to an arbitrary point in time (for example, prior to application or user error). For information about database backups under the full recovery model, see Full Database Backups (SQL Server) and Complete Database Restores (Full Recovery Model).

 

Work loss exposure

Normally none. If the tail of the log is damaged, changes since the most recent log backup must be redone.

 

Recover to point in time?

Can recover to a specific point in time, assuming that your backups are complete up to that point in time. For information about using log backups to restore to the point of failure, see Restore a SQL Server Database to a Point in Time (Full Recovery Model).

Lets try to set the Recovery Model of my ‘brisebois’ SQL Database to Simple Recovery Model

Simple Recovery Model

No log backups. Automatically reclaims log space to keep space requirements small, essentially eliminating the need to manage the transaction log space. For information about database backups under the simple recovery model, see Full Database Backups (SQL Server).

 

Work loss exposure

Changes since the most recent backup are unprotected. In the event of a disaster, those changes must be redone.

 

Recover to point in time?

Can recover only to the end of a backup. For more information, see Complete Database Restores (Simple Recovery Model).

To change the Recovery Model on ‘brisebois’ I executed the following statement on my master SQL Database.

ALTER DATABASE brisebois SET RECOVERY SIMPLE

Executing the statement will produce the following error.

Msg 102, Level 15, State 1, Line 6
Incorrect syntax near 'RECOVERY'.
SQL Server parse and compile time: 
   CPU time = 0 ms, elapsed time = 0 ms.

 

Summary

Every Windows Azure SQL Database Server has a ‘master’ database. This database is used by Microsoft to keep usage statistics and for monitoring. It’s configured by Windows Azure and is in read only mode for customers.

A SQL Database server is a logical group of databases and acts as a central administrative point for multiple databases. When you create a SQL Database server, a read-only master database is created automatically. The master database keeps track of which logins have permission to create databases or other logins. […] For more information on security administration in Windows Azure SQL Database, see Managing Databases and Logins in Windows Azure SQL Database.

 

References

2 responses to Windows Azure SQL Database Cannot be Set to Simple Recovery Model

  1. 

    So there’s no way to set a DB to simple recovery model?

    Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s