quarta-feira, 14 de novembro de 2012


SQL Script for set Recovery Model to Single in All Databases on Microsoft SQL Server (2000, 2005 and 2008)



SQL 2000:



USE MASTER
declare
@isql varchar(2000),
@dbname varchar(64)
declare c1 cursor for select name from master..sysdatabases where name not in ('master','model','msdb','tempdb') and status != '4194824'
open c1
fetch next from c1 into @dbname
While @@fetch_status <> -1
begin
select @isql = 'ALTER DATABASE @dbname SET AUTO_CLOSE OFF'
select @isql = replace(@isql,'@dbname',@dbname)
print @isql
exec(@isql)
select @isql = 'ALTER DATABASE @dbname SET RECOVERY SIMPLE'
select @isql = replace(@isql,'@dbname',@dbname)
print @isql
exec(@isql)
select @isql='USE @dbname checkpoint'
select @isql = replace(@isql,'@dbname',@dbname)
print @isql
exec(@isql)
fetch next from c1 into @dbname
end
close c1
deallocate c1


SQL 2005:


USE MASTER
declare
@isql varchar(2000),
@dbname varchar(64)
declare c1 cursor for select name from master..sysdatabases where name not in ('master','model','msdb','tempdb') and status != '4194840'
open c1
fetch next from c1 into @dbname
While @@fetch_status <> -1
begin
select @isql = 'ALTER DATABASE @dbname SET AUTO_CLOSE OFF'
select @isql = replace(@isql,'@dbname',@dbname)
print @isql
exec(@isql)
select @isql = 'ALTER DATABASE @dbname SET RECOVERY SIMPLE'
select @isql = replace(@isql,'@dbname',@dbname)
print @isql
exec(@isql)
select @isql='USE @dbname checkpoint'
select @isql = replace(@isql,'@dbname',@dbname)
print @isql
exec(@isql)
fetch next from c1 into @dbname
end
close c1
deallocate c1



SQL 2008:


USE MASTER
declare
@isql varchar(2000),
@dbname varchar(64)
declare c1 cursor for select name from master..sysdatabases where name not in ('master','model','msdb','tempdb')
open c1
fetch next from c1 into @dbname
While @@fetch_status <> -1
begin
select @isql = 'ALTER DATABASE @dbname SET AUTO_CLOSE OFF'
select @isql = replace(@isql,'@dbname',@dbname)
print @isql
exec(@isql)
select @isql = 'ALTER DATABASE @dbname SET RECOVERY SIMPLE'
select @isql = replace(@isql,'@dbname',@dbname)
print @isql
exec(@isql)
select @isql='USE @dbname checkpoint'
select @isql = replace(@isql,'@dbname',@dbname)
print @isql
exec(@isql)
fetch next from c1 into @dbname
end
close c1
deallocate c1







SQL Script para mudar o Modelo de Recuperação para Simples em todos os bacos de dados do servidor Microsoft SQL Server (2000, 2005 and 2008)



SQL 2000:



USE MASTER
declare
@isql varchar(2000),
@dbname varchar(64)
declare c1 cursor for select name from master..sysdatabases where name not in ('master','model','msdb','tempdb') and status != '4194824'
open c1
fetch next from c1 into @dbname
While @@fetch_status <> -1
begin
select @isql = 'ALTER DATABASE @dbname SET AUTO_CLOSE OFF'
select @isql = replace(@isql,'@dbname',@dbname)
print @isql
exec(@isql)
select @isql = 'ALTER DATABASE @dbname SET RECOVERY SIMPLE'
select @isql = replace(@isql,'@dbname',@dbname)
print @isql
exec(@isql)
select @isql='USE @dbname checkpoint'
select @isql = replace(@isql,'@dbname',@dbname)
print @isql
exec(@isql)
fetch next from c1 into @dbname
end
close c1
deallocate c1


SQL 2005:



USE MASTER
declare
@isql varchar(2000),
@dbname varchar(64)
declare c1 cursor for select name from master..sysdatabases where name not in ('master','model','msdb','tempdb') and status != '4194840'
open c1
fetch next from c1 into @dbname
While @@fetch_status <> -1
begin
select @isql = 'ALTER DATABASE @dbname SET AUTO_CLOSE OFF'
select @isql = replace(@isql,'@dbname',@dbname)
print @isql
exec(@isql)
select @isql = 'ALTER DATABASE @dbname SET RECOVERY SIMPLE'
select @isql = replace(@isql,'@dbname',@dbname)
print @isql
exec(@isql)
select @isql='USE @dbname checkpoint'
select @isql = replace(@isql,'@dbname',@dbname)
print @isql
exec(@isql)
fetch next from c1 into @dbname
end
close c1
deallocate c1



SQL 2008:



USE MASTER
declare
@isql varchar(2000),
@dbname varchar(64)
declare c1 cursor for select name from master..sysdatabases where name not in ('master','model','msdb','tempdb')
open c1
fetch next from c1 into @dbname
While @@fetch_status <> -1
begin
select @isql = 'ALTER DATABASE @dbname SET AUTO_CLOSE OFF'
select @isql = replace(@isql,'@dbname',@dbname)
print @isql
exec(@isql)
select @isql = 'ALTER DATABASE @dbname SET RECOVERY SIMPLE'
select @isql = replace(@isql,'@dbname',@dbname)
print @isql
exec(@isql)
select @isql='USE @dbname checkpoint'
select @isql = replace(@isql,'@dbname',@dbname)
print @isql
exec(@isql)
fetch next from c1 into @dbname
end
close c1
deallocate c1



Um comentário: