[Practical] SQL Server backs up database Stored Procedure scripts and supports complete and differential backup

SQL Server backs up database Stored Procedure scripts and supports complete and differential backup

 1   --  ========================================================== =====  2   --  Author: Shu mu  3   --  Create Date: 2011-02-15  4   --  Description: SQL Server backup database  5   -- ========================================================== =====  6   Alter   Procedure  Usp_sys_backupdb  7     @ Dbname   Varchar ( 50 ) =  Null , --  Database to be backed up  8     @ Backuptype  Varchar ( 50 ) =  '  Full  '   --  Full full backup diff differential backup  9   As  10   Begin  11       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 )--  Backup storage path  19       Declare   @ Filename   Varchar ( 100 ) --  Full name of the backup file  20   21       Set   @ Dbname  =   Isnull ( @ Dbname ,Db_name  ())  22       Set   @ Filepath  =  '  D: \ data_bak \ full  '  23       24       If   @ Backuptype  =  '  Diff  '  25      Begin  26           Set   @ Filepath  =  Replace ( @ Filepath , '  Full  ' , '  Diff  '  )  27       End  28      29       Set   @ Filename  =  @ Filepath  +  '  \  '  30                    +  @ Dbname  31                    +  Convert ( Varchar ( 100 ),Getdate (), 112  )  32                    +  '  _  '  33                    +  @ Backuptype      34   35       --  Full backup  36       If  @ Backuptype  =  '  Full  '  37       Begin  38           Set   @ Filename  =  @ Filename  39                         +  '  _  '  40                        +  Ltrim ( Str ( Datepart (Wk, Getdate ()) %  2  ))  41                         +  Str ( Rand () *  10000 , 5 )  42                         +  '  . Bak  '  43           Backup   Database   @ Dbname   To   Disk   =   @ Filename   With Init, retaindays =  6 44       End  45   46       --  Differential backup  47       If   @ Backuptype  =  '  Diff  '  48       Begin  49           Set   @ Filename =  @ Filename  50                         +  '  _  '                      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 =  6  55       End      56   End