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
it’s a nice one – thanks
LikeLike
I would question the requirement to delete all other backups. I think it is generally considered bad practice to only keep a single backup. What if that single backup you have kept is corrupted? You’ve just deleted your only non-corrupt backups. Now you must go find a new employer. Instead of deleting the older backups, I would propose to copy/move them off to a different drive or network share or other backup location. This would offer flexibility of restores in case of failures. You are testing your backups by restoring them somewhere else, right??!!
LikeLike
Hi John,
Absolutely right!! There are some instances where you don’t want to keep the backup files for Test and Dev environment. This is just an example to maintain the latest file. This code can be reused for various scenarios. I have written this based on the request from one of the OP in Microsoft forum.
Thank you for reading and making valuable suggestion. Going forward I will define the problem statement with some more clarity.
Thank you.
Prashanth
LikeLike
Simple but useful. Thanks
LikeLike