I am using the sql script below to create a transaction log backup file for my database but the log is appending to the same .bak file that will create a huge file eventually. How can I separate each transaction log backup to a separate file with time stamp? It is very easy in the Full version of SQL2005 but I am using the SQL 2005 Express this time.
BACKUP LOG [mycompany] TO DISK = N'F:\LifeDB-Backup\DB_Log.bak' WITH NOFORMAT, NOINIT, NAME = N'Transaction Log Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10
declare @backupSetId as int
select @backupSetId = position from msdb..backupset where database_name=N'mycompany' and backup_set_id=(select max(backup_set_id) from msdb..backupset where database_name=N'mycompany' )
if @backupSetId is null begin raiserror(N'Verify failed. Backup information for database ''mycompany'' not found.', 16, 1) end
RESTORE VERIFYONLY FROM DISK = N'F:\LifeDB-Backup\DB_Log.bak' WITH FILE = @backupSetId, NOUNLOAD, NOREWIND