Should transaction log backups' file extension be .TRN or .BAK

  • Or can you explain the difference? I use .BAK for db backups and .TRN for trans. backups.

    Also -does anyone see issues with this script? I will be testing a point of failure restore and want to be certain that my trans logs are being backup up properly.

    USE myDB

    GO

    -- Declares a string

    declare @sql nvarchar(255)

    -- Declares the filename.

    declare @bkdbName varchar(50)

    set @bkdbName = 'myDB_LOG_BKUP_'

    + Convert(varchar(50),getdate(),110)

    + '_' + convert(varchar(2),DATEPART(HH,getdate())) + convert(varchar(2),DATEPART(MI,getdate()))

  • You can use whatever extension you want for backup (full, diff, log) files. Use .doc if you want to, SQL Server won't care.

    The extension has no meaning, except making it easier to visually see the difference between them.

    Personally I use bak for full backups, diff for differential and trn for log backups.

  • The file extensions are just there for your convenience, since you probably won't be double-clicking a backup file to try to "open" it. Use whatever you're comfortable with.

    The script is just for generating a file name, right? If so, it looks okay to me.

    - 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

  • Thanks for the clarification. I did not paste the actual log command of my script.

    -- Sets the backup running by executing the @sql string.

    set @sql = 'BACKUP LOG myDB TO DISK = '''+@bkName+'.TRN'''

    print @sql

    exec sp_executesql @sql

  • Extension is only for your refference there is nothing to do with SQL server. The only thing is you need to specify the name with extension (which you have given to the file at the time of taking backup like .BAK or .TRN) at the time of restoring the database.

    So here your query is also correct.

    -MJ
    Please do not print mails and docx unless it is absolutely necessary. Spread environmental awareness.

  • As with all of the previous posts it is all about making it simple and easy for you to identify the type of backup.

    I have previously worked in a site that used a convention such as DBNAME_DB_YYYYMMDDHHMMSS.dmp - full backups

    DBNAME_TRN_YYYYMMDDHHMMSS.dmp - Tlog backups

    Have a look at your own companies naming standards or if there are not any a good opportunity to create some. ?

    MCT
    MCITP Database Admin 2008
    MCITP Database Admin 2008
    MCITP Database Dev 2008
    www.jnrit.com.au/Blog.aspx

  • I don't see any issue with your script. It looks fine. However, I have two suggestions:

    1) Change USE myDB to USE Master.

    2) You can use Maintenance Plan to take Log backup, if you don't have any specific reason to use customized script.

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

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