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));
PowershellShell ScriptingScripting Languages
Last Comment
CraigLazar
8/22/2022 - Mon
arnold
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.
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
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
CraigLazar
ASKER
Hi, my apologies on that very late reply on this post.
Your help has saved me hundreds of hours of internet surfing.
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.