Solved

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

Posted on 2013-06-11
3
250 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

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
T-SQL 10 35
SSIS On fail action 5 38
Add a step to a system backup job 6 18
First Max value 3 28
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Via a live example, show how to shrink a transaction log file down to a reasonable size.

856 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