tags:

views:

139

answers:

3

I'm using the below to backup a db from a SQL job. Can someone tell me how to add the current date to the output filename? Preferably in YYYYMMDD format.

BACKUP DATABASE [myDB] TO  DISK = N'\\myPath\myDB.bak' WITH NOFORMAT, INIT,  NAME = N'myDB', SKIP, REWIND, NOUNLOAD,  STATS = 10
GO

Thanks!

+2  A: 
DECLARE @MyFileName varchar(50)

SELECT @MyFileName = (SELECT '\\ServerToSave\Path\MyDB_' + convert(varchar(50),GetDate(),112) + '.bak') 

BACKUP DATABASE [myDB] TO DISK=@MyFileName ...

I love how all the other posters stole my code ?

Just convert it I just tested and it worked...

JonH
code does not work
Raj More
@Raj - of course it does you simply need to convert the date to work with varchar....Thanks for stealing my code in your post though...
JonH
@JonH, urh your code works now you've edited it. Thanks though for editing my answer and oh thanks for removing the point I'd been awarded.
CResults
@CResults - As you see with the edit is the comment I made "Just convert it...". Why wouldn't I edit on a forum where people need answers? And I didnt take any awards from you.
JonH
+1 - If an answer is off by say one simple function call I would expect the person answering TO EDIT IT, it helps others.
+1  A: 

Try this.

DECLARE @MyFileName varchar(50)
SELECT '\\ServerToSave\Path\MyDB_' + convert(nvarchar(20),GetDate(),112) + '.bak'
BACKUP DATABASE [myDB] TO DISK=@MyFileName ...

The 112 in the Convert gives you the YYYYMMDD format

CResults
@CResults - I negated you for taking credit where it was due and then downvoting original answer.
@jmh86. Maybe you should review. Whatever, life is too short. At the end of the day I was looking to get the OP an answer that works. Next time I'll add comments.
CResults
A: 

Use the following

DECLARE @BackupFileName varchar(20)

SELECT @BackupFileName = '\\ServerName\SharedFolder\DatabaseName_' + CONVERT (VarChar, GetDate(), 112) + '.bak'

BACKUP DATABASE [myDB] TO  DISK = @BackupFileName WITH NOFORMAT, INIT,  NAME = N'myDB', SKIP, REWIND, NOUNLOAD,  STATS = 10

Read up on Cast and Convert here http://msdn.microsoft.com/en-us/library/ms187928.aspx

Raj More