SQL 2005資料庫備份與壓縮-預存程序
這是適用於SQL Server 2005，對所有資料庫進行檔案壓縮與備份的預存程序。
單一資料庫壓縮檔案
/*----------------------------------------------------
description: 壓縮SQL Server 的資料庫 Log 檔案
author: Robin
date: 2012/04/02
testing code:
-----------------------------------------------------
EXEC proc_Shrink_DataBase_File 'GoodwaySCM'
-----------------------------------------------------*/
CREATE procedure [dbo].[proc_Shrink_DataBase_File]
@dbname nvarchar(256)
AS
DECLARE @SQLString nvarchar(3000);
SET @SQLString='
use master ;
--備份目前的 log
backup log ['+@dbname +'] to disk=''E:\SQL_BACKUP\db.log'' WITH NOFORMAT ;
--將資料庫復原模式切換到簡單模式
ALTER DATABASE ['+@dbname +'] SET RECOVERY SIMPLE WITH NO_WAIT ;
--找到 DatabaseNameLog 的值
use ['+@dbname +'];
declare @name varchar(50)
select @name=name from sys.database_files
where type_desc = ''log'' ;
--縮減 log file 到 1MB
DBCC SHRINKFILE(@name, 1) ;
--將資料庫復原模式切換到完整模式
USE [master] ;
ALTER DATABASE ['+@dbname +'] SET RECOVERY FULL WITH NO_WAIT ;' ;
EXEC sp_executesql @SQLString
備份與壓縮所有資料庫
/*----------------------------------------------------
description: 備份所有資料庫
author: Robin
date: 2011/11/07
testing code:
-----------------------------------------------------*/
CREATE procedure [dbo].[proc_Backup_All_DataBase]
--@Database_name varchar(50)
AS
SET NOCOUNT ON;
--儲存路徑
DECLARE @path varchar(100)
SET @path='E:\SQL_BACKUP\'
--取得現在時間
DECLARE @nowdate varchar(10)
SET @nowdate=rtrim(CONVERT(char, getdate(), 112))
-- 用來暫存資料庫名稱的變數
DECLARE @dbname nvarchar(256)
DECLARE icur cursor static for
select name from sys.databases
where name not in ( 'master', 'model', 'msdb', 'tempdb', 'ReportServerTempDB', 'ReportServer')
OPEN icur
FETCH NEXT FROM icur INTO @dbname
WHILE(@@FETCH_STATUS=0)
BEGIN
DECLARE @SQLString nvarchar(3000);
-- 壓縮資料庫
--SET @SQLString='DBCC SHRINKDATABASE( ['+@dbname+'] , 10);';
--EXEC sp_executesql @SQLString
BEGIN TRY
EXEC proc_Shrink_DataBase_File @dbname
END TRY
BEGIN CATCH
PRINT ERROR_MESSAGE()
END CATCH
-- 備份資料庫
DECLARE @file_name varchar(100)
SET @file_name=@path+@dbname+@nowdate+'.bak';
SET @SQLString='BACKUP DATABASE ['+@dbname+']
TO DISK = '''+@file_name+'''
WITH NOFORMAT, INIT, SKIP, REWIND, NOUNLOAD, STATS = 10;';
BEGIN TRY
EXEC sp_executesql @SQLString
END TRY
BEGIN CATCH
PRINT @SQLString
PRINT ERROR_MESSAGE()
END CATCH
FETCH NEXT FROM icur INTO @dbname
END
CLOSE icur
DEALLOCATE icur
--刪除7天前的備份
DECLARE @deldate varchar(10)
SET @deldate = CONVERT(char, DATEADD(day,-6,GETDATE()),111)
DECLARE @d varchar(20)
SET @d = CONVERT(char, getdate(), 111)
EXECUTE master.dbo.xp_delete_file 0,@path,N'bak',@deldate