on 07-29-2013 7:36 PM
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)'
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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.
*********************************************************************************************************
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
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
Hello Oliver,
Do you observe the same behavior with the log is backed up into a local folder instead of network?
Regards,
Eduardo Rezende
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
84 | |
23 | |
11 | |
9 | |
8 | |
5 | |
5 | |
5 | |
5 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.