February 28, 2011 at 12:58 pm
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
March 1, 2011 at 6:51 am
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
March 6, 2011 at 1:10 pm
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" 😉
March 7, 2011 at 10:57 am
How about a maintenance plan with a cleanup task ?
March 7, 2011 at 12:56 pm
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.
March 9, 2011 at 7:49 am
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.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply