Скрипт вешаем в планировщик:

exec sp_msforeachdb 'use [?];
if "?" not in ( "master", "msdb", "tempdb", "model" )

begin
declare @dbname varchar(255);
declare @filename varchar(255);
declare @string varchar(200);

SELECT @dbname = DB_NAME();
// Если тип восстановления full
SELECT @string="ALTER DATABASE [" + @dbname + "] SET RECOVERY SIMPLE WITH NO_WAIT";
EXEC(@string)

SELECT @filename = name FROM sys.database_files WHERE type = 1;
SELECT @string="DBCC SHRINKFILE (" + @filename + ")";
EXEC(@string)
print @string

SELECT @string="ALTER DATABASE [" + @dbname + "] SET RECOVERY FULL WITH NO_WAIT";
EXEC(@string)

end
'

Комментарии ()