Master database in Full recovery model? Maybe not!
Even though is possible to change the recovery model of the master database to FULL as we can see with the next script:
USE [master]; GO ALTER DATABASE [master] SET RECOVERY FULL WITH NO_WAIT GO SELECT sd.name AS N'Database Name' , sd.recovery_model_desc AS N'Recovery Model' FROM sys.databases AS sd WHERE sd.name = N'master'; GO
When we try to make a Log Backup:
BACKUP LOG [master] TO DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\Backup\master.bak' WITH COPY_ONLY , NAME = N'master-Full Database Backup' GO
We get the following error:
That's because it is impossible to do LOG backups of the master database.
At MSDN it's very clear that:
For backwards compatibility with earlier versions of Microsoft SQL Server, the recovery model of master can be set to FULL or BULK_LOGGED. However, BACKUP LOG is not supported for master. Therefore, even if the recovery model of master is changed to full or bulk-logged, the database continues to operate as if it were using the simple recovery model.
So what's the point of having master database in full recovery model?
The best practice to apply to master database is using the simple recovey model and perform a backup as soon as possible after the following operations:
- Creating, modifying, or dropping any database
- Changing server or database configuration values
- Modifying or adding logon accounts
- Configuring end-points or linked-servers