1. Home
  2. Point-Of-Sale Software
  3. DB Backup with date & timestamp in the backup name

DB Backup with date & timestamp in the backup name

I currently use the following .cmd file to backup my RMS database. Once done I send the backup file to my backoffice file server.

:: Creates the newest backup on ONE (main server) from SQL query OSQL -U sa -P password -S servername -Q "BACKUP DATABASE [main] TO DISK 'c:\temp\rmsbackup.bck' WITH FORMAT, STATS"

However, I would like to take several backups during the day and would like to add a date and time stamp to the backup file name. I tried the following .cmd file but get some syntax errors with the + sign.

:: Creates the newest backup on ONE (main server) from SQL query OSQL -U sa -P password -S servername -Q "BACKUP DATABASE [main] TO DISK 'c:\temp\rmsbackup_' + str(year(getdate())) + str(month(getdate())) + str(day(getdate())) + str(datepart(hh,getdate())) + str(mi(getdate())) + str(datepart(ss,getdate())) + '.bck' WITH FORMAT, STATS"

I get the following errors:

Msg 170, Level 15, State 1, Server T42, Line 1 Line 1: Incorrect syntax near '+'.

I don't see anything wrong with the syntax but obviously there is.

Any help would be greatly appreciated.

Reply to
Gerd
Loading thread data ...

Gerd,

Antonio sent this solution to you last year. I haven't tried it, but it looks right. Does it not work?

"Gerd" ha scritto nel messaggio news: snipped-for-privacy@tk2msftngp13.phx.gbl...

DECLARE @fullpath nvarchar(255)

set @fullpath = 'c:\Program Files\Microsoft Retail Management System\Backup\Backup-' + str(year(getdate())) + str(month(getdate())) + str(day(getdate())) + '.bck'

BACKUP DATABASE main TO DISK = @fullpath WITH FORMAT, STATS

sorry for my error antonio

Reply to
Jeff

I remember Antonio sending the samples over. I was trying for a while but could not get them to work. However, I want to get this going so I picked it up again for additional testing.

When executing the .cmd file I get those syntax errors with the + sign.

Reply to
Gerd

Perhaps a little crude, but definitely effective:

----- REM RMS Auto-Backup REM Use Task Scheduler to run this program REM Creates a rolling 3-day archive

CD C:\RMS Backup

REM Check to see if other backups exist REM Rename existing backup files and delete the oldest

IF EXIST DailyBackup3.bck DEL DailyBackup3.bck IF EXIST DailyBackup2.bck REN DailyBackup2.bck DailyBackup3.bck IF EXIST DailyBackup1.bck REN DailyBackup1.bck DailyBackup2.bck

IF EXIST DailyHQBackup3.bck DEL DailyHQBackup3.bck IF EXIST DailyHQBackup2.bck REN DailyHQBackup2.bck DailyHQBackup3.bck IF EXIST DailyHQBackup1.bck REN DailyHQBackup1.bck DailyHQBackup2.bck

REM Create a new backup from SQL Query

OSQL -U sa -P password -S SERVERNAME -Q "BACKUP DATABASE SODatabase TO DISK = 'C:\RMS Backup\DailyBackup1.bck' WITH FORMAT, STATS" OSQL -U sa -P password -S SERVERNAME -Q "BACKUP DATABASE HQDatabase TO DISK = 'C:\RMS Backup\DailyHQBackup1.bck' WITH FORMAT, STATS"

-----

You could easily change 1, 2, 3 to Open123, Noon123 and Close123, save three separate batch files and schedule three separate tasks with Task Scheduler.

If 123 doesn't work, go with 1-X where X is the number of days in your work week.

You get the idea. Create as fancy a naming structure as you need and schedule the jobs as necessary. Simple.

FWIW, I got this code (most of it, at least) from this group.

HTH, Tom

Reply to
Terrible Tom

BeanSmart website is not affiliated with any of the manufacturers or service providers discussed here. All logos and trade names are the property of their respective owners.