SQL SERVER数据库自动备份并删除30天前的备份文件

作者：admin 发布于：2013-3-20 8:32 Wednesday 分类：MSSQL

1、创建备份数据库的存储过程

 -- =============================================
-- 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、创建调用比处理文件

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计划任务

不用我说了吧

标签: 备份