/*============================================================================
  File:     2_Changing_Recovery_Model.sql

  Summary:  This script shows how to change recovery model for a database.

  Date:     April 2018

  SQL Server Version: 2005/2008/2008R2/2012/2014/2016/2017
------------------------------------------------------------------------------
  Written by Erin Stellato, SQLskills.com

  For more scripts and other useful content, go to:
	http://www.SQLskills.com

  This script is intended only as a supplement to demos and lectures
  given by Erin Stellato.  
  
  THIS CODE AND INFORMATION ARE PROVIDED "AS IS" WITHOUT WARRANTY OF 
  ANY KIND, EITHER EXPRESSED OR IMPLIED, INCLUDING BUT NOT LIMITED 
  TO THE IMPLIED WARRANTIES OF MERCHANTABILITY AND/OR FITNESS FOR A
  PARTICULAR PURPOSE.
============================================================================*/

/*
	Remember that any newly created database picks up
	database settings from the model database
*/
USE [master];
GO

SELECT 
	[database_id],
	[name],
	[collation_name],
	[recovery_model_desc],
	[compatibility_level],
	[is_auto_shrink_on],
	[is_auto_create_stats_on],
	[is_auto_update_stats_on],
	[is_auto_update_stats_async_on],
	[is_parameterization_forced],
	[page_verify_option_desc]	
FROM [master].[sys].[databases]
WHERE [name] = 'model'
ORDER BY [database_id];
GO


/*
	It's extremely easy to create a database
	I don't recommend this syntax
*/
CREATE DATABASE [MovieInfo];
GO


/*
	Check settings after database is created
*/
SELECT 
	[database_id],
	[name],
	[collation_name],
	[recovery_model_desc],
	[compatibility_level],
	[is_auto_shrink_on],
	[is_auto_create_stats_on],
	[is_auto_update_stats_on],
	[is_auto_update_stats_async_on],
	[is_parameterization_forced],
	[page_verify_option_desc]	
FROM [master].[sys].[databases]
WHERE [name] in ('model', 'MovieInfo')
ORDER BY [database_id];
GO


/*
	To change the recovery model, use ALTER DATABASE
*/
ALTER DATABASE [MovieInfo] SET RECOVERY SIMPLE;
GO


/*
	re-check settings
*/
SELECT 
	[database_id],
	[name],
	[collation_name],
	[recovery_model_desc],
	[compatibility_level],
	[is_auto_shrink_on],
	[is_auto_create_stats_on],
	[is_auto_update_stats_on],
	[is_auto_update_stats_async_on],
	[is_parameterization_forced],
	[page_verify_option_desc]	
FROM [master].[sys].[databases]
WHERE [name] = 'MovieInfo'
ORDER BY [database_id];
GO


/*
	When you inherit a new instance, check the
	recovery model for all user databases
*/
SELECT 
	[database_id],
	[name],
	[recovery_model_desc]
FROM [master].[sys].[databases]
WHERE [database_id] > 4
ORDER BY [database_id];
GO


/*
	Quick method to generate ALTER DATABASE 
	statements for all databases
*/
SELECT 
	'ALTER DATABASE [' 
	+ [name]
	+ '] SET RECOVERY FULL;'
FROM [sys].[databases]
WHERE [database_id] > 4
AND [recovery_model_desc] = 'SIMPLE'
ORDER BY [database_id];
GO


/*
	clean up
*/
USE [master];
GO

DROP DATABASE [MovieInfo];
GO