Login ProductsSalesSupportDownloadsAbout |
Home » Technical Support » ElevateDB Technical Support » Support Forums » ElevateDB General » View Thread |
Messages 1 to 5 of 5 total |
Appending CURRENT_TIMESTAMP to Backup File Name |
Sat, May 6 2023 6:41 PM | Permanent Link |
Richard Harding Wise Nutrition Coaching | 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 |
Sun, May 7 2023 2:20 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | 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 |
Tue, May 9 2023 7:41 AM | Permanent Link |
Rijk Van Der Merwe | 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 |
Tue, May 9 2023 8:49 PM | Permanent Link |
Richard Harding Wise Nutrition Coaching | 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 |
Tue, May 9 2023 8:54 PM | Permanent Link |
Richard Harding Wise Nutrition Coaching | 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 |
This web page was last updated on Saturday, May 4, 2024 at 12:54 AM | Privacy PolicySite Map © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? E-mail us at info@elevatesoft.com |