Yes.

Save this in notepad also (make sure the "Save as type" is not .txt; same with the .bat).

This .sql should be stored in "d:\mssql\backup\" or if you put it somewhere else you will need to update the locations in the .bat file.

The script will backup the transaction logs of all full/bulk_logged databases in "SERVERNAME\INSTANCENAME" specified in the .bat file (you will need to change this to the name of the server & instance).

If you have multiple instances will different databases on the same server then you will need to make further changes. I guess if you duplicate the line from the .bat file it may work that way e.g. if you have 2 instances on server SVR01 that are named INST01 and INST02 then your .bat file would look something like this:

sqlcmd -S SVR01\INST01 -E -d master -e -b -i d:\mssql\backup\backup_tlogs.sql -o d:\mssql\backup\backup_tlogs.log

sqlcmd -S SVR01\INST02 -E -d master -e -b -i d:\mssql\backup\backup_tlogs.sql -o d:\mssql\backup\backup_tlogs.log

If you wanted to do this then you would (at least) need to alter 1 line of the .sql file so that databases with the same name from different instances didn't overwrite each other. To do this change:

SET @fileName = @path + @name + '_' + @fileDate + '.BAK'

To:

SET @fileName = @path + @@servicename + '_' + @name + '_' + @fileDate + '.BAK'

To keep things cleaner you could store the backups in separate folders (per instance) but then you would need one .sql file per instance so I think just adding the instance name is easier.

Edit: Actually, how often do you plan to run this backup? The code I provided only works if the transaction log backup is once per day actually 😡

I'm guessing you plan to backup the transaction logs more regularly?

How big are the databases? How often do you backup the databases?