Pages

SQL 2005 Backup All Database

Jun 5, 2012

這是適用於SQL Server 2005,對所有資料庫進行檔案壓縮與備份的預存程序範例。

當架設好SQL Server後可以先按本文建立預存程序,並將該預存程序在 SQL Server 設定排程,日後新增任一資料庫就可以持續自動備份。

單一資料庫壓縮檔案

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


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