declare @dbName varchar(50) --数据库名称
declare @dbLogName varchar(100) --数据库日志名称
declare dbProperties cursor for select s.name,l.name from Sysdatabases as s,master.sys.master_files as l where s.dbid=l.database_id and type_desc='LOG'
begin
open dbProperties
fetch next from dbProperties into @dbName,@dbLogName
while (@@fetch_status=0)
begin
if(@dbName is not null and @dbName !='' and @dbLogName is not null and @dbLogName!='' and @dbName!='tempdb')
begin
exec('use [master] '+
'alter database ['+ @dbName +'] SET RECOVERY SIMPLE WITH NO_WAIT '+
'alter database ['+ @dbName +'] SET RECOVERY SIMPLE '+ --简单模式
'use ['+ @dbName +'] dbcc SHRINKFILE ("'+@dbLogName+'" , 11, TRUNCATEONLY) '+
'use [master] '+
'alter database ['+ @dbName +'] SET RECOVERY FULL WITH NO_WAIT '+
'alter database ['+ @dbName +'] SET RECOVERY FULL') --还原为完全模式
print @dbName
print @dbLogName
end
fetch next from dbProperties into @dbName,@dbLogName
end
close dbProperties
deallocate dbProperties
end
