Solved

remove files older than 5 days - in Powershell script

Posted on 2013-01-07
6
602 Views
Last Modified: 2014-08-08
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));
0
Comment
Question by:CraigLazar
  • 2
6 Comments
 
LVL 77

Expert Comment

by:arnold
ID: 38753613
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
 
LVL 40

Accepted Solution

by:
Subsun earned 350 total points
ID: 38754519
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
 
LVL 4

Author Comment

by:CraigLazar
ID: 38755137
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
 
LVL 4

Author Comment

by:CraigLazar
ID: 38878888
Hi, my apologies on that very late reply on this post.
0

Featured Post

Netscaler Common Configuration How To guides

If you use NetScaler you will want to see these guides. The NetScaler How To Guides show administrators how to get NetScaler up and configured by providing instructions for common scenarios and some not so common ones.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this previous article (https://oddytee.wordpress.com/2016/05/05/provision-new-office-365-user-and-mailbox-from-exchange-hybrid-via-powershell/), we made basic license assignments to users in O365. When I say basic, the method is the simplest way …
The Nano Server Image Builder helps you create a custom Nano Server image and bootable USB media with the aid of a graphical interface. Based on the inputs you provide, it generates images for deployment and creates reusable PowerShell scripts that …
Learn the basics of lists in Python. Lists, as their name suggests, are a means for ordering and storing values. : Lists are declared using brackets; for example: t = [1, 2, 3]: Lists may contain a mix of data types; for example: t = ['string', 1, T…
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…

839 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question