Sunday, February 12, 2012

Backing Up Transaction Logs

Dear sirs/ma'am

When setting up a maintenance plan in 2000, I can remove files older than 1 day.

When creating a maintenance task in 2005, I do not have that option (the database is in full mode). The only thing I can do (it seems) is to;

1. create a job with a step that uses a script from going through the motions of creating a log backup directly off the database. This is where I can say that the backup set will expire after 1 day, but in the destination field, the backup filename is hard-coded.

2. Then I schedule this script to run every hour. Sure enough, the log backup is overwritten due to the hard coded name.

The script reads;

BACKUP LOG [CSEPPWebCADB] TO DISK = N'D:\MSSQL\BACKUP\CSEPPWebCADB_backup.trn' WITH RETAINDAYS = 1, NOFORMAT, NOINIT, NAME = N'WebCADB_backup', SKIP, REWIND, NOUNLOAD, STATS = 10

How can I maintain a days worth of log backups or better yet, set up a maintenance plan to give me log backups every hour & delete them after they are a day old?

Sorry, I'm just not seeing how to do this.

Thanks for your help.


what is the srevice pack on this instance ? there are many enhancement in Maintenaceplan component in SP2 and the patch released after sp2 release. post back the result of

select @.@.Version

Madhu

|||

I don't believe that the person setting this up put SP 2 or the patch. I'll look into this and reply.

Thanks for your suggestion.

|||You need the correct SP2 loaded on your machine to start with. When you build a maintenance plan, you need to put in 2 tasks. One task is the backup database task. The second task is a clean up task. The clean up task is what removes the files older than X.|||

Thank you sir!

JDA

No comments:

Post a Comment