T-SQL Backup Script

	/*----------------------------------------------------
	description: 備份所有資料庫
	author: Nestor
	date: 2014/03/25
	testing code:
	-----------------------------------------------------
	EXEC proc_Backup_All_DataBase
	-----------------------------------------------------*/
	CREATE procedure [dbo].[proc_BACKUP_All_DataBase]
	--@Database_name varchar(50)
	AS
	SET NOCOUNT ON;
	--儲存路徑
	DECLARE @path varchar(100)='C:\SystemLead\MSSQL_BACKUP\Backup_SL\'
	--取得現在時間
	DECLARE @nowdate  varchar(10)=rtrim(CONVERT(char, getdate(), 112))
	--取得要做完整備份的日期 -1 星期日，0 星期一， 1 星期二， 2 星期三， 3 星期四， 4 星期五， 5 星期六
	--預設為星期日：現為預設值
	DECLARE @FullbackupDate varchar(10)=rtrim(CONVERT (varchar(8), DATEADD(wk, DATEDIFF(wk, 0, getdate()), -1),112 ))
	-- 用來暫存資料庫名稱的變數
	DECLARE @dbname nvarchar(256)
	DECLARE icur cursor static for
	select name from sys.databases
	where name not in ( 'master', 'model', 'msdb', 'tempdb', 'MEIHO_MIS')
	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+@FullbackupDate+'.bak';
	IF(@nowdate <> @FullbackupDate)
	BEGIN
	-- 差異備份資料庫
	SET @SQLString='BACKUP DATABASE ['+@dbname+']
	TO DISK = '''+@file_name+'''
	WITH DIFFERENTIAL　, NOFORMAT, NOINIT,  NAME = N'+@dbname+'-Differential'', STATS = 10;';
	END
	ELSE
	BEGIN
	-- 完整備份資料庫
	SET @SQLString='BACKUP DATABASE ['+@dbname+']
	TO DISK = '''+@file_name+'''
	WITH NOFORMAT, INIT, SKIP, REWIND, NOUNLOAD, COMPRESSION,  STATS = 10;';
	END
	FETCH NEXT FROM icur INTO @dbname
	end
	CLOSE icur
	DEALLOCATE icur
	--刪除7天前的備份
	DECLARE @deldate varchar(10)= CONVERT(char, DATEADD(day,-0,GETDATE()),111)
	DECLARE @d  varchar(20)=CONVERT(char, getdate(), 111)
	EXECUTE master.dbo.xp_delete_file 0,@path,N'bak',@deldate

view raw backup.sql hosted with ❤ by GitHub