This is what I useDECLARE @strFileName varchar(4000)SELECT @strFileName = 'M:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\' + db_name() + '_' + CONVERT(varchar(8), GetDate(), 112) -- yyyymmdd + '_' + REPLACE(LEFT(CONVERT(varchar(8), GetDate(), 108), 5), ':', '') -- hh:mm:ss + '_' + 'Tran' -- Type - TLog, Full, Diff etc. + '.BAK' BACKUP LOG [PUBN] TO DISK = @strFileName WITH NAME = N'PUBN-Transaction Log Backup', STATS = 10
Don't think you need any other options. You could change NAME to use an @Parameter too, and include the Date/Time alongside the description."... so everyday I get ..."You ought to be taking a TLog backup much more frequently than once a day - every 15 minutes would be a good interval ...