how to get current date in .bak file name

Question

how to get current date in .bak file name

Denise McMillan

SSCertifiable

Points: 6216
More actions
September 19, 2008 at 3:23 pm

#212898

I'm trying to get a job to make a simple backup of a database with current date so that it doesn't overwrite or append to previous bak files. Like:
BACKUP DATABASE CE10 to disk = 'd:\mssql\BACKUP\ce10_db_yyyyMMddhhmm.BAK'
How do I get the current date in there each day?
Thanks very much for any help.

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Alvin Ramard SSC-Forever Points: 41190 More actions · Answer 1

Are you doing the backup from a SQL script? If you are then you could use dynamic SQL, convert getdate() to a varchar and append that to the static part of the file name.

Alvin Ramard
Memphis PASS Chapter[/url]

All my SSC forum answers come with a money back guarantee. If you didn't like the answer then I'll gladly refund what you paid for it.

For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]

Denise McMillan SSCertifiable Points: 6216 More actions · Answer 2

Yes I'm using a SQL script. Maybe I just can't get the right syntax:

BACKUP DATABASE CE10 to disk =

'c:\mssql\BACKUP\ce10_db_convert(getdate()).BAK' WITH NOINIT

How do I append/convert correctly? I'll try looking up some examples. Thanks very much.

Alvin Ramard SSC-Forever Points: 41190 More actions · Answer 3

Assuming that "_db_convert" was supposed to be part of the file name, try replacing the getdate() with ' + convert(varchar(8), getdate(), 112)) + '

Note, include both single quotes in the replacement.

Alvin Ramard
Memphis PASS Chapter[/url]

All my SSC forum answers come with a money back guarantee. If you didn't like the answer then I'll gladly refund what you paid for it.

For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]

Denise McMillan SSCertifiable Points: 6216 More actions · Answer 4

Yes it's part of the file name. I'm still struggling with the syntax. Can you see what's wrong with this:

BACKUP DATABASE CE10 to disk = 'c:\mssql\backup\ce10_db_ '+ convert(varchar(8), getdate(), 112) +'.BAK' WITH NOINIT

Thanks very much for all ideas.

Gordon-265412 SSCrazy Points: 2898 More actions · Answer 5

Denise McMillan (9/22/2008)
Yes it's part of the file name. I'm still struggling with the syntax. Can you see what's wrong with this:
BACKUP DATABASE CE10 to disk = 'c:\mssql\backup\ce10_db_ '+ convert(varchar(8), getdate(), 112) +'.BAK' WITH NOINIT
Thanks very much for all ideas.

You cant pass a string in the manner you're trying to for the DISK statement...

DECLARE @DISKSTRING varchar(255)

SET @DISKSTRING = 'C:\mssql\backup\ce10_db_' + convert(char(8),getdate(),112) + '.BAK'

BACKUP DATABASE CE10

TO DISK = @DISKSTRING

WITH NOINIT

Your friendly High-Tech Janitor... 🙂

Denise McMillan SSCertifiable Points: 6216 More actions · Answer 6

I have it working now.

Thanks very much for all the help!