【實用】SQL Server 備份資料庫預存程序指令碼，支援完整、差異備份

SQL Server 備份資料庫預存程序指令碼，支援完整、差異備份

 1 -- ============================================= 2 -- Author     :    樞木 3 -- Create date:    2011-02-15 4 -- Description:    SQL Server 備份資料庫 5 -- ============================================= 6 ALTER procedure usp_sys_BackupDB 7   @DBName varchar(50)=null,      --需要備份的資料庫 8   @BackupType varchar(50)='full' --備份類型 full完整備份 diff差異備份 9 as10 begin11     12     --declare @DBName varchar(50)13     --declare @BackupType varchar(50)14     15     --set @DBName=DB_Name()16     --set @BackupType='full'17     18     declare @FilePath varchar(100) --備份儲存路徑19     declare @FileName varchar(100) --備份檔案全名20 21     set @DBName= isnull(@DBName,DB_Name())22     set @FilePath='D:\DATA_BAK\FULL'23     24     if @BackupType='diff'25     begin26         set @FilePath=replace(@FilePath,'FULL','DIFF')27     end28     29     set @FileName=@FilePath+'\'30                  +@DBName31                  +convert(varchar(100),getdate(),112)32                  +'_'33                  +@BackupType    34 35     -- 全備份36     if @BackupType='full'37     begin38         set @FileName=@FileName39                       +'_'40                       +ltrim(str(datepart(wk, getdate())%2))41                       +str(rand()*10000,5)42                       +'.bak'43         backup database @DBName to disk = @FileName with init,retaindays=644     end45 46     -- 差異備份47     if @BackupType='diff'48     begin49         set @FileName=@FileName50                       +'_'                    51                       +ltrim(str(datepart(dw, getdate())-1))    52                       +str(rand()*10000,5)53                       +'.bak'54         backup database @DBName to disk = @FileName with init,differential,retaindays=655     end    56 end