• Something like this will work:

    A .bat file containing the following:

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

    Then in backup_tlogs.sql you would have:

    DECLARE @name VARCHAR(50) -- database name

    DECLARE @path VARCHAR(256) -- path for backup files

    DECLARE @fileName VARCHAR(256) -- filename for backup

    DECLARE @fileDate VARCHAR(20) -- used for file name

    SET @path = 'D:\mssql\Backup\'

    -- set up backup file suffix

    SELECT @fileDate = datepart(dw,getdate()) -- day of week number 1 = Sun, 5 = Thu etc

    DECLARE db_cursor CURSOR FOR

    SELECT name

    FROM master.sys.databases

    WHERE recovery_model_desc != 'SIMPLE'

    OPEN db_cursor

    FETCH NEXT FROM db_cursor INTO @name

    WHILE @@FETCH_STATUS = 0

    BEGIN

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

    BACKUP LOG @name TO DISK = @fileName WITH INIT

    FETCH NEXT FROM db_cursor INTO @name

    END

    CLOSE db_cursor

    DEALLOCATE db_cursor

    This will keep 7 days worth of transaction logs; overwriting them as the week restarts. If you want to keep it for longer you'd want to play around with the date section of the filename.


    Dird