Pages

SQL 2008 Scheduling Backup and Shrink all db

Jul 3, 2012
建置一部 SQL Server 2008 ,每當建立新的資料庫時,"備份" 會是一個首要的工作,本文提供一個先期的作法,可以針對所有資料庫進行壓縮與備份,這是一個建立在 Master 資料庫上的預存程序,動態查詢所有資料名稱後,再進行Log壓縮與備份動作。備份檔案名稱以原始資料庫加日期命名,可指定保留備份檔案的數量,如只保留近 7 天的檔案。
若需要持續執行只要到 SQL Agent 中 建立一個作業 即可,爾後在建立資料也不怕忘記做備份的設定動作。
有兩個預存程序要建立在 master 資料庫中,建立與測試的 T-SQL 語法 :

1. 壓縮

/*---------------------------------------------------- 
description: 壓縮SQL Server 2008的資料庫 Log 檔案
author: Robin
date: 2012/04/02
testing code:
-----------------------------------------------------
EXEC proc_Shrink_DataBase_File 'TSSdb'
-----------------------------------------------------*/
ALTER procedure [dbo].[proc_Shrink_DataBase_File]
@dbname nvarchar(256)
AS
DECLARE @SQLString nvarchar(3000);
SET @SQLString='
use master ;
--將資料庫復原模式切換到簡單模式
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








2. 備份




/*----------------------------------------------------




description: 備份所有資料庫
author: Robin
date: 2011/11/07
testing code:
-----------------------------------------------------
EXEC proc_Backup_All_DataBase
-----------------------------------------------------*/
CREATE procedure [dbo].[proc_Backup_All_DataBase]
AS
SET NOCOUNT ON;
--儲存路徑
DECLARE @path varchar(100)='E:\Temp\'
--取得現在時間
DECLARE @nowdate varchar(10)=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')
OPEN icur
FETCH NEXT FROM icur INTO @dbname
WHILE(@@FETCH_STATUS=0)
BEGIN
DECLARE @SQLString nvarchar(3000);
-- 壓縮資料庫
EXEC proc_Shrink_DataBase_File @dbname
-- 備份資料庫
DECLARE @file_name varchar(100)=@path+@dbname+@nowdate+'.bak';
SET @SQLString='BACKUP DATABASE ['+@dbname+']
TO DISK = '
''+@file_name+'''
WITH NOFORMAT, INIT, SKIP, REWIND, NOUNLOAD, COMPRESSION, STATS = 10;'
;

EXEC sp_executesql @SQLString;
FETCH NEXT FROM icur INTO @dbname
END
CLOSE icur
DEALLOCATE icur
--刪除7天前的備份
DECLARE @deldate varchar(10)= CONVERT(char, DATEADD(day,-6,GETDATE()),111)
DECLARE @d varchar(20)=CONVERT(char, getdate(), 111)
EXECUTE master.dbo.xp_delete_file 0,@path,N'bak',@deldate








完成之後




執行無誤後可以在 master 資料庫中看到新增兩個預存程序





image





若要做定期排程,可以在 SQL Agent 中新增作業 ,執行下列語法:





EXEC proc_Backup_All_DataBase





以上可以稍調整後,適用於SQL Server 2005。

在 SQL Server Agent 中新增排程作業可參閱:

  -> http://note.robinks.net/2012/07/new-sql-agent-job.html