cancel
Showing results for 
Search instead for 
Did you mean: 

DB13 SQL Backup Expiration Period doesn't work successfully

ORU
Advisor
Advisor
0 Kudos

Hi together

I changed and tested all parameters in the TR DB13 to create a backup to a network drive but it doesn't work successfully. The Expiration Period for Backup Volumes doesn't work. I set this parameter to 5 days for example. The log backup overwrite the backup from the day before and so on and so on. He ignores the the expiration period any idea why. How can we correct this?

Here is my actually Job and preview information:

 

***************************** SQL Job information ******************************

Jobname: SAP CCMS Log Backup of SM2 [20130717173627-5-230000]

Type: TSQL

DB-Name: SM2

For Run: 20130726 23:00:00

**************************** Job history information *****************************

Stepname: CCMS-step 1

Command: declare @exeStmt nvarchar(2000) exec sm2.sap_backup_databases @dbList=

"SM2",@r3Db="SM2",@bDev="R3DUMP4",@expDays= 5,@jobName= "SAP CCMS Log

Backup of SM2 [20130717173627-5-230000]",@bkupChecksum="N",@bkupCompre

ss="Y",@bkupType="L",@nativeBkup="N",@exeDate = "20130717230000",@bkup

Sim = "N",@format = 1,@init = 1,@bkupDb = "SM2",@unload = 0,@exeStmt =

@exeStmt OUTPUT

Status: (success)

Message: 3014

Severity: 0

Duration: 0 hours(s) 9 min(s) 54 sec(s)

Last msg: Executed as user: sm2. Processed 1180769 pages for database 'SM2', fil

e 'SM2LOG1' on file 1. [SQLSTATE 01000] (Message 4035) BACKUP LOG suc

cessfully processed 1180769 pages in 593.193 seconds (15.551 MB/sec).

[SQLSTATE 01000] (Message 3014). The step succeeded.

<------------- End of Job Step History --------------->

Stepname: CCMS-step 2

Command: declare @exeStmt nvarchar(2000) exec sm2.sap_verify_backups @nativeBku

p = "N",@bkupSim = "N",@bDev = "R3DUMP4",@bkupChecksum = "N",@exeDate

= "20130717230000",@format_init = 1,@unload = 1,@dbCnt =1,@exeStmt = @

exeStmt OUTPUT

Status: (success)

Message: 3262

Severity: 0

Duration: 0 hours(s) 10 min(s) 48 sec(s)

Last msg: Executed as user: sm2. The backup set on file 1 is valid. [SQLSTATE 01

000] (Message 3262). The step succeeded.

<------------- End of Job Step History --------------->

 

Many thanks for helping.

regards Oliver

**************************** Job history information *****************************

and Preview information:

 

exec msdb..sp_add_job @originating_server = '(local)',@job_name = "SAP CCMS Log Backup of SM2

[20130717173627-5-230000]",@enabled = 1,@description = "SAP CCMS JOB",@start_step_id = 1,

@category_name = NULL,@category_id = NULL

exec msdb..sp_add_jobstep @job_id = null,@job_name = 'SAP CCMS Log Backup of SM2

[20130717173627-5-230000]',@step_id = 1,@step_name = 'CCMS-step 1',@subsystem = 'TSQL',

@command = 'declare @exeStmt nvarchar(2000) exec sm2.sap_backup_databases @dbList="SM2",

@r3Db="SM2",@bDev="R3DUMP4",@expDays= 5,@jobName= "SAP CCMS Log Backup of SM2

[20130717173627-5-230000]",@bkupChecksum="N",@bkupCompress="Y",@bkupType="L",@nativeBkup="N",

@exeDate = "20130726230000",@bkupSim = "Y",@format = 1,@init = 1,@bkupDb = "SM2",@unload = 0,

@exeStmt = @exeStmt OUTPUT',@database_name = 'SM2',@on_success_action = 3

BACKUP Log SM2 TO R3DUMP4 WITH

DESCRIPTION = 'SAP CCMS Log Backup of SM2 [20130717173627-5-230000]',RETAINDAYS=5,

FORMAT,

COMPRESSION,

MEDIADESCRIPTION = 'SAP CCMS Log Backup of SM2 [20130717173627-5-230000]',

MEDIANAME='RL29S',name = 'Sap log backup on 20130729 20:34:30',NOUNLOAD

exec msdb..sp_add_jobstep @job_id = null,@job_name = 'SAP CCMS Log Backup of SM2

[20130717173627-5-230000]',@step_id = 2,@step_name = 'CCMS-step 2',@subsystem = 'TSQL',

@command = 'declare @exeStmt nvarchar(2000) exec sm2.sap_verify_backups @nativeBkup = "N",

@bkupSim = "Y",@bDev = "R3DUMP4",@bkupChecksum = "N",@exeDate = "20130726230000",@format_init

= 1,@unload = 1,@dbCnt =1,@exeStmt = @exeStmt OUTPUT',@database_name = 'SM2'

