Whilder S. Mendonça | Blog

Yes, it is possible to use a SQL query to change the recovery model of all databases on a SQL Server server to "SIMPLE". To do this, you can use the following query: EXEC sp_msforeachdb 'USE [?]; IF DATABASEPROPERTYEX(''?'', ''recovery'') = ''FULL'' ALTER DATABASE [?] SET RECOVERY SIMPLE;' This query uses the SQL Server function sp_msforeachdb to iterate through all databases on the server and changes the recovery model of all databases that use the "FULL" model to "SIMPLE". Note that this query should be run with care and you should back up your databases before running this query as changing your recovery model may affect your ability to recover data in case of server failures. I hope this helps!

To verify all databases on a SQL Server server that are using the "FULL" transaction log recovery model and have a pending full transaction log backup, you can use the following query: SELECT name AS 'Nome do banco de dados' FROM sys.databases WHERE recovery_model_desc = 'FULL' AND name NOT IN ('master', 'tempdb', 'model', 'msdb') AND database_id NOT IN (     SELECT DISTINCT database_id FROM msdb.dbo.backupset     WHERE type = 'L' AND is_copy_only = 0 AND backup_finish_date IS NULL ) order by name and to check the ones that are simple, just change it to SIMPLE SELECT name AS 'Nome do banco de dados' FROM sys.databases WHERE recovery_model_desc = 'SIMPLE' AND name NOT IN ('master', 'tempdb', 'model', 'msdb') AND database_id NOT IN (     SELECT DISTINCT database_id FROM msdb.dbo.backupset     WHERE type = 'L' AND is_copy_only = 0 AND backup_finish_date IS NULL ) order...