1、创建备份数据库的存储过程 -- ============================================= -- create basic stored procedure template -- ============================================= -- drop stored procedure if it already exists if exists ( select * from
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计划任务
不用我说了吧