DELETING BACKUP FILES USING A SCRIPT.

Question

Post reply

DELETING BACKUP FILES USING A SCRIPT.

Viewing 15 posts - 16 through 30 (of 32 total)

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

Perry Whittle SSC Guru Points: 234013 More actions · Answer 1

PlaysWithHisFood (4/16/2010)
using the maintenance plan clean up task will also work.

ensure you upgrade to at least SP2 first though

-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs" 😉

crazy4sql SSCoach Points: 19590 More actions · Answer 2

just few more doubt in the same task :-

What if I want to retain the latest 2 files no matter what date they are in and then delete the rest?

Can we do it?

----------
Ashish

dmoldovan SSCertifiable Points: 6334 More actions · Answer 3

You can also try a PowerShell script

http://blogs.technet.com/heyscriptingguy/archive/2007/10/31/hey-scripting-guy-how-can-i-use-windows-powershell-to-delete-all-the-files-in-a-folder-older-than-90-days.aspx

If you prefer VB, search their "script repository".

You can easily schedule the script using SQL agent or Windows "scheduled tasks".

emily-1119612 SSCarpal Tunnel Points: 4166 More actions · Answer 4

This works as well. http://www.sqlservercentral.com/scripts/Administration/62729/

I gave up on xp_delete_file.

Perry Whittle SSC Guru Points: 234013 More actions · Answer 5

emily-1119612 (4/21/2010)
I gave up on xp_delete_file.

it does work extremely well if you take the time to get to grips with its parameters

-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs" 😉

crazy4sql SSCoach Points: 19590 More actions · Answer 6

thanks for this but xp_delete_file work based on date calculation while my requirement is based on latest available files.

For Example:-

i have a server where lot of dumps of backup file and no more required. So want to delete it but for safe side want to retain the latest 2 files.

We have lots of databases and for some of the database job is running everyday while for some every week and for some monthly.

So requrement is to do the task by script and maintain latest 2 file in each database folder.

hope requirement is clear now

----------
Ashish

steve block SSCertifiable Points: 7698 More actions · Answer 7

Here are two stored proceedures. One that performs backups for a specific type (Full, Differential, Transaction Logs) with a specific naming convention to the sub-directory for the database and the other that keeps a specific number of full backups and deletes all of the other backups in the sub-directory that are older (based upon the name assigned to it in the first SP).

Steve

RML51 SSCarpal Tunnel Points: 4410 More actions · Answer 8

We've used the following in a batch command file. You can of course execute that from a scheduled SQL Server Agent Job.

@echo off

echo.

echo Removing old emrRun log files

forfiles /p E:\MSSQL.1\MSSQL\BACKUPS /s /m *.bak /d -7 /c "cmd /c del @path"

You have to edit for the pathname to your backups. The "-7" is number of days, so change it to "-3" for three days. Also, if you just want to delete some of your backups files, you can change the "*.bak" to something like "XYZ*.bak" to only delete backups of dbs with names prefaced with "XYZ".

crazy4sql SSCoach Points: 19590 More actions · Answer 9

lovely scripts...

but unfortunately again

substring(fil_nam,@iLen, 12) < @cDateTime comparing with date and then deciding on delete.

while I want to retain the latest 2 files, no matter since when the files are there in folder, but just latest two to retain

----------
Ashish

emily-1119612 SSCarpal Tunnel Points: 4166 More actions · Answer 10

ashish.kuriyal (4/22/2010)
lovely scripts...
while I want to retain the latest 2 files, no matter since when the files are there in folder, but just latest two to retain

You should be able to query against table backupmediafamily to find the files you don't want to delete.

USE MSDB

SELECT TOP 2 (physical_device_name),*

FROM dbo.backupmediafamily

WHERE physical_device_name like ...

ORDER BY media_set_id DESC

BharatBhushan SSC Eights! Points: 927 More actions · Answer 11

You can create a maintenance cleanup task and schedule it as per your requirement.

Thanks

steve block SSCertifiable Points: 7698 More actions · Answer 12

Ashish,

You did not look very closely to the scripts. The script finds the oldest backup file you want to keep (depending upon how many sets you wish to keep)

--Extract the YyyyMmDdHhMm from the oldest

-- Full Backupfile you want to Keep

-- The filename is DbName_Full_YyyyMmDdHhMm.FUL

SET @iLen = len(@vcDbName) + 7

SET @cDateTime = substring(@vcFileName,@iLen,12)

and uses that to find the files which are older than that based upon the name. You have to use the first SP, usp_Db_Backup, for it to work correctly but it would do exactly as you ask. The whole point of this scipt is to keep a specific number of set of backups.

Don't just look quickly at the script and assume you know what it is doing.

Steve

crazy4sql SSCoach Points: 19590 More actions · Answer 13

yes, you were right.

Finally little bit modification and it did worked across all my environment.

thanks a ton for this.

----------
Ashish

steve block SSCertifiable Points: 7698 More actions · Answer 14

steve block

SSCertifiable

Points: 7698

April 23, 2010 at 6:39 am

#1156777

You're welcome.

Glad it worked for you.

Steve

andrew.neuman SSC Veteran Points: 231 More actions · Answer 15

If you're not familar with writing the code and your not wanting to use a maintenance plan perhaps you could create a maintenance plan that does exactly what you want using the GUI tools then, if you need it as a script, you can view the T-SQL from the properties of each task the, delete the maintenance plan.