Solved

Sql backup script show "Incorect sintax near '+' -". Need someone to check code.

Posted on 2013-06-11
3
245 Views
Last Modified: 2013-06-14
Hello,
I'm trying to create daily backup of my local databases. Currently I'm testing below code, but of course not sucessfull.
Well, code is working, but I would like to have daily different file names. I have in mind for bak files to include also a date. So, that each daily file would have name associated to day of backup.
When I have execute below code Sql server show me error: Incorrect sintax near '+'.

USE Test;
GO
BACKUP DATABASE Test1
TO DISK = 'D:\Backups\Test_' + getdate + '.Bak'
   WITH FORMAT,
      MEDIANAME = 'Z_SQLServerBackups',
      NAME = 'Full Backup of Test;
GO

Open in new window


Thanks in advance for help.
0
Comment
Question by:dejandejanovic
3 Comments
 
LVL 13

Assisted Solution

by:jonnidip
jonnidip earned 250 total points
ID: 39237768
Syntax for getdate is: getdate().
Then you need to convert it to a varchar (and choose a format).
For example: select convert(varchar(8), getdate(), 112)
(returns a date in the yyyyMMdd format).

Regards.
0
 
LVL 23

Accepted Solution

by:
nemws1 earned 250 total points
ID: 39239469
Yup - what jonnidip said.  You need the parens *and* you need to cast it to VARCHAR().

Here are a couple sites that show you conversion options:

http://linesofcode.net/snippets/45
http://networking.ringofsaturn.com/SQL/howtoformatdatetime.php
0
 

Author Comment

by:dejandejanovic
ID: 39248712
Thank you for tips, and working result is here:
USE Test;
GO
DECLARE @DateBackup varchar(50)

SELECT @DateBackup = (SELECT 'D:\Test_' + convert(varchar(50),GetDate(),112) + '.bak') 
BACKUP DATABASE Test
TO DISK = @DateBackup 
   WITH FORMAT,
      MEDIANAME = 'Z_SQLServerBackups',
      NAME = 'Full Backup of Test';
GO

Open in new window

0

Featured Post

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

777 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question