Tail backup time stamp

• gary1

  SSC-Insane

  Points: 21152

  More actions

  November 11, 2009 at 9:45 pm

  #227886

  Hi,

  I'm taking the Tail backup using the below script and the .trn file is creating with Mydb_tail.trn. But I want the date-time stamp for .trn file. Could you please tell me what change I need to do or Is there any good script to take tail backp?

  BACKUP LOG [Mydb] TO DISK = N'C:\Taillog_backup\Mydb_Tail.trn'

  WITH NO_TRUNCATE , NOFORMAT, NOINIT, NAME = N'Mydb-Transaction Log Backup',

  SKIP, NOREWIND, NOUNLOAD, NORECOVERY , STATS = 10

  GO

  declare @backupSetId as int

  select @backupSetId = position from msdb..backupset where database_name=N'Mydb'

  and backup_set_id=(select max(backup_set_id) from msdb..backupset where database_name=N'Mydb' )

  if @backupSetId is null begin raiserror(N'Verify failed. Backup information for database ''Mydb'' not found.', 16, 1) end

  RESTORE VERIFYONLY FROM DISK = N'C:\Taillog_backup\Mydb_Tail.trn'

  WITH FILE = @backupSetId, NOUNLOAD, NOREWIND

  GO

  thanks

• Jack Corbett

  SSC Guru

  Points: 184394

  More actions

  November 12, 2009 at 6:38 am

  #1078312

  I'm not sure what you are asking. Do you want to find the time you took the tail log backup or do you want to add a timestamp to the name of the tail log backup?

  Jack Corbett
  Consultant - Straight Path Solutions
  Check out these links on how to get faster and more accurate answers:
  Forum Etiquette: How to post data/code on a forum to get the best help
  Need an Answer? Actually, No ... You Need a Question

• gary1

  SSC-Insane

  Points: 21152

  More actions

  November 12, 2009 at 1:14 pm

  #1078572

  thanks,

  I'm not sure what you are asking. Do you want to find the time you took the tail log backup or do you want to add a timestamp to the name of the tail log backup?

  I want to add a timestamp to the name of the tail log backup so that I know at what time the tail backup is done.

  thanks so much

• Jack Corbett

  SSC Guru

  Points: 184394

  More actions

  November 12, 2009 at 1:47 pm

  #1078591

  You need to do something like:

  DECLARE @path NVARCHAR(MAX),

  @filename NVARCHAR(MAX);

  SET @path = N'C:\Taillog_backup\'

  Set @filename = N'Mydb_Tail_' + REPLACE(REPLACE(REPLACE((CONVERT(nvarchar, GETDATE(), 120)), '-', ''), ' ', '_'), ':', '') + N'.trn'

  SET @path = @path + @filename

  BACKUP LOG [Mydb] TO DISK = @path

  WITH NO_TRUNCATE , NOFORMAT, NOINIT, NAME = N'Mydb-Transaction Log Backup',

  SKIP, NOREWIND, NOUNLOAD, NORECOVERY , STATS = 10

  Jack Corbett
  Consultant - Straight Path Solutions
  Check out these links on how to get faster and more accurate answers:
  Forum Etiquette: How to post data/code on a forum to get the best help
  Need an Answer? Actually, No ... You Need a Question