PowerShell: Restore SQL Database

RestoreDatabase.ps1

Param(
    # The bak file from which the restore will be performed
    [Parameter(Mandatory=$True)]
    [string]$file,

    # The directory where the databse files (MDF and LDF) will be saved/restored to
    [Parameter(Mandatory=$True)]
    [string]$directory,

    # The name of the database that is being restored
    [Parameter(Mandatory=$True)]
    [string]$database,

    # The server in which the database is hosted
    [Parameter(Mandatory=$False)]
    [string]$server = '(local)\SQLExpress2012',

    # A flag to specify whether or not to use integrated security.
    # If set to false, then SQL Authentication will be used 
    [Parameter(Mandatory=$False)]
    [boolean]$useIntegratedSecurity = $True,

    # Username for when SQL Authentication is being used
    [Parameter(Mandatory=$False)]
    [string]$username = "",

    # Password for when SQL Authentication is being used
    [Parameter(Mandatory=$False)]
    [string]$password = ""
)

# Ensure that the specified bak file exists.
if(-not(Test-Path $file -PathType Leaf))
{
    Write-Host "Specified BAK file not found: $file" -ForegroundColor Red
    exit 1
}

# Ensure that the file is a BAK file by checking it's extension
if(-not([System.IO.Path]::GetExtension($file) -eq ".bak"))
{
    Write-Host "Specified file is not a BAK file: $file" -ForegroundColor red
    exit 1
}

# Check if the specified directory where the MDF and LDF files are to be located actually exists.
# If it doesn't, then we should create that directory.
if(-not(Test-Path $directory -PathType Container))
{
    New-Item -ItemType Directory -Path $directory
}

# Specify the location of the SQL file that contains the TSQL to restore the database.
$sqlfile = "./RestoreDatabase.sql"

# Define the locations of where the MDF and LDF files should be located when restoring the database
$mdfFileLocation = "$directory\$database.mdf"
$ldfFileLocation = "$directory\$database.ldf"

