PowerShell – Backup Individual database and delete all it’s respective files by keeping a recent file

Problem Statement

The requirement is to backup individual database and after successful backup the script should retain the most recent file and delete the rest from a directory for that specific database.

The below Powershell script is used to backup a specific database on a given directory

[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMO") | Out-Null
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SmoExtended") | Out-Null
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.ConnectionInfo") | Out-Null
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SmoEnum") | Out-Null
#Define the SQL Instancet
$InstanceName='DBSP18001'
#We define the folder path as a variable
$bkdir = 'F:\PowerSQL'  
#Name of the database, the search is based on this parameter
$dbName='PowerSQL'
$s = New-Object ('Microsoft.SqlServer.Management.Smo.Server') $InstanceName
$dt = get-date -format yyyyMMddHHmm
#Create a Backup object instance with the Microsoft.SqlServer.Management.Smo.Backup namespace 
$dbBackup = new-object ("Microsoft.SqlServer.Management.Smo.Backup")
#Set the Database property to Northwind 
$dbBackup.Database = $dbname
#Add the backup file to the Devices collection and specify File as the backup type 
$dbBackup.Devices.AddDevice($bkdir + "\" + $dbname + "_db_" + $dt + ".bak", "File")
#Specify the Action property to generate a FULL backup 
$dbBackup.Action="Database"
#Call the SqlBackup method to generate the backup 
$dbBackup.SqlBackup($s)

To retain a most recent file and delete the rest by sorting the files on LastwriteTime then skip a first one.

#Get the latest backup file for the specific database
$file=get-ChildItem $bkdir -Filter $dbname_db_*.bak | Select-object LastWriteTime,directoryname,name | Sort-Object -Property LastwriteTime -Descending | Select -Skip 1
#write-host $file
foreach($f in $file)
{
$filename=$bkdir+'\'+$f.name
write-output 'File can be deleted' $filename
remove-item $filename -Force
}

The full transcript which initiates backup for specific database , retains the most recent file and deletes the rest is given below. In the script the remove-item is commented. Please be sure of what you are doing before un-commenting the remove-item line.

[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMO") | Out-Null
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SmoExtended") | Out-Null
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.ConnectionInfo") | Out-Null
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SmoEnum") | Out-Null
#Define the SQL Instancet
$InstanceName='DBSP18001'
#We define the folder path as a variable
$bkdir = 'F:\PowerSQL'  
#Name of the database, the search is based on this parameter
$dbName='PowerSQL'
$s = New-Object ('Microsoft.SqlServer.Management.Smo.Server') $InstanceName
$dt = get-date -format yyyyMMddHHmm
#Create a Backup object instance with the Microsoft.SqlServer.Management.Smo.Backup namespace 
$dbBackup = new-object ("Microsoft.SqlServer.Management.Smo.Backup")
#Set the Database property to Northwind 
$dbBackup.Database = $dbname
#Add the backup file to the Devices collection and specify File as the backup type 
$dbBackup.Devices.AddDevice($bkdir + "\" + $dbname + "_db_" + $dt + ".bak", "File")
#Specify the Action property to generate a FULL backup 
$dbBackup.Action="Database"
#Call the SqlBackup method to generate the backup 
$dbBackup.SqlBackup($s)
#Get the latest backup file for the specific database
$file=get-ChildItem $bkdir -Filter $dbname_db_*.bak | Select-object LastWriteTime,directoryname,name | Sort-Object -Property LastwriteTime -Descending | Select -Skip 1
#write-host $file
foreach($f in $file)
{
$filename=$bkdir+'\'+$f.name
write-output 'File can be deleted' $filename
remove-item $filename -Force
}




 

Script Execution From PowerShell-ISE

Output