在sqlserver2005/2008中备份数据库，收缩日志文件

---1.先备份数据库(含日志文件）
use myhis
go
backup database myhis to disk='d:\myhis_rzbak'
go


---2.设为简单恢复模式
use [master] 
go
alter database myhis set recovery simple with no_wait 
go
alter database myhis set recovery simple 
go


---3.收缩数据库日志文件为8M
use myhis
go
dbcc shrinkfile(myhis_log,8)
go

---4.重新设为完整恢复模式
use master
go
alter database myhis set recovery full with no_wait 
go
alter database myhis set recovery full
go

alter proc proc_database  
  @database nvarchar(50)
as  
begin  
	---1.先备份数据库(含日志文件） 
	execute('
			use '+@database+'  
			backup database '+@database+'  to disk=''d:\'+@database+'_rzbak''')
 
	---2.设为简单恢复模式 
	execute(' 
			use [master]   
			alter database '+@database+'  set recovery simple with no_wait   
			alter database '+@database+'  set recovery simple')
  
	---3.收缩数据库日志文件为8M 
	execute(' 
			use '+@database+'
			dbcc shrinkfile('+@database+'_log,8)')
			
	---4.重新设为完整恢复模式
	execute('  
			use master
			alter database '+@database+'  set recovery full with no_wait
			alter database '+@database+'  set recovery full') 
	
	---使用示例
	--execute proc_database 'AmazonErp'  
end  

转载于:https://www.cnblogs.com/smartsmile/p/6234144.html