Greetings

For years, a JOB creates a database backup file at 2200 each night which is then emailed to the company accountant and myself.

The backup file has the timestamp appended to the backup filename, example below.

* ES_StockDB_2018-02-24-2200.EDBBkp

The backup drive run out of space recently and the client added an additional backup disk drive. The backup job somehow was deleted.

This should be easy to recreate the backup JOB, I said to myself.

I have tried replacing the line that creates the backup file with the name "ES_StockDB" with one that appends the CURRENT_TIMESTAMP. One example is shown in the script below.

I have tried lots of different ways to try to append the CURRENT_TIMESTAMP to the backup file in the following format.
* ES_StockDB_YYYY-MM-DD-HHMM.EDBBkp

There have been forum posts relating to the issue but I have not been able to solve the problem.

I would really appreciate it if someone could offer suggestions.

I think there is a documentation error for BACKUP DATABASE which is defined as:

BACKUP DATABASE <Name>
 AS <BackupName>
 TO STORE <StoreName>
 [INCLUDE CATALOG [ONLY]]

The three strings are shown as <A String>.
However, to execute the command the first string does not have quotes and the next two do.

BACKUP DATABASE es_StockDB AS "ES_StockDB" TO STORE "Desktop" INCLUDE CATALOG

Richard.

= = = = = = = = = =

SCRIPT
BEGIN
DECLARE strFormattedDate VARCHAR(255) COLLATE ANSI;

DECLARE strDatabaseName VARCHAR(255) COLLATE ANSI;
DECLARE strBackupName VARCHAR(255) COLLATE ANSI;
DECLARE strTIMESTAMP VARCHAR(255) COLLATE ANSI;
DECLARE strBackNameWithDate VARCHAR (255) COLLATE ANSI;
DECLARE strDesktop VARCHAR(255) COLLATE ANSI;

DECLARE S VARCHAR(255) COLLATE ANSI;

SET strDatabaseName = 'es_StockDB';
SET strBackupName = 'ES_StockDB';
SET strDesktop = 'Desktop';

-- How do you CONCAT BackupName with the Current Timestamp?
SET strTIMESTAMP = CAST(CURRENT_TIMESTAMP() AS VARCHAR(24)
    DATE FORMAT 'YYYY-MM-DD-' TIME FORMAT 'HHMM');

-- Error at second comma
-- An error was found in the line below at line 19 and column 53 (Expected ) but instead found ,)
SET strBackNameWithDate = CONCAT(strBackupName, '_', strTIMESTAMP);

SET S = 'BACKUP DATABASE ' +
      strDatabaseName +
      ' AS ' +

--     QUOTEDSTR(strBackupName USING '"') +         that is a single quote, double quote and a single quote
      QUOTEDSTR(strBackNameWithDate USING '"') +

      ' TO STORE ' +
      QUOTEDSTR(strDesktop USING '"') +
      ' INCLUDE CATALOG';

SET LOG MESSAGE TO S;
EXECUTE IMMEDIATE S;

END

Richard


My OLH shows

CONCAT(<StringExpression> WITH <StringExpression>)
CONCAT(<StringExpression>, <StringExpression>)

so your second , should not be there. Why not just use good old +

Roy Lambert

Richard

Here is my code doing something similar -

