Bulk delete files from library using name of file

Question

I have a library with greater than 10,000 files and I need a way to bulk delete files whose name includes text like "bad_file" or a certain value in another column. This is SP2013 on-prem so I do have access to DBs and PowerShell.

I know with PowerShell I can run a loop and access the file.Name attribute and do something such as

file.Name -like "bad_file*"

We also have a metadata column called "document type". Is it possible to delete all files with a certain value in that column?

My concern using PowerShell is the size of this library and checks and balances. I don't want to run this through thousands of files and find out it did something unexpected. Is there a way to first export the list of files for review and then change the code to delete after reviewing?

Does anyone have experience doing something like this using PowerShell, or even going the database route? Could you provide an example of how you accomplished this?

Thanks!

Answer 1

You shouldn't do anything directly in the content database. This can lead to many problems and is unsupported and discouraged by Microsoft. If you want to use PowerShell, you have several methods to do this. Generally, you can get list items using CAML query with Contains condition and export information about them to CSV for verification. Then you can use batch processing to delete them in one request.

$webUrl = ''
$listName = ''
$query = '<Where>' +
    '<Contains>' +
    '<FieldRef Name="FileLeafRef"/><Value Type="Text">bad_file</Value>' +
    '</Contains>' +
    '</Where>'
$viewFields = '<FieldRef Name="FileRef"/><FieldRef Name="FileLeafRef"/>'
$delete = $false

Start-SPAssignment -Global

try {
    $web = Get-SPWeb $webUrl
    $list = $web.Lists[$listName]
    $spQuery = New-Object -TypeName Microsoft.SharePoint.SPQuery
    $spQuery.Query = $query
    $spQuery.ViewFields = $viewFields
    $spQuery.ViewFieldsOnly = $true
    $spQuery.ViewAttributes = 'Scope="Recursive"'
    $items = $list.GetItems($spQuery)
    $items | Select-Object ID, @{Name="FileName";Expression={$_["FileLeafRef"]}} |
        Export-Csv -Path ".\FilesToDelete.csv" -NoTypeInformation
    if ($delete) {
        $batch = '<?xml version="1.0" encoding="UTF-8"?>'
        $batch += '<ows:Batch>'
        foreach ($item in $items) {
            $batch += '<Method>'
            $batch += '<SetList Scope="Request">' + $list.ID + '</SetList>'
            $batch += '<SetVar Name="Cmd">Delete</SetVar>'
            $batch += '<SetVar Name="ID">' + $item.ID + '</SetVar>'
            $batch += '<SetVar Name="owsfileref">' + $item["FileRef"] + '</SetVar>'
            $batch += '</Method>'
        }
        $batch += '</ows:Batch>'
        $web.ProcessBatchData($batch)
    }
}
catch {
    Write-Error $_
}

Stop-SPAssignment -Global

Batch delete moves files to Recycle Bin, so if you want to delete them permanently, you may want to erase Recycle Bin manually or using code:

$web.RecycleBin.DeleteAll()
$web.Site.RecycleBin.DeleteAll()

Instead batch delete, you could just loop through list items and call Delete() method on each, but this would be slower.

Answer 2

If you have ShareGate, you can create a custom view to display all the files titles with "bad_file." Then just delete all the files within the view via ShareGate.