How to backup transaction log after database backup everyday in SQL server 2005

Question

How to backup transaction log after database backup everyday in SQL server 2005

Answer 1

Why not just use SSIS to backup the log, so it can backup, then copy it to where it needs to be.

UPDATE:

You can look at this question, it talks about how to go from SQL Server 2005 query to an Excel file: http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/DTS/Q_23090779.html

The useful answer is:

1. Create a stored procedure that will have the output you need to export in excel.
2. In the DTS package add a SQL connection and an excel conection. SQL conn should point you server and db and excel conn your file. If it doesn't exist just create one on the fly.
3. Create the Transformation task betreen the SQL conn and excel conn.
4. Double click on the arrow and in the trasformation data task properties window in Source tab instead of Table/View pick SQL query. In the panel below type EXEC sprocname, where sprocname will be the name of your procedure from step 1. 5.Click on Destination tab; if file/worksheet if doesn't exist will open a dialog window for creation. Edit if you want and click OK.
5. In Transformations tab define your trasnfromation by matching the columns. 7 Run.

If you want to run this automatically you need in an ongoing matter what you need is to define a Dynamic properties tasl where you can edit the excel connection to generate a name that will have a timestamp, (you can use an sql statement as well) and then in an Active X task create/copy the file from an existing structure file.

So

Dynamic Property Task ---> ActiveX task (copy from struct file to new generated name file ) ---> SQL conn ------> Excel conn.