A N

October 5, 2015

Restore Microsoft SQL *.bak file with the help of Powershell

Filed under: Tips — donrsh @ 12:17 pm
<#	
	.NOTES
	===========================================================================
	 Created on:   	2015-09-09 
	 Created by:   	Arash Nabi
	 Email: 	    arash@nabi.nu 

	===========================================================================
	.DESCRIPTION for 'Run-DBQuery' function
		The function connects to a Microsoft SQL database and query a TSQL query
	.Exampel
		Run-DBQuery -Database SERVER\SQLEXPRESS -QueryString "SELECT * FROM [table].[dbo].[DatabaseName]"
	===========================================================================
	.DESCRIPTION for 'Restore-DB' function
		The function restore a *.bak file to a Microsoft SQL database.
	.Exampel
		Restore-DB -Database DatabaseName -ServerInstance SERVER\SQLEXPRESS -BackupFile C:\bakfile.bak -ReplaceDatabase
		You have two switches, ReplaceDatabase and Confirm.
#>
function Run-DBQuery
{
	[CmdletBinding()]
	[OutputType([int])]
	Param
	(
		[Parameter(Mandatory = $True,
				   HelpMessage = "Please specify the SQL Server instance. Ex Computer\Instance",
				   ValueFromPipeline = $False)]
		[ValidateNotNullorEmpty()]
		$Database,
		[Parameter(Mandatory = $true,
				   ValueFromPipelineByPropertyName = $true,
				   HelpMessage = "Please specify the TSQL query.",
				   Position = 0)]
		[OutputType([System.Data.DataTable])]
		$QueryString
		
		
	)
	
	$ConnectionString = "Data Source=$Database;Integrated Security=True;User ID=;Password="
	
	$command = New-Object System.Data.SqlClient.SqlCommand ($QueryString, $ConnectionString)
	$adapter = New-Object System.Data.SqlClient.SqlDataAdapter ($command)
	
	#Load the Dataset
	$dataset = New-Object System.Data.DataSet
	[void]$adapter.Fill($dataset)
	
	#Return the Dataset
	return @(, $dataset.Tables[0])
}
function Restore-DB
{
	[CmdletBinding()]
	[OutputType([int])]
	Param
	(
		[Parameter(Mandatory = $true,
				   ValueFromPipelineByPropertyName = $False,
				   HelpMessage = "Please specify the SQL Server Database Name. Ex MyDB",
				   Position = 0)]
		[ValidateNotNullorEmpty()]
		$Database,
		[Parameter(Mandatory = $True,
				   HelpMessage = "Please specify the SQL Server instance. Ex Computer\Instance",
				   ValueFromPipeline = $False)]
		[ValidateNotNullorEmpty()]
		$ServerInstance,
		[Parameter(Mandatory = $false,
				   HelpMessage = "Please specify the SQL Server instance. Ex Computer\Instance",
				   ValueFromPipeline = $False)]
		[ValidateNotNullorEmpty()]
		$BackupFile,
		[Parameter(Mandatory = $false,
				   HelpMessage = "Do you want to replace this database?",
				   ValueFromPipeline = $False)]
		[switch]$ReplaceDatabase,
		[Parameter(Mandatory = $false,
				   HelpMessage = "Confrim the action?",
				   ValueFromPipeline = $False)]
		[switch]$Confirm
	)
	try
	{
		$Mod = Get-Module -ListAvailable | Where-Object { $_.Name -eq 'SQLPS' }
		if ($Mod)
		{
			Write-Verbose "Powershell SQL Module exist in $env:COMPUTERNAME. Continuing to restore database $Database"
			Import-Module -Name SQLPS -Verbose -ErrorAction Stop
			$GetDBSettings = Run-DBQuery -Database $ServerInstance -QueryString "select 
    InstanceDefaultDataPath = serverproperty('InstanceDefaultDataPath'),
    InstanceDefaultLogPath = serverproperty('InstanceDefaultLogPath')"
			$SQLDataPath = $GetDBSettings.InstanceDefaultDataPath
			$SQLLogPath = $GetDBSettings.InstanceDefaultLogPath
			
			Write-Verbose "Default Data Path is: $SQLDataPath"
			Write-Verbose "Default Log Path is: $SQLLogPath"
			
			$RelocateData = New-Object Microsoft.SqlServer.Management.Smo.RelocateFile($Database, "$($SQLDataPath)$Database.mdf")
			$RelocateLog = New-Object Microsoft.SqlServer.Management.Smo.RelocateFile("$($Database)_log", "$($SQLLogPath + $Database)_log.ldf")
			
			$params = @{
				'Serverinstance' = $ServerInstance
				'Database' = $Database
				'Backupfile' = $BackupFile
				'RelocateFile' = @($RelocateData, $RelocateLog)
			}
			
			
			if ($DatabaseObject)
			{
				$params.Add('DatabaseObject', $true)
			}
			if ($PSBoundParameters['Verbose'])
			{
				$params.Add('verbose', $true)
			}
			
			if ($ReplaceDatabase)
			{
				$params.Add('ReplaceDatabase', $true)
			}
			if ($Confirm)
			{
				$params.Add('Confirm', $true)
			}
					
			Restore-SqlDatabase @params
		}
		else
		{
			Write-warning "There is no powershell module for SQL exist in $env:COMPUTERNAME. Aborting..." -ForegroundColor Magenta
			return
		}
		
	}
	catch [System.Net.WebException], [System.Exception]
	{
		$errorMessage = $_.Exception.Message
		Write-Warning -Message "$errorMessage"
	}
	finally
	{
		
	}
	
}
Advertisements

Leave a Comment »

No comments yet.

RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Blog at WordPress.com.

%d bloggers like this: