remove files older than 5 days - in Powershell script

Hi,
I have the folloing script file i inherited. I need to add to it a loop to remove all files older than 5 days. Is it also possible to zip the files as well ?
thanks very much

## Full + Log Backup of MS SQL Server databases/span>            
## with SMO.            
[void][System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.ConnectionInfo');            
[void][System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.Management.Sdk.Sfc');            
[void][System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO');            
# Requiered for SQL Server 2008 (SMO 10.0).            
[void][System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMOExtended');            
$Server = "TEL-ACCOUNTS";     # SQL Server Instance.            
$Dest = "C:\SQL_PS_Bck\";    # Backup path on server (optional).            
$srv = New-Object Microsoft.SqlServer.Management.Smo.Server $Server;            
# If missing set default backup directory.            
If ($Dest -eq "")            
{ $Dest = $server.Settings.BackupDirectory + "\" };            
Write-Output ("Started at: " + (Get-Date -format yyyy-MM-dd-HH:mm:ss));            
# Full-backup for every database            
foreach ($db in $srv.Databases)            
{            
    If($db.Name -ne "tempdb")  # Non need to backup TempDB            
    {            
        $timestamp = Get-Date -format yyyy-MM-dd-HH-mm-ss;            
        $backup = New-Object ("Microsoft.SqlServer.Management.Smo.Backup");            
        $backup.Action = "Database";            
        $backup.Database = $db.Name;            
        $backup.Devices.AddDevice($Dest + $db.Name + "_full_" + $timestamp + ".bak", "File");            
        $backup.BackupSetDescription = "Full backup of " + $db.Name + " " + $timestamp;            
        $backup.Incremental = 0;            
        # Starting full backup process.            
        $backup.SqlBackup($srv);    
        # For db with recovery mode <> simple: Log backup.            
        If ($db.RecoveryModel -ne 3)            
        {            
            $timestamp = Get-Date -format yyyy-MM-dd-HH-mm-ss;            
            $backup = New-Object ("Microsoft.SqlServer.Management.Smo.Backup");            
            $backup.Action = "Log";            
            $backup.Database = $db.Name;            
            $backup.Devices.AddDevice($Dest + $db.Name + "_log_" + $timestamp + ".trn", "File");            
            $backup.BackupSetDescription = "Log backup of " + $db.Name + " " + $timestamp;            
            #Specify that the log must be truncated after the backup is complete.            
            $backup.LogTruncation = "Truncate";
            # Starting log backup process            
            $backup.SqlBackup($srv);            
        };            
    };            
};            
Write-Output ("Finished at: " + (Get-Date -format  yyyy-MM-dd-HH:mm:ss));
LVL 4
CraigLazarAsked:
Who is Participating?
 
SubsunConnect With a Mentor Commented:
You can use the following command to remove files older than specific date. The parameter -whatif is added for testing. Remove it to make any permanent changes.
GCI "C:\folder\" | where {$_.LastWriteTime -le $(get-date).AddDays(-5)} | Remove-Item -whatif

Open in new window


You can refer the following article for function to zip files..
http://blogs.msdn.com/b/daiken/archive/2007/02/12/compress-files-with-windows-powershell-then-package-a-windows-vista-sidebar-gadget.aspx

usage:
new-zip c:\demo\myzip.zip
dir c:\demo\files\*.* -Recurse | add-Zip c:\demo\myzip.zip

Open in new window

0
 
arnoldCommented:
Peculiar why you would not use the ssms and sql agent jobs to configure both the backups and the cleanup.
http://sqlautopowershell.blogspot.com/2010/01/rarring-files-maintenance-plans-and.html?m=1
IMHO, using ssms and sql agent to manage the schedule of backup/cleanup  provides for a single location to look.
The compression for saving space is not worth the overhead for the compressing.
You might be able to use 7zip instead of winrar for the compression.
0
 
CraigLazarAuthor Commented:
Hi Thanks for the input
@arnold, i am running SQL Express, so cannot setup the standard backup job in Management Studio. So this is why i am using this powershell sctipt which works really well. Just i need to remove old files.
@Subsan, thanks for the code i will give it a go :)
thanks again
0
 
CraigLazarAuthor Commented:
Hi, my apologies on that very late reply on this post.
0
All Courses

From novice to tech pro — start learning today.