backup job adding timestamp to generated file

• Facundo Barrera

  Grasshopper

  Points: 13

  More actions

  March 12, 2007 at 9:28 am

  #369801

  Hi:

  i need to add timestamp to automatic generated backup files, these are full database backups, using jobs from management SQL server agent.

  The idea is to archive this backups that i set to be maiden once a day, and identified them from the timestamp (date).

  Thanks a lot

  FB

• MohammedU

  SSCoach

  Points: 19644

  More actions

  March 12, 2007 at 4:22 pm

  #694529

  Declare @Dt_DateAndTime Varchar(30), @dbname sysname, @file sysname

  select @Dt_DateAndTime = convert(char(8), getdate(), 112)+''+ Replace(convert(char(12), getdate(), 14),':','')

  select @dbname = 'master'

  select @file = 'c:\'+@dbname+'_'+@Dt_DateAndTime

  select @file

  backup database @dbname to disk =  @file

  with init

  MohammedU
  Microsoft SQL Server MVP