The way to keep Log backups?

Question

Post reply

The way to keep Log backups?

madhu-686862

SSChampion

Points: 10707
More actions
November 5, 2008 at 3:46 pm

#195459

Hi all,
I want to take log backups of 20 databases every 15 mins to the location 'Z:\backups\log\'
Is the way generally dba's follow to keep every 15 min logbackup in folder?
Generally how long the dba's keep the log backups?
and I want to setup a job to delete the oldbackups?
what is the script to delete the old backups in the location Z:\backups\log\ after 'x' days?
I know the anser to all above depends on the organization, but I would like to in general
Thanks

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Jonathan Kehayias One Orange Chip Points: 26778 More actions · Answer 1

There is a script on this article:

http://www.sqlservercentral.com/articles/Administering/usingvbscripttoautomatetasks/1171/

I use a Operating System (CmdExec) step to call the script. If you save the script as DeleteOldBackups.vbs, then the Command for the step would be:

cscript "DeleteOldBackups.vbs"

I modified/extended the one above for specifically deleteing backups in the same folder as the script. The changed code can be found:

http://code.msdn.microsoft.com/SQLExamples/Wiki/View.aspx?title=ExpressMaintenance&referringTitle=ExpressBackups#Cleanup

How long you keep the backups is really determined by your particular environment. My files are picked up by Netbackup and archived to our VLS every hour. I keep 2 days of backups on the local disk for the server that works like this, the current day, and the previous day. You can set in the scripts above how old the files should be to get deleted.

You can also look at the Maintenance Plan cleanup task which deletes files older than a set period in SQL Server 2005, but you need to have a post SP2 update applied to correct an error with them in SP2.

sayfrend SSCommitted Points: 1733 More actions · Answer 2

Hi all,

I want to take log backups of 20 databases every 15 mins to the location 'Z:\backups\log\'

Its good you create Backup device,If not you will see these things,

1. normal backup files will consume lot of space to view backup details, as there will be many database if you consider the frequency as 15 min.

2. When you restore these transactional log backups, it will be very difficult to apply these logs one by one.

I would do something like this, I will create a backup device, only one folder and it will contain all the backups for that particular database.

Is the way generally dba's follow to keep every 15 min logbackup in folder?

create a backup device.

Generally how long the dba's keep the log backups?

Depends on following,

1. How frequently you take your full backup, If you have a full backup does it make sense to keep a log backup for long time?

2. How critical your data is ( For point in time recovery given time and date ).

3. How much space you have on OS.

and I want to setup a job to delete the oldbackups?

I would do it through a maintenance plan, When you create a maintenance plan, this process will create jobs for you...

what is the script to delete the old backups in the location Z:\backups\log\ after 'x' days?

One of the tasks in Maintenance plans is Maintenance Cleanup Task (2005) which deletes older backups.

I know the anser to all above depends on the organization, but I would like to in general

This is also General....

Lets wait for Master's reply....

Thanks

Jerry Hung SSChampion Points: 12968 More actions · Answer 3

I would use the Maintenance Plan Wizard as well for regular backup and cleanup

Or, you could use the Log Shipping wizard to mimic the behaviour (take log backup every 15 mins, keep X days copy)

To delete files (without getting into VBS), FORFILE is a quick easy way (may not exist on all OS's and doesn't work with UNC)

FORFILES /p "Z:\BACKUP" /m *.trn /d -1 /c "cmd /c del @file"

SQLServerNewbieMCITP: Database Administrator SQL Server 2005