Hello All,
I have a Job based on the example in the manual.
The backup is done every hour.

ALTER /* CREATE */ JOB Backup
RUN AS "System"
FROM DATE '2015-01-01' TO DATE '2015-04-30' /* YYYY-MM-DD */
EVERY 1 HOURS
ON MON, TUE, WED, THU, FRI, SAT, SUN
BETWEEN TIME '00:01 AM' AND TIME '11:59 PM'
CATEGORY 'Backup'
BEGIN
   DECLARE DBCursor CURSOR FOR DBStmt;
   DECLARE DBName VARCHAR DEFAULT '';

   PREPARE DBStmt FROM 'SELECT * FROM Databases';

   OPEN DBCursor;

   FETCH FIRST FROM DBCursor ('Name') INTO DBName;

   WHILE NOT EOF(DBCursor) DO
      IF (DBName <> 'Configuration') THEN
         EXECUTE IMMEDIATE 'BACKUP DATABASE "' + DBName + '" AS "' +
                           DBName + '-Backup-' + CAST(CURRENT_DATE AS
VARCHAR(10)) + '-' +
                           /* CAST(CURRENT_TIME AS VARCHAR(10)) + */
                           '" TO STORE "Backups" INCLUDE CATALOG';
      END IF;
      FETCH NEXT FROM DBCursor ('Name') INTO DBName;
   END WHILE;

   CLOSE DBCursor;
END

This gives a database name like:-

  MyDatabase-Backup-2015-04-28

As I am running every 1 hour then the files overwrite each other.

Can I output a unique number on the end?

Can I output the hour and minute?
   To look like  19-06 for 6 minutes past 7 PM.

Perhaps there is a way to output a set number of backups before they get
overwritten. Like a father, grandfather set of backups?

Regards,
  Peter Evans

Peter


Don't think there's anything for creating a block of backups but for the time format you could use

REPLACE(':','-',CAST(CURRENT_TIME AS VARCHAR(5)))

Roy Lambert

Peter Evans wrote:

>                            /* CAST(CURRENT_TIME AS VARCHAR(10)) + */

I know little of these things, but that commented out section appears
to do pretty much what you want?

--

Matthew Jones

Hi Peter

On 28/04/2015 10:01 p.m., Peter Evans wrote:
> Hello All,
> I have a Job based on the example in the manual.
> The backup is done every hour.
>

Thought that I'd chuck in my version that seems to work well for me.
This JOB is generated in code:-

  DECLARE StoreName VARCHAR;
  DECLARE FileName VARCHAR;

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

  EXECUTE IMMEDIATE 'BACKUP DATABASE "DB0000" AS "' + FileName + '"
    TO STORE "' + StoreName + '" INCLUDE CATALOG ';

This results in filenames like this:-

DB0000-2015-03-06_1102.EDBBkp

Cheers

Jeff

On 28/04/2015 8:01 PM, Peter Evans wrote:
> Hello All,
> I have a Job based on the example in the manual.
> The backup is done every hour.

I will work on a solution and get back to you.

Peter

On 29/04/2015 8:40 AM, Peter Evans wrote:

> I will work on a solution and get back to you.


I thought the idea of having a FileName was good as it separated that
routine and made the Execute Immediate clearer.

The following runs every 2 minutes so is only for testing.

The output names look like

       MyDatabase-Backup-2015-04-29_0933.EDBBkp




ALTER /* CREATE */ JOB Backup
RUN AS "System"
FROM DATE '2015-01-01' TO DATE '2015-05-31' /* YYYY-MM-DD */
/* DAILY
   BETWEEN TIME '11:00 PM' AND TIME '11:30 PM' */
/* EVERY 1 HOURS */
EVERY 2 MINUTES
ON MON, TUE, WED, THU, FRI, SAT, SUN
BETWEEN TIME '00:01 AM' AND TIME '11:59 PM'
CATEGORY 'Backup'
BEGIN
   DECLARE DBCursor CURSOR FOR DBStmt;
   DECLARE DBName VARCHAR DEFAULT '';
   DECLARE FileName VARCHAR;

   PREPARE DBStmt FROM 'SELECT * FROM Databases';

   OPEN DBCursor;

   FETCH FIRST FROM DBCursor ('Name') INTO DBName;

   WHILE NOT EOF(DBCursor) DO
      IF (DBName <> 'Configuration') THEN

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

         EXECUTE IMMEDIATE 'BACKUP DATABASE "' + DBName + '" AS "' +
                           FileName +
                           '" TO STORE "Backups" INCLUDE CATALOG';
      END IF;
      FETCH NEXT FROM DBCursor ('Name') INTO DBName;
   END WHILE;

   CLOSE DBCursor;
END

Regards,
  Peter Evans