Sql Server 備份檔案管理(自動刪除、到期)

對於 Sql Server 備份檔案的管理，是十分有講究的，特別是對於磁碟空間不大或者DB伺服器過多的管理員來說，真的比較難。同時也比較重要，因為如果不定時清理，磁碟肯定會滿，也會導致新的備份無法進行。因為。當然，人工整理備份檔案最好，但是卻不智能。沒辦法，只能通過程式來做了。

 

1. 利用 管理計劃(Maintenance Plans) 中的備份檔案到期策略和 清除維護 來管理備份檔案

 

a. 設定備份到期詳情：

 

 

b.根據磁碟空間，自動覆蓋到期備份(Maintenance Plans(維護計劃) 中的 清除維護(Maintenance Cleanup) )

 

 

Maintenance Plans(維護計劃)的詳情請參閱：Maintenance Plans(維護計劃)詳解 

 

2.  利用 job 和 T-Sql 來刪除備份檔案

這個做法很悲劇，為什麼呢？因為上面方法的 job 被禁用掉了，我還說，為什麼找不到原因不能刪除檔案，不認真啊，不過這種尋找方法還是蠻能學習的，希望對大家有所協助

首先，google 一下，卻發現，刪除 備份檔案的都是 這段 SQL：

--刪除到期的備份檔案

 

 

declare @str varchar(100),@dir varchar(100),@fileName varchar(30)

set @dir='del D:\DBtext\jgj\DBABak\'

set @filename=left(replace(replace(replace(convert(varchar,getdate()-15,20),'-',''),' ',''),':',''),8)

set @str=@dir+'fullbak'+@filename+'*.bak'

exec xp_cmdshell @str

set @filename=left(replace(replace(replace(convert(varchar,getdate()-8,20),'-',''),' ',''),':',''),8)

set @str=@dir+'diffbak'+@filename+'*.diff'

exec xp_cmdshell @str

set @filename=left(replace(replace(replace(convert(varchar,getdate()-8,20),'-',''),' ',''),':',''),8)

set @str=@dir+'logbak'+@filename+'*.trn'

exec xp_cmdshell @str

雖然這段 SQL 能達到效果，但是卻十分的弱智，十分的不智能

1. 這短Sql 要手工的輸入備份檔案磁碟地址，要拼參數，拼參數，通用性十分不好，對於不同的 DB，都要再次手工更改，非常不智能！

2. 不能智能的得到哪個備份到期了

 

之後，我就對這段 Sql 語句進行了改造，改造成了更通用，更智能的刪除備份檔案：

-- use the cursor to delete the expire bak

-- also could not user the cursor if you only want to delete the top 1 oldest bak

-- @filePath : the expire bak's path

declare fileCursor CURSOR for 

SELECT * from

(

SELECT TOP 3 b.physical_device_name    

FROM [msdb].[dbo].[backupset] a,[msdb].[dbo].[backupmediafamily] b  

where a.media_set_id=b.media_set_id  and [expiration_date]<GETDATE()

order by [expiration_date] asc

) 

as filetable

declare @filePath varchar(100)  

open  fileCursor  

fetch next from fileCursor into @filePath  

while @@fetch_status=0  

begin  

declare @delCmd varchar(100)

set @delCmd =('del '+@filePath)

-- user xp_cmdshell to delete the bak

exec xp_cmdshell @delCmd

fetch next from fileCursor into @filePath 

end  

close fileCursor  

deallocate fileCursor 

雖然這種方法看起來很高科技，但是還推薦你使用第一種方法，除非的sql server 版本過老，不支援 維護計劃，另外第二種方法需要開啟 xp_cmdshell ，是需要重啟 Sql Server 服務的

 

另外分享一條SQL語句，發現很多同學還不會用，蠻好的，但是不解釋了，希望能協助大家:

 

with #pager as
(
select FollowID,COUNT(FollowID) as num

from Table2

where FollowType =2 and FollowID>0

group by(FollowID)
)

update Table1 set FollowCount=FollowCount+num
from #pager as p,Table3 c
where p.FollowID=c.ID