PoshCode Archive  Artifact [463b4e0c6e]

Artifact 463b4e0c6edbdb0baa90de525667b2670dee9fd2427cd93a18a04c896373033b:

• File Script-demonstrates-automated-SQL-database-.ps1 — part of check-in [0d20c62472] at 2018-06-10 13:12:01 on branch trunk — Script demonstrates automated SQL database restore using SQLPSX SQLServer module that relocates files to default directory and kills all connections to database. (user: unknown size: 1511)

# encoding: ascii
# api: powershell
# title: 
# description: Script demonstrates automated SQL database restore using SQLPSX SQLServer module that relocates files to default directory and kills all connections to database.
# version: 2.3.1
# type: module
# license: CC0
# x-poshcode-id: 2530
# x-archived: 2017-05-11T14:45:17
#
#
param($sqlserver, $filepath)

#Note: Uses Changeset 59378 or higher of SQLPSX SQLServer module http://sqlpsx.codeplex.com/SourceControl/changeset/view/58378#564810
#Added FileListOnly option to Invoke-SqlRestore. The option will be included in releases after 2.3.1

import-module sqlserver -force


$server = get-sqlserver $sqlserver

$filepath = Resolve-Path $filepath | select -ExpandProperty Path
$dbname = Get-ChildItem $filePath | select -ExpandProperty basename

$dataPath = Get-SqlDefaultDir -sqlserver $server -dirtype Data
$logPath = Get-SqlDefaultDir -sqlserver $server -dirtype Log

$relocateFiles = @{}
Invoke-SqlRestore -sqlserver $server  -filepath $filepath -fileListOnly | foreach { `
    if ($_.Type -eq 'L')
    { $physicalName = "$logPath\{0}" -f [system.io.path]::GetFileName("$($_.PhysicalName)") }
    else
    { $physicalName = "$dataPath\{0}" -f [system.io.path]::GetFileName("$($_.PhysicalName)") }
    $relocateFiles.Add("$($_.LogicalName)", "$physicalName")
}

$server.KillAllProcesses($dbname)

Invoke-SqlRestore -sqlserver $server -dbname $dbname -filepath $filepath -relocatefiles $relocateFiles -Verbose -force