how to add today name for Sybase ASE DB backup filename

[Yasin]

Hi,

am using DBACOCKPIT SQL script to schedule daily backup for our Sybase database, i want to keep 7 files as retention so i need the backup file name to contain today day name for example

PRD_Backup_Saturday.dump
PRD_Backup_Sunday.dump
PRD_Backup_Monday.dump

.....

command should be something like this:

dump database DMR to "D:\SAP_DB_BACKUP\PRD\PRD_Backup_" + getdate(day) + ".dump" with compression=101

how can i achieve that.

Thanks

[Mark_A_Parsons · Accepted solution]

I'm guessing when you run your command you're receiving an error similar to:

Msg 102, Level 15, State 181:
Server 'ASE400', Line 1:
Incorrect syntax near '+'.

The dump database command doesn't support on-the-fly creation of dump device names like you're attempting. What you can do is use a variable to build the device name and then reference the variable in the dump database command; adding datename() to generate the 'day of week' (eg, Monday), one idea:

declare @dumpdev varchar(1000)
select  @dumpdev="D:\SAP_DB_BACKUP\PRD\PRD_Backup_" + datename(dw,getdate()) + ".dump"
dump database DMR to @dumpdev with compression=101
go

NOTE: I don't work with DBACOCKPIT so I'm assuming it can support a SQL batch like this; another option would be to install a custom stored proc that does this same thing (assuming DBACOCKPIT will allow you to call a stored proc when defining your backup operation).