Restoring Transaction Logs in Powershell

Using the SQL Server UI to restore a bunch of transaction logs is the most painful thing I have every done. I created this script, and then tested restoring around 10,000 transaction logs and burnt through them pretty fast.

Create the script restore-transactionLog.ps1 with the following code. (I am using integrated auth and running the script on localhost. It wouldn’t be too hard to use different connection strings in the code if you wanted)

param
(
    [string] $dbName
)

process {

    if(test-path $_){
        $sql = "RESTORE LOG [$dbName] FROM  DISK = N'$($_.fullName)' 
                WITH  FILE = 1,  NORECOVERY,  NOUNLOAD,  STATS = 10"

        write-verbose $sql

        sqlcmd  -Q "$sql" -E

    }else{
        write-error "Can't find file $_"
    }
}

Then you can use it pretty easily in the following scenario.

gci c:\MyTransactionLogDir | 
    sort LastWriteTime | 
    .\restore-transactionLog.ps1 "myDb"

Menu