ROXL Lab

PowerShell Script for Fast Database Restore (SQL Server)

powershellsql-serverdatabaserestoreautomationscripting
Published on
•
3 mins read
•
--- views

This script will be useful if you need often reset your database. For example, due active development that affects data in tables. Script listed on my github repository here - [link] Scenario is:

  1. EXEC sp_configure 'show advanced options', 1
  2. EXEC sp_configure 'xp_cmdshell', 1
  3. Mounting remote unc-path (for restoring database from remote host)
  4. Bringing database that will be restorted offline
  5. Restoring database
  6. Bringing database back online

SQLCmd module listing (can be outdated, latest version exists in repository by link):

Function Exec(
    [System.String]$command,
    [System.String]$serverInstance
) {
    Invoke-Sqlcmd -Query $command -ServerInstance $ServerInstance
}

Function Test(
    [System.String] $serverInstance
) {
    $command = EXEC master..xp_cmdshell 'set'
    Exec $command $serverInstance
}

Function Reconfigure(
    [System.String] $serverInstance
) {
    $command = RECONFIGURE
    Exec $command $serverInstance
}

Function Show-Advanced-Options(
    [System.String] $serverInstance
) {
    $command = EXEC sp_configure 'show advanced options', 1
    Exec $command $serverInstance
    Reconfigure $serverInstance
}

Function Xp-CmdShell(
    [System.String] $serverInstance
) {
    $command = EXEC sp_configure 'xp_cmdshell', 1
    Exec $command $serverInstance
    $command = RECONFIGURE
    Reconfigure $serverInstance
}

Function Net-Use-Delete(
    [System.String] $drive,
    [System.String] $serverInstance
) {
    $command = EXEC XP_CMDSHELL 'net use $drive /delete /y'
    Exec $command $serverInstance
}

Function Net-Use-Add(
    [System.String] $drive,
    [System.String] $hostPath,
    [System.String] $hostUser,
    [System.String] $hostPassword,
    [System.String] $serverInstance
) {
    $command = EXEC XP_CMDSHELL 'net use $drive $hostPath /user:$hostUser $hostPassword /persistent:yes'
    Exec $command $serverInstance
}

Function Set-Database-Offline(
    [System.String] $dbName,
    [System.String] $serverInstance
) {
    $command = ALTER DATABASE [$dbName] SET OFFLINE WITH ROLLBACK IMMEDIATE
    Exec $command $serverInstance
}

Function Set-Database-Online(
    [System.String] $dbName,
    [System.String] $serverInstance
) {
    $command = ALTER DATABASE [$dbName] SET ONLINE
    Exec $command $serverInstance
}

Function Restore-Database(
    [System.String] $dbName,
    [System.String] $dbFilePath,
    [System.String] $serverInstance
) {
    $command = RESTORE DATABASE [$dbName] FROM DISK = '$dbFilePath' WITH REPLACE
    Exec $command $serverInstance
}

Restore database scenario listing (can be outdated too):

.\Load-Module.ps1 SQLCmd

$serverInstance = BOT-SP2016

$db = @{
    name = SharePoint_Content_80
    path = \db\ksup\content\2019.09.16.00.00.07.bak
}

$hostInfo = @{
    path = $env:hostPath
    user = $env:hostUser
    password = $env:hostPassword
}

Write-Host [x] database preconfig
Show-Advanced-Options $serverInstance
Xp-CmdShell $serverInstance

Write-Host [x] virtual path mappings
Net-Use-Delete H: $serverInstance
Net-Use-Add H: $hostInfo.path $hostInfo.user $hostInfo.password $serverInstance

Write-Host [x] taking database $($db.name) offline
Set-Database-Offline $db.name $serverInstance

Write-Host [x] restorting database $($db.name) from file $($db.path)
$path = Join-Path -Path $hostInfo.path -ChildPath $db.path
Restore-Database $db.name  $path $serverInstance

Write-Host [x] taking database $($db.name) online
Set-Database-Online $db.name $serverInstance
Write-Host [x] scenario completed -ForegroundColor Green

Attention: For correct script work, you need to setup some environment variables in you OS:

Variables are:

  • hostPath
  • hostUser
  • hostPassword

And set up variables in scenario script: - db.name, db.path