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
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.
So there’s no way to set a DB to simple recovery model?
LikeLike
mbmaster, doing so would go against the database as a service model. Where recovering from failure is the extremely important.
LikeLike