sql-serverpowershelldatabase-restore

Powershell script to restore a SQL Server database with multiple TRN files

I am trying to come up with a Powershell script to dynamically do 'Restore Database' in SQL Server 2019 with multiple TRN (or BAK in my case) files that are located in one folder on a daily basis.

I will manually do the full backup first, and this task will be scheduled to run after (once on a daily basis).

So, a Python script will grab only yesterday's files from another folder into this folder, and this Powershell script will execute to run to restore a database using these TRN / BAK files (located in this folder).

The plan is go thru each TRN files (located in the same folder) sequentially (not with the time files were created, but by file name).

For example, it will start from "..04" --> "..12" in this case.

enter image description here

I found some examples from this site, but I was not sure how to code where it recognize the sequence ("..04" --> "..12") to run.

PS C:\> $File = Get-ChildItem c:\backups, \\server1\backups -recurse
PS C:\> $File | Restore-DbaDatabase -SqlInstance Server1\Instance -UseDestinationDefaultDirectories
Solution

  • So, by default, I think Get-ChildItem should be already displaying the files starting from lowest to highest but if you want to make sure you could try something like this and see if the output fits your case.

    For starting the test I'll create files using the same names as yours:

    $i=1;1..12|foreach{
    $null > "LOG_us_bcan_multi_replica_20210427$($i.ToString('0#')).bak"
    $null > "LOG_us_bcan_multi_replica_20200327$($i.ToString('0#')).bak"
    $i++
}

    This creates 24 files with the same naming convention you have.

    We know sorting by DateTime is possible so we can use string manipulation to get the date of your FileNames and use the ParseExact method on them.

    Example:

    # Assuming your current directory is the directory where the .bak files are

$expression={
    [datetime]::ParseExact(
        $_.BaseName.split('replica_')[1],'yyyyMMddHH',[cultureinfo]::InvariantCulture
    )
}

Get-ChildItem | Select-Object BaseName,@{n='DateFromFileName';e=$expression}

# This will return a side by side FileName with their Dates from FileName

BaseName                             DateFromFileName
--------                             ----------------
LOG_us_bcan_multi_replica_2020032701 3/27/2020 1:00:00 AM
LOG_us_bcan_multi_replica_2020032702 3/27/2020 2:00:00 AM
LOG_us_bcan_multi_replica_2020032703 3/27/2020 3:00:00 AM
LOG_us_bcan_multi_replica_2020032704 3/27/2020 4:00:00 AM
LOG_us_bcan_multi_replica_2020032705 3/27/2020 5:00:00 AM
LOG_us_bcan_multi_replica_2020032706 3/27/2020 6:00:00 AM
.....

    Now we can use the same $expression with Sort-Object instead of Select-Object

    Get-ChildItem | Sort-Object $expression