Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Deleting BackUp Files Expand / Collapse
Author
Message
Posted Monday, February 28, 2011 12:58 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, February 28, 2011 12:49 PM
Points: 1, Visits: 0
Hello,
Im new to admin role.
Can somebody help in where and how to delete the backup files in UAT farm?
the files deleted in UAT will not cause any issue to PROD server, right??

I got few codes thats need to be executed to get the job done but where to execute them
and what all admin access do i need to have?

pls help me.


Thanks
Post #1070775
Posted Tuesday, March 1, 2011 6:51 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Friday, August 1, 2014 6:18 AM
Points: 1,618, Visits: 20,980
Deleting the backup files on UAT instance will not affect the PROD instance. To delete the files you need to have FULL permissions on the folder. If you are making using of xp_cmdshell in the script, you need to be a member of the sysadmin server role.

Pradeep Adiga
Blog: sqldbadiaries.com
Twitter: @pradeepadiga
Post #1071196
Posted Sunday, March 6, 2011 1:10 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 9:47 AM
Points: 6,306, Visits: 13,605
no need for xp_cmdshell or any other nasties. Create a sql server job step as activex script and set to VBS. The code i use is as follows

Option Explicit
'Delete all SQL Server backup files more than 5 days old

Dim oFS, oSQLBackupFol, oFol, oFil
Dim sPattern
sPattern = "*.bak"
Set oFS = CreateObject("Scripting.FileSystemObject")
Set oSQLBackupFol = oFS.GetFolder("drive:\path") 'Change this as appropriate

For Each oFol IN oSQLBackupFol.SubFolders 'get subfolders of the above path
For Each oFil in oFol.Files 'get each file in subfolder

If oFil.DateCreated < Now-5 then 'Change this as appropriate
If ucase(right(oFil.name, 4)) = ".BAK" then
oFil.Delete
End If
End If

Next
Next

Set oFS = nothing
Set oSQLBackupFol = nothing



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

"Ya can't make an omelette without breaking just a few eggs"
Post #1073899
Posted Monday, March 7, 2011 10:57 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Friday, August 22, 2014 12:24 PM
Points: 2,829, Visits: 8,477
How about a maintenance plan with a cleanup task ?


Post #1074321
Posted Monday, March 7, 2011 12:56 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Tuesday, August 26, 2014 9:11 PM
Points: 1,752, Visits: 1,324
srpd.m123 if you are new to Admin role and you are unfamiliar with where to run the code, maybe a Maintenance Plan would be best for you. You can use the Maintenance Plan Wizard if you do not see what you are looking for. In SQL Server Management Studio, Under Management, right click on Maintenance Plans and select Wizard.

If you are strapped for space, you will want to perform the cleanup step first and then the backup. Please make note that you will need to be on Service Pack 2 or higher for SQL Server 2005 to clean up old backups correctly.


Chris Powell

George: You're kidding.
Elroy: Nope.
George: Then lie to me and say you're kidding.
Post #1074423
Posted Wednesday, March 9, 2011 7:49 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Tuesday, April 19, 2011 7:31 AM
Points: 2,832, Visits: 5,316
Meet George Jetson (3/7/2011)
srpd.m123 if you are new to Admin role and you are unfamiliar with where to run the code, maybe a Maintenance Plan would be best for you. You can use the Maintenance Plan Wizard if you do not see what you are looking for. In SQL Server Management Studio, Under Management, right click on Maintenance Plans and select Wizard.

If you are strapped for space, you will want to perform the cleanup step first and then the backup. Please make note that you will need to be on Service Pack 2 or higher for SQL Server 2005 to clean up old backups correctly.


Thanks, now I have the Jetson's theme song stuck in my head. "Jane, his wife. Daughter Judy. His boy Elroy'......and the darned plinging of the piano keys - way too early in the day for this!


-- You can't be late until you show up.
Post #1075563
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse