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

PowerShell-Database-Backup1

Output

 PowerShell-Database-Backup

 

Advertisements

About Prashanth Jayaram

DB Technologist, Author, Blogger, Operations-Mgr at CTS, Automation Expert, Technet WIKI Ninja, MVB and Powershell Geek You can connect me via https://social.technet.microsoft.com/profile/prashanth jayaram/ http://www.sqlshack.com/author/prashanth/ https://www.tumblr.com/blog/prashantjayaram http://www.sqlservercentral.com/blogs/powersql-by-prashanth-jayaram/ http://www.toadworld.com/members/prashanthjayaram/ My Articles are published in following sites http://www.ssas-info.com/analysis-services-articles/ http://db-pub.com/ http://www.sswug.org/sswugresearch/community/
This entry was posted in PowerShell, SQL and tagged , , . Bookmark the permalink.

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

  1. Golam Kabir says:

    it’s a nice one – thanks

  2. 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??!!

    • 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

  3. Simple but useful. Thanks

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s