數(shù)據(jù)庫自動備份并刪除30天前的備份文件

1、創(chuàng)建備份數(shù)據(jù)庫的存儲過程 -- ============================================= -- Create basic stored procedure template -- ============================================= -- Drop stored procedure if it already exists IF EXISTS ( SELECT * FROM

1、創(chuàng)建備份數(shù)據(jù)庫的存儲過程

-- =============================================
-- Create basic stored procedure template
-- =============================================

-- Drop stored procedure if it already exists
IF EXISTS (
SELECT *
FROM INFORMATION_SCHEMA.ROUTINES
WHERE SPECIFIC_SCHEMA = N'dbo'
AND SPECIFIC_NAME = N'SP_BackUpPortal'
)
DROP PROCEDURE dbo.SP_BackUpPortal
GO

CREATE PROCEDURE dbo.SP_BackUpPortal
@backFolderPath varchar(256)='D:/BackUp/Portal'
as
declare @today datetime
declare @todayString varchar(50)
declare @bakfilePath varchar(256)
declare @datenameString varchar(50)
set @today=getDate()
set @todayString=convert(varchar(11),@today,120)
select @datenameString= datename(dw,getdate())
-----If today is Sunday then do a full backup
if(@datenameString='Sunday')
begin
set @bakfilePath=@backFolderPath+'/Portal'+@todayString+'Full.bak';
backup database WSS_Content
to disk=@bakfilePath
end
------Else do a increment backup
else
begin
set @bakfilePath=@backFolderPath+'/Portal'+@todayString+'Increment.bak';
backup database WSS_Content
to disk=@bakfilePath
with DIFFERENTIAL
end
GO

2、創(chuàng)建調(diào)用比處理文件

echo Backup database daily, if the day is sunday do a full back up else do a Increment backup
SQLCMD.EXE -S Server/Instance -d DataBaseName -Q "exec dbo.SP_BackUpPortal "

Echo delte the backfile which generated before 30 days

FORFILES /P D:/BackUp/Portal /D -30 /c "cmd /c del @path"

if %date:~0,3%==Sun goto BackByMossCmd
Exit
:BackByMossCmd
echo Backup by the mosscmd
cd C:/Program Files/Common Files/Microsoft Shared/web server extensions/12/BIN
C:
STSADM.EXE -o backup -url http://mossSite/ -filename D:/BackUp/Portal/MossCmdPortalBack%date:~10,4%-%date:~4,2%-%date:~7,2%.bak
Exit

3.在批處理中添加刪除30天前的備份文件腳本

FORFILES /P D:/BackUp/Portal /D -30 /c "cmd /c del @path"

4.新建windows計(jì)劃任務(wù)

不用我說了吧

聲明：本網(wǎng)頁內(nèi)容旨在傳播知識，若有侵權(quán)等問題請及時與本網(wǎng)聯(lián)系，我們將在第一時間刪除處理。TEL:177 7030 7066 E-MAIL:11247931@qq.com