SQL 2008 Scheduling Backup and Shrink all db

建置一部 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 資料庫中看到新增兩個預存程序

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

EXEC proc_Backup_All_DataBase

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

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

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

搜尋此網誌

RobinNotes

SQL 2008 Scheduling Backup and Shrink all db

1. 壓縮

2. 備份

完成之後