Backup Script Summary

1. Database full backup and differential backup stored procedures:

Use [Xxxdb]
GO

SET ANSI_NULLS on
GO

SET QUOTED_IDENTIFIER ON
GO
--Backpath Backup path
--Deldate days to save backups
--dbname database name for backup

CREATE proc [dbo]. [Dba_backupfulldb]
@deldate varchar (50)
, @bakpath varchar (50)
, @dbname varchar (50)
As
DECLARE @sqltxtdel varchar (max)
, @createpath varchar (max)
, @backupdb varchar (max)
, @ddate varchar (8)
, @dweek varchar (10)
, @weekday int
, @filename varchar (500)
, @sqltxtcopy varchar (max)
Set @ddate = Convert (char (8), GETDATE (), 112)
Set @dweek = DatePart (week,getdate ())
Set @weekday = DatePart (weekday,getdate ())
Set @createpath = ' exec ' (' xp_create_subdir ' [email protected]+ ' \ ' [email protected]+ ') '
Print @createpath
EXEC (@createpath)
if (@weekday = 1)
Begin
--set @deldate = '-9 ' days to save backups
Set @sqltxtdel = ' xp_cmdshell ' forfiles/p ' [email protected]+ '/d ' [email protected]+ '/s/m *.full/c ' cmd/c del @file ' ‘‘‘
Print @sqltxtdel
EXEC (@sqltxtdel)
--Full backup
Set @backupdb = '
Backup DATABASE [' [email protected]+ '] to disk = ' [email protected]+ ' \ ' [email protected]+ ' \ ' [email protected]+ ' _ ' [ Email protected]+ ' _ ' [email protected]+ '. Full '
With CHECKSUM, COMPRESSION
‘
Print @backupdb
EXEC (@backupdb)
End
Else
Begin
--set @deldate = '-8 '
Set @sqltxtdel = ' xp_cmdshell ' forfiles/p ' [email protected]+ '/d ' [email protected]+ '/s/m *.diff/c ' cmd/c del @file ' ‘‘‘
Print @sqltxtdel
EXEC (@sqltxtdel)
--Differential backup
Set @backupdb = '
Backup DATABASE [' [email protected]+ '] to disk = ' [email protected]+ ' \ ' [email protected]+ ' \ ' [email protected]+ ' _ ' [ Email protected]+ ' _ ' [email protected]+ '. diff '
With Checksum,compression,differential
‘
--print @backupdb
EXEC (@backupdb)
End

GO

The above stored procedures can be used in conjunction with the following job scripts to implement daily backups:

Declare @delday int,        @bakpath varchar( -),        @dbname varchar( -)Set @delday='-16'Set @bakpath='F:\DBbackup‘

--Backpath Backup path
--Deldate days to save backups
--dbname database name for backup

DeclareMyCursorcursor  forSelect db_name(b.database_id) fromSys.dm_hadr_availability_replica_states aJoinSys.dm_hadr_database_replica_states b ona.group_id=b.group_id anda.replica_id=b.replica_idwhereRole=1OpenMyCursorFetch Next  fromMyCursor into @dbname--gets the name of the database that needs to be backed up (the database in always on) while @ @FETCH_STATUS=0begin Print @dbnameexecDba_backupfulldb@delday,@bakpath,@dbnameFetch Next  fromMyCursor into @dbnameEndCloseMyCursordeallocateMyCursor

2. Available stored procedures for log backups:

 Use [Xxdb]GOSETAnsi_nulls onGOSETQuoted_identifier onGOCREATE proc [dbo].[Dba_backuplog]        @deldate varchar( -)        ,@bakpath varchar( -)        ,@dbname varchar( -) as        Declare @sqltxtdel varchar(Max)        ,@createpath varchar(Max)        ,@backupLog varchar(Max)        ,@sn nvarchar( -)--Set @bakpath backup path--set @deldate log retention daysSet @createpath='Sp_msforeachdb"'Xp_create_subdir" '"+@bakpath+'\'+@dbname+'\ "" "Print @createpathexec(@createpath)Set @sqltxtdel ='xp_cmdshell"'forfiles/p'+@bakpath+'\'+@dbname+'/ D'+@deldate+'/s/m *.trn/c "cmd/c del @file"" "Print @sqltxtdelexec(@sqltxtdel)--log backup startedSet @sn = Replace(Replace(Replace(Convert(varchar,getdate(), -),'-',"'),' ',"'),':',"')Set @backupLog='backup Log ['+@dbname+'] to disk =" "+@bakpath+'\'+@dbname+'\'+@dbname+'_no'+@sn+'. TRN"'With compression'Print @backuplogexec(@backuplog)GO

The above stored procedures can be called in a job, and a backup plan can be implemented as needed to implement a backup strategy in SQL Server Agent jobs.

Backup Script Summary