# Fix any issues with incorrect path separators
$mdfFileLocation = $mdfFileLocation.Replace("/", "\").Replace("\\", "\")
$ldfFileLocation = $ldfFileLocation.Replace("/", "\").Replace("\\", "\")

# Define the variables that need to be passed to the SQL script.
# Within the SQL script, these should be referenced as $(VariableName).
$sqlVar_BakFile = "BakFileToRestoreFrom=$file"
$sqlVar_MdfFile = "MdfFileLocation=$mdfFileLocation"
$sqlVar_LdfFile = "MdfFileLocation=$mdfFileLocation"
$sqlVar_DbName = "DatabaseName=$database"

# Create the object that will be passed as to the -Variables paramaeter
$sqlVariables = $sqlVar_BakFile, $sqlVar_MdfFile, $sqlVar_LdfFile, $sqlVar_DbName

# Check how we should be authenticating with SQL server. integrated security or SQL Authentication
if(-not($useIntegratedSecurity) -and $username.Length -gt 0 -and $password.Length -gt 0)
{
    # Execute SQL script using SQL Authentication
    Invoke-Sqlcmd -ServerInstance $server -Username $username -Password $password -InputFile $sqlfile -Variable $sqlVariables
}
else
{
    # Execute SQL script using integrated security.
    # The user that will execute the SQL script is the same user that is running the PowerShell session
    Invoke-Sqlcmd -ServerInstance $server -InputFile $sqlfile -Variable $sqlVariable
}

RestoreDatabase.sql

USE [master]
GO

-- NOTE
-- When using this SQL file some varaibles need to be passed when running the script.
-- This can be specified using the -Variable parameter when calling Invoke-SqlCmd from PowerShell.
--  - $(BakFileToRestoreFrom)  - The location of the BAK file
--  - $(MdfFileLocation)       - The location of where the MDF (DATA) file is to be located
--  - $(LdfFileLocation)       - The location of where the LDF (Logs) file is to be located
--  - $(DatabaseName)          - The name of that database that is to be restored (or created if it doesn't exist)

-- Declare a Table varaible that will hold the data obtained from running RESTORE FILELISTONLY
DECLARE @fileListTable TABLE (
    [LogicalName]           NVARCHAR(128),
    [PhysicalName]          NVARCHAR(260),
    [Type]                  CHAR(1),
    [FileGroupName]         NVARCHAR(128),
    [Size]                  NUMERIC(20,0),
    [MaxSize]               NUMERIC(20,0),
    [FileID]                BIGINT,
    [CreateLSN]             NUMERIC(25,0),
    [DropLSN]               NUMERIC(25,0),
    [UniqueID]              UNIQUEIDENTIFIER,
    [ReadOnlyLSN]           NUMERIC(25,0),
    [ReadWriteLSN]          NUMERIC(25,0),
    [BackupSizeInBytes]     BIGINT,
    [SourceBlockSize]       INT,
    [FileGroupID]           INT,
    [LogGroupGUID]          UNIQUEIDENTIFIER,
    [DifferentialBaseLSN]   NUMERIC(25,0),
    [DifferentialBaseGUID]  UNIQUEIDENTIFIER,
    [IsReadOnly]            BIT,
    [IsPresent]             BIT,
    [TDEThumbprint]         VARBINARY(32) -- remove this column if using SQL 2005
)

-- Declare and set a variable to specify the location of the BAK file that is to be used for the restore
DECLARE @BakFile NVARCHAR(300)
SET @BakFile = '$(BakFileToRestoreFrom)'

-- Declare a variable to specify the location of the MDF file (Data) where the database is to be restored to
DECLARE @MdfFile NVARCHAR(260)
SET @MdfFile = '$(MdfFileLocation)'

-- Declare and set a variable to specify the location of the LDF file (Logs) where the database is to be restored to
DECLARE @LdfFile NVARCHAR(260)
SET @LdfFile = '$(LdfFileLocation)'

-- Declare and set a variable to specify the database name
DECLARE @DatabaseName NVARCHAR(200)
SET @DatabaseName = '$(DatabaseName)'

-- Retrive the information about the BAK file
-- Store this data within @fileListTable Table variable
-- We need to be able to retrieve certain data from this and making it available within a table a way to do that
INSERT INTO @fileListTable EXEC('RESTORE FILELISTONLY FROM DISK = ''' + @BakFile + '''');

-- Retrieve the Logical name for the MDF file (Data)
-- We need this to be able to tell the restore to move the MDF file (Data) to the new location
DECLARE @LogicalName_Data NVARCHAR(128)
SELECT @LogicalName_Data = LogicalName FROM @fileListTable WHERE [Type] = 'D' ;

-- Retrieve the Logical name for the LDF file (Logs)
-- We need this to be able to tell the restore to move the LDF file (Log) to the new location
DECLARE @LogicalName_Log NVARCHAR(128)
SELECT @LogicalName_Log = LogicalName FROM @fileListTable WHERE [Type] = 'L' ;

-- Generate a series of KILL statements to terminate any existing connections to the specified database.
-- This does not KILL the sessions here it only generates the statement that we still need to execute.
DECLARE @kill NVARCHAR(MAX) = '';
SELECT  @kill + 'kill ' + CONVERT(varchar(5), session_id) + ';'  
FROM sys.dm_exec_sessions
WHERE database_id  = db_id(@DatabaseName)

-- Execute the KILL statements
-- We need to do this to ensure that there are no existing connections to the database prior to restoring the database.
-- This should prevent the error where it states that "Exclusive access could not be obtained because the database is in use"
EXEC(@kill);

-- Execute the restore
RESTORE DATABASE @DatabaseName
FROM DISK = @BakFile WITH 
MOVE @LogicalName_Data TO @MdfFile, 
MOVE @LogicalName_Log TO @LdfFile,
REPLACE
GO