PSCI

1.1.0

baseModules/PPoShSqlTools/0.1.5/Public/Restore-SqlDatabase.ps1

function Restore-SqlDatabase {
    <#
    .SYNOPSIS
        Restores database on MSSQL Server.

    .DESCRIPTION
    Uses Restore-SqlDatabase.sql sql script to restore database.

    .EXAMPLE
        Restore-SqlDatabase -DatabaseName "DbName" -ConnectionString "data source=localhost;integrated security=True" -Path "C:\database.bak"
    #> 

    [CmdletBinding()]
    [OutputType([void])]
    param(
        [Parameter(Mandatory=$true)]
        [string]
        $ConnectionString,

        # Database name - if not specified, Initial Catalog from ConnectionString will be used.
        [Parameter(Mandatory=$false)]
        [string]
        $DatabaseName, 
        
        # Backup file path.
        [Parameter(Mandatory=$true)]
        [string]
        $Path,

        # Remote share credential to use if $Path is an UNC path. Note the file will be copied to localhost if this set, and this will work only if
        # you're connecting to local database.
        [Parameter(Mandatory=$false)]
        [PSCredential] 
        $RemoteShareCredential,

        # Timeout for executing sql restore command.
        [Parameter(Mandatory=$false)] 
        [int]
        $QueryTimeoutInSeconds = 3600
    )

    try { 
        if (!$DatabaseName) { 
            $csb = New-Object -TypeName System.Data.SqlClient.SqlConnectionStringBuilder -ArgumentList $ConnectionString
            $DatabaseName = $csb.InitialCatalog
        }
        if (!$DatabaseName) {
            throw "No database name - please specify -DatabaseName or add Initial Catalog to ConnectionString."
        }

        if ($RemoteShareCredential) {
            $shareDir = Split-Path -Path $Path -Parent
            #TODO: disconnect-share by prefix
            Connect-Share -Path $shareDir -Credential $RemoteShareCredential
            $tempDir = New-TempDirectory
            Write-Log -Info "Copying '$Path' to '$tempDir'"
            Copy-Item -Path $Path -Destination $tempDir -Force
            #TODO: unhardcode this user
            Set-SimpleAcl -Path $tempDir -User 'NT Service\MSSQLSERVER' -Permission 'Read' -Type 'Allow'
            $Path = Join-Path -Path $tempDir -ChildPath (Split-Path -Path $Path -Leaf)
        }

        $sqlScript = Join-Path -Path $PSScriptRoot -ChildPath "Restore-SqlDatabase.sql"
        $parameters =  @{ "DatabaseName" = $DatabaseName }
        $parameters += @{ "Path" = $Path }
        [void](Invoke-Sql -ConnectionString $ConnectionString -InputFile $sqlScript -SqlCmdVariables $parameters -QueryTimeoutInSeconds $QueryTimeoutInSeconds -DatabaseName '')
    } finally {
        if ($RemoteShareCredential) {
            Disconnect-Share -Path $shareDir
            Remove-TempDirectory
        }
    }
}