CraigLazar
asked on
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.A ssembly]:: LoadWithPa rtialName( 'Microsoft .SqlServer .Connectio nInfo');
[void][System.Reflection.A ssembly]:: LoadWithPa rtialName( 'Microsoft .SqlServer .Managemen t.Sdk.Sfc' );
[void][System.Reflection.A ssembly]:: LoadWithPa rtialName( 'Microsoft .SqlServer .SMO');
# Requiered for SQL Server 2008 (SMO 10.0).
[void][System.Reflection.A ssembly]:: LoadWithPa rtialName( 'Microsoft .SqlServer .SMOExtend ed');
$Server = "TEL-ACCOUNTS"; # SQL Server Instance.
$Dest = "C:\SQL_PS_Bck\"; # Backup path on server (optional).
$srv = New-Object Microsoft.SqlServer.Manage ment.Smo.S erver $Server;
# If missing set default backup directory.
If ($Dest -eq "")
{ $Dest = $server.Settings.BackupDir ectory + "\" };
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.Mana gement.Smo .Backup");
$backup.Action = "Database";
$backup.Database = $db.Name;
$backup.Devices.AddDevice( $Dest + $db.Name + "_full_" + $timestamp + ".bak", "File");
$backup.BackupSetDescripti on = "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.Mana gement.Smo .Backup");
$backup.Action = "Log";
$backup.Database = $db.Name;
$backup.Devices.AddDevice( $Dest + $db.Name + "_log_" + $timestamp + ".trn", "File");
$backup.BackupSetDescripti on = "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));
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.A
[void][System.Reflection.A
[void][System.Reflection.A
# Requiered for SQL Server 2008 (SMO 10.0).
[void][System.Reflection.A
$Server = "TEL-ACCOUNTS"; # SQL Server Instance.
$Dest = "C:\SQL_PS_Bck\"; # Backup path on server (optional).
$srv = New-Object Microsoft.SqlServer.Manage
# If missing set default backup directory.
If ($Dest -eq "")
{ $Dest = $server.Settings.BackupDir
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.Mana
$backup.Action = "Database";
$backup.Database = $db.Name;
$backup.Devices.AddDevice(
$backup.BackupSetDescripti
$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.Mana
$backup.Action = "Log";
$backup.Database = $db.Name;
$backup.Devices.AddDevice(
$backup.BackupSetDescripti
#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));
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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
@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
ASKER
Hi, my apologies on that very late reply on this post.
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.