question

nightingale99 avatar image
nightingale99 asked

PowerShell Script to find the latest file

Hello , I'm trying to write a powershell script which would find me the latest file in a directory . For example I have a sql backup files stored on D:\Backups folder and the sql backups are taken daily and 5 days worth of backup are stored on the directory . sqlbackup_23042015.bak sqlbackup_24042015.bak sqlbackup_25042015.bak The powershell script should be able to identify the latest backup file ,if there is no file for that particular day when the powershell is run it needs to return a warning message as well. Is this achievable ? Thanks for all your help .
powershell
2 comments
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Thanks both for sending the script . I guess I'll get the base I want from these two scripts .
0 Likes 0 ·
ASK SQL Server Central runs by you voting. For all helpful answers below, please indicate this by clicking on the thumbs up next to those answers. If any one answer lead to a solution, please indicate this by clicking on the check mark next that answer.
0 Likes 0 ·
DenisT avatar image
DenisT answered

Here you go! It could have been one liner but I decided to break it up for easier following. Let me know if you have any questions. Working with PoSH is a LOT of fun :)

    $BackupFolder = 'D:\Temp\Backup' #Paste the path to your backup folder b/w the single quotes
    #Let's get the files from the BACKUP folder
    $LatestBackup = Get-ChildItem -Path $BackupFolder | Where-Object {$_.Name -ilike "*.bak"} | Sort-Object -Descending LastWriteTime | Select-Object Name,LastWriteTime -First 1
    #Don't have to do it but I used it in the ELSE
    $LatestBackupName = $LatestBackup.Name
    
    if ($LatestBackupName -eq $null)
    {
     Write-Host "No BAK files were found in the path provided"
    }
    else
    {
     #If the backup LastWriteTime is less than today then the backup failed.
     if (($LatestBackup).LastWriteTime.ToString("M/dd/yyyy") -lt (Get-Date -Format d)
     {
      Write-Host "WARNING: There is no today's backup!"
     }
     else
     {
      Write-Host "The latest backup is $LatestBackupName"
     }
    }
5 comments
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Hi Denis , Really appreciate your help . Only one thing I noticed is that when I run the script after deleting yesterdays backup or even day before's it gives me the message "the latest backup is $takes the latest among the existing backup files" ,it doesn't give me the warning "There is no backup today " . Would you be able to help again please . And also is there a way we get powershell to send the notification as an email ? Thanks a lot
0 Likes 0 ·
Sorry, but I didn't understand your issue. Where does this come from -- "the latest backup is $takes the latest among the existing backup files"? Yes, it can send emails if you have an SMTP server to relay messages -- http://blogs.msdn.com/b/rkramesh/archive/2012/03/16/sending-email-using-powershell-script.aspx
0 Likes 0 ·
Also modified the script to handle when there are not BAK files in the backup path.
0 Likes 0 ·
Appreciate your prompt reply . what I meant was that on the D:\ we store 5 days .bak file . So the script will check for the latest backup and give me the result . For eg : while checking for files if it doesn't find .bak files for 28th april and 27th april but finds backup files for 26th april,instead of giving me the warning that there is no backup for today it gives me the message that the latest backup is 26April backup , Do you get the issue ?
0 Likes 0 ·
ok, thank you for explaining but I cannot reproduce the issue on my end! Works ok here! No matter what I do, if I don't have the today's file, I get the warning.
0 Likes 0 ·
Shawn_Melton avatar image
Shawn_Melton answered

I would actually take a bit of a different route based on what the end result or outcome is for this script. SQL Server is much better source at (1) determining when the last backup was done for a database, and (2) giving me the file path of that backup. One thing to consider is what if you end up with an application like Sharepoint that can take native backups of a database from the admin console. You can add your logic where you want for a date being older than current run time and if the backup file exist in a different directory, this is just code for the meat of getting the path initially.

So if I want to use PowerShell to get the last physical backup file and the test of that path is good:

    function Get-LatestBackup
    {
        [cmdletbinding()]
        param(
            [Parameter(
                Mandatory=$true,
                Position=0
                )]
            [ValidateNotNull()]
            [Alias("instance")]
            [string]$server
            )
    
        # For SQL Server 2012
        Import-Module SQLPS -DisableNameChecking -NoClobber | Out-Null
# For SQL Server 2008 and 2008 R2, un-comment the next line #Add-PSSnapin SQL* $s = New-Object Microsoft.SqlServer.Management.Smo.Server $server $dbList = $s.Databases | where {$_.ID -ne 2} | select Name, LastBackupDate #check whether the last backup exist on the server foreach ($db in $dbList) { $currentDB = $db.Name [datetime]$lastBackupDate = $db.LastBackupDate $query = @" SELECT bmf.physical_device_name FROM sys.databases s INNER JOIN msdb.dbo.backupset bs ON (bs.database_name = s.name ) LEFT OUTER JOIN msdb.dbo.backupmediaset bms ON ( bs.media_set_id = bms.media_set_id ) LEFT OUTER JOIN msdb.dbo.backupmediafamily bmf ON ( bmf.media_set_id = bms.media_set_id ) WHERE s.name = '$($currentDB)' AND bs.backup_finish_date = '$($lastBackupDate)' "@ $backupPath = (Invoke-Sqlcmd -ServerInstance $s -Database msdb -Query $query -QueryTimeout ([int]::MaxValue)).physical_device_name if (Test-Path $backupPath) { #Backup Exist Write-Verbose "Backup path for current database [$currentDB] found to be: $backupPath" } else { #backup file does not exist Write-Verbose "Backup path for current database [$currentDB] not found: $backupPath" #do something, put your logic here for what action you want to take } } }

An example execution of this from my local machine: ![alt text][1]

[1]: /storage/attachments/2400-capture.jpg


capture.jpg (363.1 KiB)
4 comments
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Thanks, Shawn! The best approach to set up the backup task to let you know that it failed :) I think it's worth mentioning, thought that this approach will work with SQL Server 2012 and above. The SQLPS module has been included with 2012+. In the earlier editions, you'll have to jump thought some hoops.
0 Likes 0 ·
This will work on SQL Server 2008 and up, all you have to do is switch `Import-Module` to `Add-Snappin`. However the OP does not state the version they are working on.
0 Likes 0 ·

I get this error on my Microsoft SQL Server 2008 R2 (SP2) - 10.50.4033.0 (X64): Import-Module : The specified module 'SQLPS' was not loaded because no valid module file was found in any module direct ory. At line:1 char:14 + Import-Module <<<< SQLPS -DisableNameChecking + CategoryInfo : ResourceUnavailable: (SQLPS:String) [Import-Module], FileNotFoundException + FullyQualifiedErrorId : Modules_ModuleNotFound,Microsoft.PowerShell.Commands.ImportModuleCommand

From this link -- Practical PowerShell for Developers and DBAs "To use the Invoke-Sqlcmd cmdlet you must load (import) the sqlps module. To import it, you must install it. To install it, you must find and download it. Don’t laugh; while it is installed with SQL Server 2012 it is not available with earlier SQL Server versions and it is not at all obvious where to get it."

0 Likes 0 ·
@DenisT As I stated you need to change line 15 in the script from: `Import-Module SQLPS` To `Add-PSSnapin SQL*` SQL Server 2008 and SQL Server 2008 R2 Microsoft released SQL PowerShell as a Snap-in. In SQL Server 2012 they changed it to be a module for more easier management and updates. If you have installed SQL Server 2008 R2 Management Tools then the snap-in exist. You can also download the SQLPS for SQL Server 2008 or 2012 by downloading the SQL Server feature pack for that version. You can find the one for 2008 here: http://www.microsoft.com/en-us/download/details.aspx?id=26728 And then for 2012 here: http://www.microsoft.com/en-us/download/details.aspx?id=29065
0 Likes 0 ·
nightingale99 avatar image
nightingale99 answered
@DenisT - Thanks a lot for providing the script .I edited the script a bit more to suit my environment . If anyone is looking for a script to check if a backup file exists and send an email if it doesn't then here is the script . $BackupFolder = 'D:\Backups' #Paste the path to your backup folder b/w the single quotes $Date = "{0:yyyy_MM_dd}" -f (get-date).AddDays(-1) #If you backup is taken the previous night and your job runs the next day morning this date factor will come in handy #if the backup files are stored in subfolders then you need this script to search the backup folders for specific .bak files ForEach ($LatestBackupPath in Get-ChildItem -Path $BackupFolder) { $LatestBackupName = Get-ChildItem $LatestBackupPath.Fullname|Where-Object {$_.Name -ilike "*.bak"} | Sort-Object -Descending LastWriteTime | Select-Object Name,LastWriteTime -First 1 $LatestBackupName = $LatestBackupName.Name foreach ( $LatestBackupFile in $LatestBackupName) { if ($LatestBackupFile -eq $null) { "No BAK files were found in the folder $LatestBackupPath " | Out-File c:\temp\ output.txt -Append } else { #If the backup LastWriteTime is less than today then the backup failed. if ($LatestBackupFile -notmatch ($Date)) { "WARNING: There is no backup today in folder $LatestBackupPath !!!" | Out-File c:\temp\ output.txt -Append } else { "The latest backup is $LatestBackupFile" | Out-File c:\temp\ output.txt -Append } } } } $recipient = ”your email” $smtpserver = "smtpserver" $msg = new-object Net.Mail.MailMessage $smtp = new-object Net.Mail.SmtpClient($smtpServer) $msg.From = "noreply_email" $msg.To.Add($recipient) $msg.Subject = "SQLServer Backup Report" $msg.Body = "Please find the daily Backup Report" foreach ($file in gci "c:\temp\ output.txt"){ $att = New-Object Net.Mail.Attachment($file.fullname) $msg.Attachments.Add($att) } $smtp.Send($msg) #get-content c:\temp\ output1.txt | select-string 'WARNING' - use this code if you want to check for any warnings inside the file and then send an email
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.