SQL Server 备份数据库sql脚本 本地

-- =============================================
-- Author     : 枢木
-- Create date: 2011-02-15
-- Description: SQL Server 备份数据库sql脚本 本地
-- =============================================
create procedure usp_sys_BackupDB
  @DBName varchar(50)=null,      --需要备份的数据库
   @BackupType varchar(50)='full' --备份类型 full完整备份 diff差异备份
as
begin
 
 declare @DBName varchar(50)
 declare @BackupType varchar(50)
 
 set @DBName=DB_Name()
 set @BackupType='full'
 
 declare @FilePath varchar(100) --备份保存路径
 declare @FileName varchar(100) --备份文件全名

 set @DBName= isnull(@DBName,DB_Name())
    set @FilePath='D:\DBBAK\FULL'
   
    if @BackupType='diff'
    begin
  set @FilePath=replace(@FilePath,'FULL','DIFF')
    end
   
 set @FileName=@FilePath+'\'
     +@DBName
     +convert(varchar(100),getdate(),112)
     +'_'
     +@BackupType 

 -- 全备份
 if @BackupType='full'
 begin
  set @FileName=@FileName
                      +'_'
                      +ltrim(str(datepart(wk, getdate())%2))
                      +'.bak'
        backup database @DBName to disk = @FileName with init,retaindays=6
 end

 -- 差异备份
 if @BackupType='diff'
 begin
  set @FileName=@FileName
                      +'_'     
                      +ltrim(str(datepart(dw, getdate())-1)) 
                      +'.bak'
  backup database @DBName to disk = @FileName with init,differential,retaindays=6
 end 
end