log file backups

Question

log file backups

do-208479

SSC Enthusiast

Points: 182
More actions
January 21, 2009 at 5:11 am

#199046

Hi
Can anyone tell me how to create individual log file backups i.e. I want individual hourly .trn backups on disk and do not want appended log backups in the same .trn file. I would like to retain the .trn files on disk for a period of 7 days.
I have done this using a maintenance plan but would like to know how to do it by way of a sql agent job.
Thanks
Ding

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

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

turrasque SSC Enthusiast Points: 146 More actions · Answer 1

You can use the BACKUP LOG command, you just need to find a way to give the file a unique name. The first thing that comes to mind is a simple timestamp of some kind. Below is an example for you to work with. It's a little messy but you get the idea.

declare @path varchar(50)

declare @timestamp varchar(20)

set @timestamp =

convert(varchar(4), datepart(YY,GETDATE())) +

convert(varchar(3), datepart(dy,GETDATE())) +

convert(varchar(3), datepart(hh,GETDATE())) +

convert(varchar(3), datepart(n,GETDATE())) +

convert(varchar(3), datepart(ss,GETDATE()))

set @path = 'c:\test_' + @timestamp + '.trn'

backup log test to disk = @path

do-208479 SSC Enthusiast Points: 182 More actions · Answer 2

do-208479

SSC Enthusiast

Points: 182

January 22, 2009 at 3:37 am

#930962

Thanks very much. That works a treat!

Adi Cohn-120898 SSC-Dedicated Points: 33944 More actions · Answer 3

Not that I think that turrasque’s way is wrong, but it is an opportunity to write about a little known feature that is called agent tokens. This feature can be used to define a value in a job’s step that will be replaced each time that the step will run. You can find more details about it at http://msdn.microsoft.com/en-us/library/ms175575(SQL.90).aspx. Since you are using the agent in order to create the backup file, you can use the agent tokens. For example take a look at this code:

Backup log MyDB to disk =

'c:\MyDB$(ESCAPE_SQUOTE(STRTDT))_$(ESCAPE_SQUOTE(STRTTM)).trn'

This will create a file that is called MyDB_YYYYMMDD_HHMMSS.trn where instead of YYMMDD it will have the date and instead of HHMMSS it will have the exact hour that the job ran (for example if the job ran today (Jan 22) at 9:30 in the morning the file name will be MyDB20080122_093000). If you’ll try to use it in ad-hoc query or stored procedure it will fail. It has to be used in a job’s step.

Adi

--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

turrasque SSC Enthusiast Points: 146 More actions · Answer 4

I knew there had to be a more efficient way, that's very neat Adi!

do-208479 SSC Enthusiast Points: 182 More actions · Answer 5

Thanks. I'll give that a go. Also, is there any way that another step could be added into the job to delete any of the log file backups that are > 7 days old

Ding

ps. One Orange Chip Points: 29255 More actions · Answer 6

Adi Cohn (1/22/2009)
Backup log MyDB to disk =
'c:\MyDB$(ESCAPE_SQUOTE(STRTDT))_$(ESCAPE_SQUOTE(STRTTM)).trn'
This will create a file that is called MyDB_YYYYMMDD_HHMMSS.trn where instead of YYMMDD it will have the date and instead of HHMMSS it will have the exact hour that the job ran (for example if the job ran today (Jan 22) at 9:30 in the morning the file name will be MyDB20080122_093000). If you’ll try to use it in ad-hoc query or stored procedure it will fail. It has to be used in a job’s step.

Cool Stuff Adi. Thanks for sharing in the forum :).

Pradeep Singh

Adi Cohn-120898 SSC-Dedicated Points: 33944 More actions · Answer 7

do (1/22/2009)
Thanks. I'll give that a go. Also, is there any way that another step could be added into the job to delete any of the log file backups that are > 7 days old
Ding

You can create a temporary table and then insert the files’ names into the table using xp_cmdshell that activates dir command with the parameter b. This parameter uses bare format. This means that you’ll get only the files’ names without any other information. Since part of the name is the date and time that the file was created, you can use string functions such as substring to get the information about the file’s creation date. According to this information you can decide which file should be deleted.

Adi

--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/