create variable name???

• Staggerlee

  SSCertifiable

  Points: 5272

  More actions

  May 27, 2011 at 8:40 am

  #238266

  Hi all,

  I would like to do full backups with the name being being DB+date.bak

  ie:

  MyDatabase25052011.bak

  How do you do this when you are writing it in TSQL?

  Also if i want the file to delete will putting RETAINDAYS = 7 delete the file after 7 days? or does this only count if do backups to the same file and want to retain 7 days of them?

  Hope someone can help

  Thanks

  s

• Lowell

  SSC Guru

  Points: 323518

  More actions

  May 27, 2011 at 8:52 am

  #1331048

  something like this?

  --SandBox_2011-05-27_10-51-42.bak

  SELECT db_name()

  +'_'

  + REPLACE(

  REPLACE(

  CONVERT(VARCHAR(35),getdate(),120),

  ' ','_'), --fix any spaces

  ':','-') --fix the colons in the time

  + '.bak'

  Lowell

  ---

  --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

• GSquared

  SSC Guru

  Points: 260824

  More actions

  May 27, 2011 at 8:56 am

  #1331050

  I'd recommend using an ANSI/ISO standard date format for it, so the files sort naturally. So it would be 20110527, not 27052011.

  You can do that like this:

  declare @String varchar(50);

  set @String = 'MyDBName' + convert(varchar(50), getdate(), 112);

  select @String;

  That's easy enough to build into a backup command.

  The retention days don't automatically delete the backup. That requires an O/S command of some sort. Can be done through the command shell, through .NET objects, et al. All the usual ways you would delete a file. All ExpireDate/RetainDays does is tell SQL Server it's okay to overwrite a backup file after a certain point. Not applicable if the files will have different names (which is the case when you have a date on the end of the filename). More applicable if you append backups to an existing file/tape.

  - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
  Property of The Thread

  "Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

• John Mitchell-245523

  SSC Guru

  Points: 148809

  More actions

  May 27, 2011 at 8:57 am

  #1331051

  stebennettsjb (5/27/2011)

  ---

  Also if i want the file to delete will putting RETAINDAYS = 7 delete the file after 7 days? or does this only count if do backups to the same file and want to retain 7 days of them?

  No, this won't cause the file to be deleted. The easiest way of getting rid of files after a certain number of days is to create a maintenance plan.

  John

• Staggerlee

  SSCertifiable

  Points: 5272

  More actions

  May 27, 2011 at 9:31 am

  #1331073

  thanks for all the quick replies!

  I shall have a play!