Solved

SQL syntax

Posted on 2008-10-01
4
255 Views
Last Modified: 2010-03-20
This is a Script I would like to Run at Month END to create a special Backup with the Month and year in the file name.


USE [700];

GO

BACKUP DATABASE [700]

TO DISK = 'E:\DB\' & DATENAME(month, getdate()-5) & DATENAME(year, getdate()-5) &'.Bak'

   WITH FORMAT,

      MEDIANAME = 'E_SQLServerBackups',

      NAME = DATENAME(month, getdate()-5) &' End of Month';

GO

Open in new window

0
Comment
Question by:kwitcom
4 Comments
 
LVL 32

Accepted Solution

by:
Daniel Wilson earned 250 total points
Comment Utility
Change your & to + in your string concatenation.  

Otherwise ... I don't see any problem.
0
 
LVL 11

Assisted Solution

by:aaronakin
aaronakin earned 250 total points
Comment Utility
Try this...

USE [700];
GO
DECLARE
      @FileName            VARCHAR(500),
      @BackupName            VARCHAR(500)

SELECT @FileName = 'E:\DB\'+ CAST(YEAR(GETDATE()) AS VARCHAR(4)) +'_'+ RIGHT('0'+CAST(MONTH(GETDATE()) AS VARCHAR(2)),2) +'.bak'
SELECT @BackupName = RIGHT('0'+CAST(MONTH(GETDATE()) AS VARCHAR(2)),2) +'/'+ CAST(YEAR(GETDATE()) AS VARCHAR(4)) +' End of Month'

BACKUP DATABASE [700]
TO DISK = @FileName
   WITH FORMAT,
      MEDIANAME = 'E_SQLServerBackups',
      NAME = @BackupName;
GO
0

Featured Post

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…
This video demonstrates how to create an example email signature rule for a department in a company using CodeTwo Exchange Rules. The signature will be inserted beneath users' latest emails in conversations and will be displayed in users' Sent Items…

762 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

Need Help in Real-Time?

Connect with top rated Experts

7 Experts available now in Live!

Get 1:1 Help Now