Tlog backup device and data Expire

Question

Tlog backup device and data Expire

fawwad

SSC Eights!

Points: 965
More actions
September 24, 2010 at 5:27 am

#227181

Hi All,
I have two Backup Device one for DB backup and other for Tlog Backup.
i am using following strategy for my backup plan.
Full backup once a day , differential backup every 6 hours
with NOINIT means data gets append and I configure data to be Expire after 1 DAY.
while for Tlog : i have every 4 hour backup and it should get Expire after 1 Day.
Problem here, my data isnt CLEAN. I want EXPIRE data to be Removed from my Drive. so, i may not run out of space...
I tried Maintainance plan cleanup but it does give my any solution.
please help me out

Viewing 11 posts - 1 through 10 (of 10 total)

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

Oliiii SSCertifiable Points: 5328 More actions · Answer 1

The EXPIRE is not meant to remove data from your disk, it's meant to keep sql server from overwriting it before a specific date.

You have to clean the drive yourself with either a stored proc if you have xp_cmdshell enabled or some scheduled batch file.

Adiga One Orange Chip Points: 27284 More actions · Answer 2

Maintenance clean up task is the easiest method to clean up old backup files.

Pradeep Adiga
Blog: sqldbadiaries.com
Twitter: @pradeepadiga

fawwad SSC Eights! Points: 965 More actions · Answer 3

Adiga (9/24/2010)
Maintenance clean up task is the easiest method to clean up old backup files.

I have even tried Maintainance clean up task but no effect.

when i am going to Restore database for testing purpose, and choose backup device i got a list of backups from last week, i suppose there should be SINGLE file for last day !!!

please help me out here

Jeffrey Williams SSC Guru Points: 90303 More actions · Answer 4

fawwad (9/26/2010)
Adiga (9/24/2010)
Maintenance clean up task is the easiest method to clean up old backup files.
I have even tried Maintainance clean up task but no effect.
when i am going to Restore database for testing purpose, and choose backup device i got a list of backups from last week, i suppose there should be SINGLE file for last day !!!
please help me out here

Instead of using a backup device, you should change the backups use a new file every time. The maintenance plan will automatically date stamp each file for you.

Check the box in the maintenance plan to create a new folder for each database (do this for the transaction log backups too).

Now, you will have a folder for each database - and all backups in that folder. The maintenance cleanup task will then work and remove old files based upon the criteria you define.

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs

Adiga One Orange Chip Points: 27284 More actions · Answer 5

Agree with Jeffrey. If you are using Maintenance Plan to backup the database, it will create a file for each run. Here is a step by step guide to create a Backup Maintenance Plan.

Pradeep Adiga
Blog: sqldbadiaries.com
Twitter: @pradeepadiga

fawwad SSC Eights! Points: 965 More actions · Answer 6

If you are using Maintenance Plan to backup the database, it will create a file for each run.

fine , i will try your suggestion...

but i am creating backup with a simple job schedule (WITH NOINIT data append)and only using maintainance plan to clean up maintainance tasks...

does it still work ?? or is it necessary to have DB BACKUP WITH MAINTAINANCE PLAN on different files ??

Adiga One Orange Chip Points: 27284 More actions · Answer 7

but i am creating backup with a simple job schedule (WITH NOINIT data append)and only using maintainance plan to clean up maintainance tasks...

If you are using WITH NOINIT only one backup file is created and all the backup sets are appended to it.

does it still work ?? or is it necessary to have DB BACKUP WITH MAINTAINANCE PLAN on different files ??

In this case Cleanup task will not work because all backup sets are in one file.

Pradeep Adiga
Blog: sqldbadiaries.com
Twitter: @pradeepadiga

odanwada SSCrazy Points: 2133 More actions · Answer 8

Hi, I am Attaching the procedure for creating Standard Maintenance plan, we are following the same in our project it is working fine for us, Try this out.

Omeswar Danwada

fawwad SSC Eights! Points: 965 More actions · Answer 9

@ odanwada

really appreciate the efforts.

i will try this way with multiple files.

but a question just circling in my mind, what is the advantage of BACKUP DEVICE ?? 😛

odanwada SSCrazy Points: 2133 More actions · Answer 10

Backup Devices can be a tape drive or a disk file. Some times u need to take backup to a tape drives automatically at that time backup devices are helpful.