Powershell script to iterate through folder and restore SQL backups

Question

I am working on a PowerShell script which will loop through a directory containing SQL Server database backup .bak files; move the files to a different location; restore each of those files to the local SQL instance using OSQL.exe; output the SQL output to a log file.

This works OK, but I am willing to improve it.

$sourceBackupDirectory = "C:\FTP"
$sqlDatabaseLocation = "C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\"
$backupsLocation = "C:\DatabaseRestore\"

Get-ChildItem $sourceBackupDirectory -Filter "*.bak" -Recurse | Move-Item -Destination $backupsLocation #move files
$backupsLocationFiles = Get-ChildItem $backupsLocation -Filter "*.bak" #load files into array

$todayUK = Get-Date -format yyyyMMdd
$logsFolder = "LogFiles\"
$logFile = $backupsLocation + $logsFolder + $todayUK + ".txt"

foreach ($file in $backupsLocationFiles)
{
    $filePath = $backupsLocation + $file
    $file = [io.path]::GetFileNameWithoutExtension($file)
    #$filePath
    #$file

    RestoreDB $file $filePath
}

function global:RestoreDB ([string] $newDBName, [string] $backupFilePath)
{
    [string] $dbCommand = "RESTORE DATABASE [$newDBName] " +
    "FROM    DISK = N'$backupFilePath' " +
    "WITH    FILE = 1, " + 
    "MOVE N'$newDBName' " + 
    "TO N'$sqlDatabaseLocation" + "$newDBName.mdf', " + 
    "MOVE N'$newDBName" + "_log' " +
    "TO N'$sqlDatabaseLocation" + "$newDBName" + "_log.ldf', " + 
    "NOUNLOAD, REPLACE, STATS = 5"
    Write-Host $dbCommand

    OSQL.EXE -E -Q $dbCommand >> $logFile #this ouputs the SQL output into a logfile. 
}

Answer 1

Functions

They must be declared before they are called. This script would fail if run from a clean session the first time. Just make sure you have functions declared before they are called. At the beginning of a script is a natural in PowerShell. I came from a VBS background so this was odd for me at first too.

Improved string concatenation

In your function

The format operator would be of great use here. Especially since you are putting the same variable in several places. Like in Quills answer use of here-string helps with the new lines. Note that is it not required you can just use and open and closing quotes to wrap the whole thing up.

    $dbCommand = "RESTORE DATABASE [{0}]
FROM    DISK = N'{1}'
WITH    FILE = 1,  
MOVE N'{0}' 
TO N'{2}{0}.mdf', 
MOVE N'{0}_log'
TO N'{2}{0}_log.ldf',
NOUNLOAD, REPLACE, STATS = 5" -f $newDBName, $backupFilePath, $sqlDatabaseLocation

One could argue that readability is reduced but I think this makes it clearer. It removed all the + used for concatenation. No more need for repeated variables in the string. Casting it as a [string] is also redundant but that is just being nitpicky.

For log files

PowerShell is forgiving when it comes to variable expansion in strings. Since you are just dealing with string variables you can just do something simple like this.

$logFile = "$backupsLocation$logsFolder$todayUK.txt"

You can also use the Combine static method from [system.io.path]. This has the feature of being more resilient to the presence and lack of backslashes. It is not simple string concatenation so it is a little more robust.

$logFile = [System.IO.Path]::Combine($backupsLocation, $logsFolder, "$todayUK.txt")

Both of these commands would produce the same output.

Simplify gathering files.

Move-Item has a passthru switch so that it will pass the moved item down the pipeline. No need to requery to get the files again.

$backupsLocationFiles = Get-ChildItem $sourceBackupDirectory -Filter "*.bak" -Recurse | 
    Move-Item -Destination $backupsLocation -PassThru

Loop Calling Function

The loop where you are calling the function has some room for improvement.

You are using $file and changing its meaning from a file object then turning it into a simple string. Not really a best practice. Does not matter since you don't even really need it.

File objects returned from Get-ChildItem have a basename property which is exactly what you are trying to get from [io.path]::GetFileNameWithoutExtension($file). While not what it was designed for Join-Path will also build the path for you without relying on string concatenation.

foreach ($file in $backupsLocationFiles){
    $backupPath = Join-Path -Path $backupsLocation -ChildPath $file.Name
    RestoreDB -newDBName $file.BaseName -backupFilePath $filePath 
}

Answer 2

This is mostly style things, but:

• You should store all your constants at the top (including $logsFolder)

• #this ouputs the SQL output into a logfile: you misspelled outputs

• You should use the string concatenation syntax:

"TO N'$($sqlDatabaseLocation)$($newDBName)_log.ldf',"

• You only use $todayUK once, so you should include it directly as a string instead of declaring it.

• Instead of concatenating each string component together, use a multi line string operator like @" or a trailing backtick `.

    [string] $dbCommand = @"RESTORE DATABASE [$newDBName]
FROM    DISK = N'$backupFilePath' 
WITH    FILE = 1,
MOVE N'$newDBName'
TO N'$($sqlDatabaseLocation)$($newDBName).mdf', 
MOVE N'$($newDBName)_log'
TO N'$($sqlDatabaseLocation)$($newDBName)_log.ldf',
NOUNLOAD, REPLACE, STATS = 5"

• Now, you can just directly call that string instead of assigning it to $dbCommand:

    Write-Host @"RESTORE DATABASE [$newDBName]
FROM    DISK = N'$backupFilePath' 
WITH    FILE = 1,
MOVE N'$newDBName' #more stuff here