Biegral Blog

---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  

--使用作业定时压缩数据库
DECLARE @Error INT
SET @Error=(SELECT TOP 1 size/128.0 - CAST(FILEPROPERTY([NAME], 'SpaceUsed') AS int)/128.0 AS AvailableSpaceInMB FROM sys.database_files ORDER BY [NAME] DESC)
--PRINT @Error
IF(@Error>1)
    BEGIN
        ALTER DATABASE HCJK_Customer　　--数据库名字
        SET RECOVERY SIMPLE;　　--设置简单恢复模式
        DBCC SHRINKFILE (HCJK_Customer, 1);　　--(M)不能小于1M,参数“HCJK_Customer”应为 SELECT [name] FROM sys.database_files
        DBCC SHRINKFILE (HCJK_Customer_log, 1);　　--(M)不能小于1M,参数“HCJK_Customer_log”应为 SELECT [name] FROM sys.database_files
        ALTER DATABASE HCJK_Customer
        SET RECOVERY FULL;　　--恢复为原来完整模式
    END
--ELSE
--BEGIN
--    PRINT  @Error
--END    

--备份数据库存储过程
ALTER PROCEDURE [BackupDatabase]
	@FolderPath NVARCHAR(500),
	@DatabaseName NVARCHAR(500)
as
DECLARE @FullPath NVARCHAR(1000) 
DECLARE @Sql NVARCHAR(MAX)
	SET @FullPath = @FolderPath + @DatabaseName + '_' + CONVERT(NVARCHAR(4), YEAR(GETDATE())) 
					+ RIGHT('0' + CONVERT(NVARCHAR(2), MONTH(GETDATE())), 2)
					+ RIGHT('0' + CONVERT(NVARCHAR(2), DAY(GETDATE())), 2) + '.bak'
	SET @Sql='backup database ['+@DatabaseName+'] to disk='''+@FullPath+''' WITH INIT '	
	PRINT @Sql
	EXEC(@Sql)


--执行数据库备份
EXEC BackupDatabase @FolderPath='E:\mybak\',@DatabaseName='SE_DB'