PoshCode Archive  Artifact Content

Artifact 669dad893cd9115af4a56de5662e7c668c5043115b8bf96d8277e2e8b3632369:

  • File Restore-Database.ps1 — part of check-in [fe318ed71c] at 2018-06-10 13:12:03 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: Chad Miller size: 1590)

# encoding: ascii
# api: powershell
# title: Restore-Database.ps1
# 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
# author: Chad Miller
# license: CC0
# x-poshcode-id: 2531
# x-archived: 2014-11-12T15:42:45
# x-published: 2011-02-28T07:22:00
#
#
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