PowerShell Script for fast database restore

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:

EXEC sp_configure ‘show advanced options’, 1
EXEC sp_configure ‘xp_cmdshell’, 1
Mounting remote unc-path (for restoring database from remote host)
Bringing database that will be restorted offline
Restoring database
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
}

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

.\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

mssql powershell sharepoint

Leave a Reply Cancel reply