Transaction Log Backup Scheduling Query - DB13

[former_member201800]

Hi Basis Folks,

I want to schedule every hour Transaction Log Backups through DB13 where i want to get separate logs files for each hour to ensure point to point recovery.

option's that i am getting to schedule is Tape unload, Init, Format etc, so whichever options i choose it either appends or over rites the first Log file which it created. So through DB13 I am unable to generate multiple T-Log files for every hour.

OS - Windows

DB - SQL 2008

Please let me know whether its possible to generate multiple T-Log files on the same device through DB13, or we need to schedule through SQL Studio only.

Regards

Shyam Kumar

[former_member193518]

You can create multiple backup devices in SSMS pointing to different log backup file names.

Create one backup device for each hour (Tlog_backup_1000, Tlog_backup_1100.......)

Then in DB13, schedule the log backup and specify the specific backup device that you created in SSMS.

[former_member201800]

Hi Darryl,

I need to schedule T-log backups for 30 mins, so it would be difficult to schedule via above suggested way as i would end up creating 48 devices in total.

Please let me know if there are other possibilities or else do i need to get it configured via SQL Studio.

Regards

Shyam Kumar

[former_member193518]

That's not difficult.

EXEC master.dbo.sp_addumpdevice  @devtype = N'disk', @logicalname = N'R3_FullBackup_1', @physicalname = N'I:\SAPBACKUP\R3_FullBackup_1.bak'

GO

[former_member193518]

and...

EXEC master.dbo.sp_addumpdevice  @devtype = N'disk', @logicalname = N'TranLog_SAT_1200', @physicalname = N'I:\SAPBACKUP\TranLog_SAT_1200.bak'

GO

[former_member183107]

Hi Shyam,

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.

Inyour case i would suggest go with the option of NOINIT

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

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.

Hope it helps.

Regards,

Himanshu

[former_member201800]

Hi Himanshu,

Thnx for quick reply.. we have tried this bt no luck..

Regards,

Shyam