RESTORE VERIFYONLY FROM R3DUMP4 WITH FILE = X ,UNLOAD

exec msdb..sp_add_jobschedule @job_id = null,@job_name = 'SAP CCMS Log Backup of SM2

[20130717173627-5-230000]',@name = 'CCMS-sche1',@enabled = 1,@freq_type =8,@freq_interval = 32,

@freq_subday_type = 1,@freq_subday_interval = 0,@active_start_date = 20130717,

@active_start_time = 230000,@freq_recurrence_factor = 1

exec msdb..sp_add_jobserver @job_name = 'SAP CCMS Log Backup of SM2

[20130717173627-5-230000]',@server_name = '(local)'

Accepted Solutions (1)

Accepted Solutions (1)

Former Member
0 Kudos

Below is what RetainDays/ expiry does for you. 

It will not let you overwrite the backup until the retaindays is past. (and that too if you do not use "format" option) It will just throw you an error if you just use init option to reinitialize the backup file. With format option in the backup command it will not throw a warning and remove the old file.

If you don't use format and don't use init it will let you append the backup to the same backupset without overwriting the file.

If you do want to implement what you are trying to do here I think you need to write a script  to implement what you want to do. Or you can do a maintenance plan with a workflow of "backup task" and then a "maintenance cleanup task"

Please see the illustration below about the behavior of retaindays, init and format command. I am on sql server 2012

--new backup. note the file number in the second last line.

BACKUP DATABASE [master] TO  DISK = N'C:\test.bak' WITH init, RETAINDAYS = 1, init, REWIND, NOUNLOAD,  STATS = 10

go


--try to overwrite before retention is due. throws an error

BACKUP DATABASE [master] TO  DISK = N'C:\test.bak' WITH init, RETAINDAYS = 1, init, REWIND, NOUNLOAD,  STATS = 10

go


--remove the init and you will see the backup written to file 2 in the same  file. note the file number in second last line again

BACKUP DATABASE [master] TO  DISK = N'C:\test.bak' WITH  RETAINDAYS = 1, REWIND, NOUNLOAD,  STATS = 10

go

--add the format and you are back to file 1 in the second last line.

BACKUP DATABASE [master] TO  DISK = N'C:\test.bak' WITH  init, format, RETAINDAYS = 1, REWIND, NOUNLOAD,  STATS = 10

go

warm regards,

sudhir

ORU
Advisor
Advisor
0 Kudos

Hi sudhir

many thanks. Yes you are right, if I don't use the format and the init option the backup and log files was appended. But if he retaindays are exceeded the file won't be overwriten.

Any idea why?

regards Oliver

cherry_tian
Explorer
0 Kudos

Hi, Oliver,

If you want to overwrite the backup devide, you need to use the INIT option. Please see the description about the INIT option in MSDN.

*********************************************************************************************************

{ NOINIT | INIT }
Controls whether the backup operation appends to or overwrites the existing backup sets on the backup media. The default is to append to the most recent backup set on the media (NOINIT).
NOINIT
Indicates that the backup set is appended to the specified media set, preserving existing backup sets. If a media password is defined for the media set, the password must be supplied. NOINIT is the default.

INIT

Specifies that all backup sets should be overwritten, but preserves the media header. If INIT is specified, any existing backup set on that device is overwritten, if conditions permit. By default, BACKUP checks for the following conditions and does not overwrite the backup media if either condition exists:

      --Any backup set has not yet expired. For more information, see  the EXPIREDATE  and   RETAINDAYS options.

     --The backup set name given in the BACKUP statement, if provided, does not match the name on the backup media. For more information, see the NAME option, earlier in this section.

*********************************************************************************************************

DB13 uses the stored procedure sap_backup_database to execute the backup command. The default is INIT. But please check SAP KBA 1867938 . There is an error happening if you use only one media to do the backup on DB13 with init option and expiration date.

Best regards.

Cherry

ORU
Advisor
Advisor
0 Kudos

Hi Cherry

many thanks for helping. Note 1867938 helps me great.

regards Oliver

Former Member
0 Kudos

Retaindays confuses by its name. It does not help in cleaning up old backups. It will just let you overwrite an existing file with an init option. But init option will "reinitialize" your expired file and you won't be able to append anything to it. you will just overwrite it.

The only thing you get with retaindays is the warning message when you try to overwrite an unexpired file with init option in backup command.

http://sqlblog.com/blogs/tibor_karaszi/archive/2012/07/08/backup-and-the-evil-retaindays-option.aspx

See if the above blog helps you understand retaindays, init and format option.

Hope this help

warm regards,

sudhir

Message was edited by: sudhir dubey

Answers (1)

Answers (1)

former_member184473
Active Contributor
0 Kudos

Hello Oliver,

Do you observe the same behavior with the log is backed up into a local folder instead of network?

Regards,

Eduardo Rezende

ORU
Advisor
Advisor
0 Kudos

Hi Eduardo

yes we do the same for the log file. The path is on the same network place.

regards Oliver