DB Backup with date & timestamp in the backup name

Posted by Gerd on March 15, 2006, 9:16 pm

Please log in for more thread options

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:temprmsbackup.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:temprmsbackup_' + 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.

Posted by Jeff on March 15, 2006, 9:40 pm

Please log in for more thread options

Gerd,

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

> Antonio,

> I just tried to run the following db_backup.cmd but get an error on the +

> sign. Here is the exact syntax of the command with the error message:

> C:Program FilesMicrosoft Retail Management System>OSQL -U UserName -P

> password -S TCS50

> -Q "BACKUP DATABASE main TO DISK = 'c:Program FilesMicrosoft Retail

> Management SystemBackupBackup-' + str(year(getdate())) +

> str(month(getdate())) + str(day(getdate())) '.bck' WITH FORMAT, STATS"

> Msg 170, Level 15, State 1, Server TCS50, Line 1

> Line 1: Incorrect syntax near '+'.

DECLARE @fullpath nvarchar(255)

set @fullpath = 'c:Program FilesMicrosoft Retail
Management SystemBackupBackup-' + str(year(getdate())) +
str(month(getdate())) + str(day(getdate())) + '.bck'

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

sorry for my error
antonio

--
*
Get Secure! - www.microsoft.com/security

You must be using Outlook Express or some other type of newsgroup reader to
see and download the file attachment. If you are not using a reader, follow
the link below to setup Outlook Express. Click on "Open with newsreader"
under the MS Retail Management System on the right.

http://tinyurl.com/75bgz

**********

"Gerd" <gerddotgoegelatbavariandashcons.com> wrote in message
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:temprmsbackup.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:temprmsbackup_' + 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.

Posted by Gerd on March 16, 2006, 9:24 am

Please log in for more thread options

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.

> Gerd,

> Antonio sent this solution to you last year. I haven't tried it, but it

> looks right. Does it not work?

>> Antonio,

>> I just tried to run the following db_backup.cmd but get an error on the +

>> sign. Here is the exact syntax of the command with the error message:

>> C:Program FilesMicrosoft Retail Management System>OSQL -U UserName -P

>> password -S TCS50

>> -Q "BACKUP DATABASE main TO DISK = 'c:Program FilesMicrosoft Retail

>> Management SystemBackupBackup-' + str(year(getdate())) +

>> str(month(getdate())) + str(day(getdate())) '.bck' WITH FORMAT, STATS"

>> Msg 170, Level 15, State 1, Server TCS50, Line 1

>> Line 1: Incorrect syntax near '+'.

> DECLARE @fullpath nvarchar(255)

> set @fullpath = 'c:Program FilesMicrosoft Retail

> Management SystemBackupBackup-' + str(year(getdate())) +

> str(month(getdate())) + str(day(getdate())) + '.bck'

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

> sorry for my error

> antonio

> --

> *

> Get Secure! - www.microsoft.com/security

> You must be using Outlook Express or some other type of newsgroup reader

> to

> see and download the file attachment. If you are not using a reader,

> follow

> the link below to setup Outlook Express. Click on "Open with newsreader"

> under the MS Retail Management System on the right.

> http://tinyurl.com/75bgz

> **********

> "Gerd" <gerddotgoegelatbavariandashcons.com> wrote in message

> 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:temprmsbackup.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:temprmsbackup_' + 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.

Posted by Terrible Tom on March 16, 2006, 8:55 pm

Please log in for more thread options

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 BackupDailyBackup1.bck' WITH FORMAT, STATS"
OSQL -U sa -P password -S SERVERNAME -Q "BACKUP DATABASE HQDatabase TO DISK
= 'C:RMS BackupDailyHQBackup1.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
--
The worst words in business:
"We''ve always done it that way"
--
Stop Fishing for eMail.

"Gerd" wrote:

> 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:temprmsbackup.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:temprmsbackup_' + 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.