/*
Modify the recovery model for all user databases to Simple mode
*/
EXEC Sp_msforeachdb '
DECLARE @dbname VARCHAR (30)
SET @dbname = '? '
DECLARE @exsql VARCHAR (500)
IF (@dbname not in (
SELECT name from sys.databases WHERE database_id<=4--Non-system table
or recovery_model_desc= ' simple '--non-easy recovery mode
or name= ' ReportServer ' or name= ' reportservertempdb '--Non-report library
OR Is_read_only=1--not read-only
OR state=6--Non-offline
or Is_distributor=1--non-subscription library
or is_published=1--non-publishing library
or is_subscribed=1--Non-distribution library
UNION
SELECT db_name (database_id) from sys.database_mirroring WHERE mirroring_role are not NULL--non-mirrored library
))
BEGIN
SELECT @dbname =name from master.sys.databases with (NOLOCK) WHERE [email protected]
Set @exsql = "Use master ALTER DATABASE" [email protected]+ ' Set recovery simple with no_wait '
Begin try
EXEC (@exsql)
Print @dbname
End Try
Begin Catch
Select @dbname +error_message ()
End Catch
END
‘
/*
After database restore, modify the owner of all databases to SA (prevents SSB from not being used)
*/
EXEC Sp_msforeachdb '
DECLARE @dbname NVARCHAR (100);
DECLARE @sql NVARCHAR (max);
SELECT @dbname = '? ';
if (@dbname not in (
SELECT name from sys.databases WHERE database_id<=4--Non-system table
--or recovery_model_desc= ' simple '--non-easy recovery model
or name= ' ReportServer ' or name= ' reportservertempdb '--Non-report library
OR Is_read_only=1--not read-only
OR state=6--Non-offline
or Is_distributor=1--non-subscription library
or is_published=1--non-publishing library
or is_subscribed=1--Non-distribution library
UNION
SELECT db_name (database_id) from sys.database_mirroring WHERE mirroring_role are not NULL--non-mirrored library
))
Begin
SELECT @dbname =name from master.sys.databases with (NOLOCK) WHERE [email protected]
Set @sql = ' use ' + @dbname +
' EXEC sp_changedbowner ' ' sa '
Begin try Execute (@sql) print @dbname End Try
Begin catch Select @dbname +error_message () End catch
End
‘
Bulk Modify the recovery model and DB owner for all libraries