Hi
The query executes without any errors, below is the log file:
create procedure sap_backup_databases
@dbList nvarchar(500),
@r3Db nvarchar(128),
@bDev nvarchar(2000) = N'R3DUMP0',
@expDays int = 27,
@jobName nvarchar(128) = N'SAP CCMS Job',
@bkupChecksum nchar(1) = N'N',
@bkupType nchar(1) = N'F',
@nativeBkup nchar(1) = N'N',
@exeDate nvarchar(14) = N'',
@bkupSim nchar(1) = N'N',
@format int = 0,
@init int = 0,
@bkupDb nvarchar(128) = N'',
@unload tinyint = 0,
@exeStmt nvarchar(2000) output
AS
BEGIN
/*********************************************************************/
/** Object:Stored Procedure sap_backup_databases Date: 12/29/98 ******/
/* */
/* */
/* 03/28/00 - modified temp table #spintab to add the */
/* columns flag,BindingId, RecoveryForkID, collation as */
/* the RESTORE HEADERONLY Returns more columns in shiloh -RR */
/* */
/* 05/01/00 - modified sp to check for version and create */
/* temp tables accordingly -RR */
/* 11/14/01 - Rewritten the stored procedure to add @dblist */
/* parameter and removed all the condition check for */
/* r3 db, master, msdb. Now the databases can be */
/* combined as one parameter -RR */
/* 04/25/02 - Replace restore headeronly with Restore Label */
/* Only as restore headeronly takes long time */
/* for high capacity device like LTO devices */
/* The mediafamily id in the result set can be */
/* used to compare media_family_id in the table */
/* msdb..backupmediafamily and col media_set_id */
/* is used to join tables backupmediafamily, */
/* backupmediaset,backupset to get the position */
/* in the table backupset. */
/* 01/07/03 - changed the select stmt to get the pos */
/* by replacing 'max' with 'TOP 1' and adding */
/* sort order by last_finish_date - RR */
/* 09/14/04 - Changed @dbcommand variable length to 1000 */
/* 06/21/07 - Getdate function is used in backup name(from @exedate)*/
/*********************************************************************/
declare @day varchar(2), /* todays day of the month */
@length tinyint, /* the length of the day (1 or 2) */
@command nvarchar(4000),/* complete command for one DB BACKUP */
@secondPos tinyint, /* position of the second backup device,
in case of a parallel BACKUP */
@deviceCommand nvarchar(3000),/* complete device option in the BACKUP
command */
@backupDesc nvarchar(255), /* Description option in the backup
command */
@devices nvarchar(2000),/* List of devices */
@fmt_int nvarchar(255) ,/* FORMAT and INIT options */
@cmd1 nvarchar(1000),/* RESTORE command */
@cmd2 nvarchar(500), /* RESTORE command */
@backupsetposition int, /* backup set position in tape */
@backupsetposition2 nvarchar(10), /* backupset position in tape */
@dbCommand nvarchar(1000) /* beginning of the BACKUP command */
declare @multiple_db tinyint, /* flag to check multiple db backups */
@multiple_devs tinyint, /* flag to check multiple devices */
@nextdb_pos tinyint, /* Next database poistion in @dblist */
@firstDb sysname,/* First db in the @dblist */
@backupName nvarchar(128),/* Name option in the BACKUP command */
@fst_medname_char nchar(1),/* First char in media name */
@snd_medname_char nchar(1),/* Second char in media name */
@r3db_pos tinyint, /* R3 database pos in database list */
@med_family_id uniqueidentifier,
@ErrorSave int,
@withcmd nvarchar(200),
@dateStr nvarchar(20),
@medianame nvarchar(200),
@verifyCmd nvarchar(500),
@checkSum nvarchar(20),
@userdbPos int,
@jobexeDt datetime,
@jobexeDtC nvarchar(17),
@exeDt nvarchar(21)
set nocount on
If @nativeBkup = N'N'
Begin
if exists ( select * from msdb..sysobjects
where name = 'sap_backup_user_exit'
and type = 'P')
begin
print 'Backup Executed by stored procedure sap_backup_user_exit'
if @bkupSim = N'N'
exec msdb..sap_backup_user_exit
@dbList,
@r3Db,
@bDev,
@expDays,
@jobName,
@bkupChecksum,
@bkupType,
@exeDate,
@format,
@init,
@unload,
@bkupDb
else
select @exeStmt = 'Third party backup procedure will be used'
return
end
END
/*initilization */
SELECT @multiple_db = 0
SELECT @multiple_devs = 1
SELECT @backupsetposition = 1
SELECT @deviceCommand = N''
SELECT @devices = N''
set @ErrorSave = 0
if user_name() = N'dbo'
dbcc traceon( 3605)
/* set the checksum string */
if @bkupChecksum = N'Y'
select @checkSum = N'CHECKSUM,'
else
select @checkSum = N''
select @jobexeDt = convert(datetime,getdate(),112)
select @day = convert(nvarchar(2),DATENAME( day, @jobexeDt))
select @length = DATALENGTH( @day)
/* find out if more than one backup devices are given */
SELECT @secondPos = CHARINDEX( N',',@bDev)
/* if so, step through a while loop and parse the single devices*/
WHILE @secondPos <> 0
BEGIN
SELECT @multiple_devs = @multiple_devs + 1
break
END
/* @devices used in the Restore VERIFY ONLY */
SELECT @devices = @bDev
/* check the database list */
SELECT @nextdb_pos = CHARINDEX( N',',@dbList)
/*set the multiple_db */
IF @nextdb_pos <> 0
select @multiple_db = 1
/* construct the backup command */
if @bkupType = N'F'
Begin
SELECT @dbCommand = N'BACKUP DATABASE '+ @bkupDb +N' TO '
SELECT @backupName =N',NAME = '+ N''''+N'Sap backup on ' +
convert(nvarchar,getDate(),112) +
+N' ' +convert(nvarchar,getDate(),108) +N''''
SELECT @withcmd =N' WITH ' + @checkSum + N'DESCRIPTION = '''
+@jobName+ N''''
select @snd_medname_char = N'D'
end
else if @bkupType = N'D'
begin
SELECT @dbCommand = N'BACKUP DATABASE '+ @bkupDb +N' TO '
SELECT @backupName =N',NAME = '+ N''''+
N'Sap Differential backup on '+
convert(nvarchar,getDate(),112) +
+N' ' +convert(nvarchar,getDate(),108) +N''''
SELECT @withcmd = N' WITH ' + @checkSum + N'DESCRIPTION = '''
+ @jobName + N''',DIFFERENTIAL'
select @snd_medname_char = N'+'
end
else
begin
SELECT @dbCommand =N'BACKUP Log '+ @bkupDb +N' TO '
SELECT @backupName = N',name = '+ N''''+
N'Sap log backup on ' +
convert(nvarchar,getDate(),112) +
+N' ' +convert(nvarchar,getDate(),108) +N''''
SELECT @withcmd = N' WITH ' + @checkSum + N'DESCRIPTION = '''
+@jobName+ N''''
select @snd_medname_char = N'L'
end
/* construct the @fmt_int */
IF @format = 1
BEGIN
SELECT @fmt_int = N',FORMAT,MEDIADESCRIPTION = ''' +@jobName
+N''''
END
ELSE
BEGIN
IF @init = 1
SELECT @fmt_int = N',INIT'
ELSE
SELECT @fmt_int = N',NOINIT'
END
/************************************************************/
/* New naming convention */
/*Single database Ist character: S: for master Backup */
/* M: for msdb Backup */
/* R: for R/3 Backup */
/* O: other databases */
/Multiple Databases Ist character C: for combination(R3 DB/
/* Not included) */
/* I: for combination(R3 DB*/
/* included) */
/* 2nd charater L: for trans log backup */
/* 😧 for database backup */
/* (not supported now) F: for file backup */
/* (not supported now) G: for filegroup backup */
/* +: for diff. backup */
/* 3rd and 4th characters day in the month */
/* 5th character P: for a parallel backup*/
/* S: for a seq. backup */
/* */
/************************************************************/
/* check the first character in media name */
if @bkupDb = N'master' or @bkupDb = N'msdb'
or @bkupDb = @r3Db
BEGIN
IF @multiple_db = 0
SELECT @fst_medname_char =
CASE @bkupDb
WHEN N'master' THEN N'S'
WHEN N'msdb' THEN N'M'
WHEN @r3Db THEN N'R'
ELSE N'O'
END
ELSE
BEGIN
SELECT @r3db_pos = CHARINDEX( @r3Db,@dbList)
if @r3db_pos = 0
SELECT @fst_medname_char= N'C'
ELSE
SELECT @fst_medname_char= N'I'
END
END
else
select @fst_medname_char = N'U'
/*construct the medianame */
IF @length = 1
BEGIN
if @multiple_devs = 1
SELECT @medianame = N',MEDIANAME='''+@fst_medname_char
+ @snd_medname_char+N'0' + @day + N'S' + N''''
else
SELECT @medianame = N',MEDIANAME=N'''+@fst_medname_char
+ @snd_medname_char +N'0' + @day + N'P' +N''''
END
ELSE
BEGIN
if @multiple_devs = 1
SELECT @medianame = N',MEDIANAME='''+@fst_medname_char
+ @snd_medname_char+ @day + N'S' + N''''
else
SELECT @medianame = N',MEDIANAME=N'''+@fst_medname_char
+ @snd_medname_char+ @day +N'P' +N''''
END
IF @init = 1 OR @format = 1
SELECT @deviceCommand = @bDev + @withcmd
+N',RETAINDAYS=' + CONVERT( varchar, @expDays)
+ @fmt_int + @medianame
else
SELECT @deviceCommand = @bDev + @withcmd
+N',RETAINDAYS=' + CONVERT( varchar, @expDays)
+ @fmt_int
if @unload = 1
select @command = @dbCommand + @deviceCommand
+ @backupName +N',UNLOAD'
else
select @command = @dbCommand + @deviceCommand
+ @backupName +N',NOUNLOAD'
if @bkupSim =N'N'
EXECUTE( @command ) else
select @exeStmt = @command
END
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.