Pages

SQL 2005 Backup All Database Script

Jul 12, 2012


/*----------------------------------------------------
description: 備份所有資料庫SQL 2005
author: Robin
date: 2012/07/12
testing code:
-----------------------------------------------------
EXEC proc_Backup_All_DataBase
-----------------------------------------------------*/
ALTER procedure [dbo].[proc_Backup_All_DataBase]
AS
SET NOCOUNT ON;
--儲存路徑
DECLARE @path varchar(100)
SET @path='D:\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 in ( 'ReportServer', 'ReportServer2', 'ReportServerTempDB', 'ReportServer2TempDB')
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)
SET @file_name = @path+@dbname+@nowdate+'.bak';
    SET @SQLString='BACKUP DATABASE ['+@dbname+']
    TO DISK = '''+@file_name+'''
    WITH NOFORMAT, INIT, SKIP, REWIND, NOUNLOAD, STATS = 10;';

    EXEC sp_executesql @SQLString;

print @SQLString
    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