BEGIN
 DECLARE StoreName VARCHAR;
 DECLARE FileName VARCHAR;

 SET StoreName = 'SM_Production';
 SET FileName = 'SM_Production' + '-' + REPLACE(' ' WITH '_' IN
 REPLACE(':' WITH '' IN CAST(CURRENT_TIMESTAMP AS VARCHAR(17))));

 EXECUTE IMMEDIATE ('BACKUP DATABASE "SM_Production" AS "' + FileName + '"
   TO STORE "' + StoreName + '" INCLUDE CATALOG ');
END

Rijk

Thank you very much Rijk

<< Here is my code doing something similar>>

It all seems very simple. I think that having dashes as the date separator instead of the underscore was causing a problem.

I have posted my complicated solution which is not nearly as simple as yours.

I do not know what changed at the client because for many years the accountant and myself were being emailed the backup file with dash separators.

Richard

Roy, Thank you for the effort that you have taken to respond.

On Sunday, I was rushing to get a response back to you before the start of the new week which brings a lot of activity to contend with.

My response was not very helpful.


However, I have made much progress as the script below shows.

I set the variable strBackupNameWithDate to a number of different expressions.

SET strBackupNameWithDate = 'X';
-- Example 1: Creates a valid backup file 'X.EDBBkp'

SET strBackupNameWithDate = (QUOTEDSTR(strBackupName USING '"'));
-- Example 2: Creates a valid backup file 'ES_StockDB.EDBBkp'

SET strBackupNameWithDate = strBackupName;
-- Example 3: Creates a valid backup file 'ES_StockDB.EDBBkp'

SET strBackupNameWithDate = strBackupName || strUnderscore || '2023_05_09_1745';
-- Example 4: Creates a valid backup file 'ES_StockDB_2023_05_09_1745.EDBBkp'

SET strBackupNameWithDate = strBackupName || strUnderscore || '2023-05-09-1745';
-- Example 5: Backup file not created, Underscores work, dashes do not
--

SET strBackupNameWithDate = strBackupName || strUnderscore ||
    CAST(CURRENT_TIMESTAMP() AS VARCHAR(24) DATE FORMAT 'YYYY_MM_DD_' TIME FORMAT 'hhmm');
-- Example 6:
-- NOT WORKING, even though the DATE FORMAT has the separator of Underscores
-- ElevateDB Error #700 An error was found in the statement at line 56 and column 20 (Expected TO but instead found 2154)
-- Current Time is (was) 2154.


SET LOG MESSAGE TO <astring> is not very helpful as a diagnostic tool.
It does not provide useful information in the session log messages and it is an inconvenience to access.
Whilst the log message tab in the EDManager does show sometimes the Log Message tab, it appears to be overwritten by EDManager messages.

Below is my latest script. Hopefully it makes more sense than the last.

SCRIPT
BEGIN
DECLARE strFormattedDate VARCHAR(255) COLLATE ANSI;
DECLARE strDash VARCHAR(1) COLLATE ANSI;
DECLARE strUnderscore VARCHAR(1) COLLATE ANSI;

DECLARE strDatabaseName VARCHAR(255) COLLATE ANSI;
DECLARE strBackupName VARCHAR(255) COLLATE ANSI;
DECLARE strBackupNameWithDate VARCHAR (255) COLLATE ANSI;
DECLARE strDesktop VARCHAR(255) COLLATE ANSI;

DECLARE S VARCHAR(255) COLLATE ANSI;

SET strDash = '-';
SET strUnderscore = '_';

SET strDatabaseName = 'es_StockDB';
SET strBackupName = 'ES_StockDB';
SET strDesktop = 'Desktop';

-- Below are a selection of constants set to strBackupNameWithDate

SET strBackupNameWithDate = 'X';
-- Example 1: Creates a valid backup file 'X.EDBBkp'

SET strBackupNameWithDate = (QUOTEDSTR(strBackupName USING '"'));
-- Example 2: Creates a valid backup file 'ES_StockDB.EDBBkp'

SET strBackupNameWithDate = strBackupName;
-- Example 3: Creates a valid backup file 'ES_StockDB.EDBBkp'

SET strBackupNameWithDate = strBackupName || strUnderscore || '2023_05_09_1745';
-- Example 4: Creates a valid backup file 'ES_StockDB_2023_05_09_1745.EDBBkp'

SET strBackupNameWithDate = strBackupName || strUnderscore || '2023-05-09-1745';
-- Example 5: Backup file not created, Underscores work, dashes do not
--

SET strBackupNameWithDate = strBackupName || strUnderscore ||
    CAST(CURRENT_TIMESTAMP() AS VARCHAR(24) DATE FORMAT 'YYYY_MM_DD_' TIME FORMAT 'hhmm');
-- Example 6:
-- NOT WORKING, even though the DATE FORMAT has the separator of Underscores
-- ElevateDB Error #700 An error was found in the statement at line 56 and column 20 (Expected TO but instead found 2154)
-- Current Time is 2154.

-- Example 4 produces a valid backup file with underscore separators where as example 5 does not produce a backup file with dash separators.
-- I do not know why this would be the case.

-- Example 6 seems to indicate that there is a problem with DATE FORMAT and TIME FORMAT.
---------------------------------------------------------------

SET LOG MESSAGE TO strBackupNameWithDate;

SET S = 'BACKUP DATABASE ' +
      strDatabaseName +
      ' AS ' +
  
      strBackupNameWithDate +

     ' TO STORE ' +
     QUOTEDSTR(strDesktop USING '"') +
     ' INCLUDE CATALOG';

EXECUTE IMMEDIATE S;
END