PowerShell Script to Copy Most Recent Files

Posted on 2009-04-17
Last Modified: 2016-03-20
I am trying to write a PowerShell script that will select the most recent SQL backup file for each of my databases and then copy that file to another directory and then run WinRAR on those files to "zip" them up so that our online backup program (MozyPro) can back up this one compressed file.  The files are all stored in C:\MSSQL\Data\Backup with each database having it's own folder.  Also each folder holds 2 weeks of backup files, so I want the script to just select the most recent file from each folder.  The following Power Shell script works fine to list the files:

PS C:\MSSQL\Data\Backup> ls -r -fi *.bak | sort @{expression={$_.LastWriteTime};Descending=$true} | select Directory,
Name, LastWriteTime | Group-Object Directory | %{$_.Group | Select -first 1}

And this command will invoke WinRAR to "zip" up files that have been copied to C:\Mozy:
rar a -r "C:\Mozy\SQLBackups.rar" "C:\Mozy\*.bak"

Now how do I combine the two?  Basically how to I tell PowerShell to copy the files and then invoke WinRAR?
Question by:bmccleary
    LVL 38

    Accepted Solution

    This should do it.

    $source = "C:\MSSQL\Data\Backup"
    $filter = "*.bak"
    $dest = "C:\Mozy"
    $command = 'rar'
    $switches = 'a -r "' + $dest + '\SQLBackups.rar" "' + $dest + '\' + $filter + '"'
    ls -path $source -fi $filter | sort-object LastWriteTime | select-object -last 1 | copy-item -dest $dest
    [Diagnostics.Process]::Start($command, $switches)

    Open in new window


    Author Comment

    Thank you very much.  That was very helpful.  I needed to tweak it a bit because without the "-r" switch it wouldn't recurse through the subdirectories, and even after I added the "-r" switch, it would only select the one most recently modified file in all of the directories combined, not the most recently modified file in each sub directory.  Therefore, my final command looks like the following:

    ls -r -path "C:\MSSQL\Data\Backup" -fi *.bak | sort @{expression={$_.LastWriteTime};Descending=
    $true} | select-object | Group-Object Directory | %{$_.Group | Select -first 1} | copy-item -dest "C:\Mozy"

    So now I have two questions for you...
    1) Do you see any issues with the modified line I posted and if it would miss any files (e.g. inconsistant sort, etc.)?
    2) Is there anyway that I can combine the line I just wrote with the "[Diagnostics.Process]::Start($command, $switches)" line so I can run it as one command from within Task Scheduler, or will I have to save the two lines to a .ps1 file and run that?

    Again, I appreciate all the help!

    Author Comment

    One last question (we'll call it #3)...
    After WinRAR zips up the files, the *.bak file are left in that folder.  Is there anyway I can delete those files when WinRAR completes it's process?

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    What Should I Do With This Threat Intelligence?

    Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

    When you hear the word proxy, you may become apprehensive. This article will help you to understand Proxy and when it is useful. Let's talk Proxy for SQL Server. (Not in terms of Internet access.) Typically, you'll run into this type of problem w…
    This article is meant to give a basic understanding of how to use R Sweave as a way to merge LaTeX and R code seamlessly into one presentable document.
    Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
    The viewer will learn how to count occurrences of each item in an array.

    759 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    9 Experts available now in Live!

    Get 1:1 Help Now