Tag Archives: database

Move MSSQL Databases using Powershell with Detach\Attach

This is a script which can assist you in moving or copying a number of databases to a new server. We recently had to move move a lot of databases to new hardware and this came in very handy. The script uses the Detach and Attach method. You can move one database or you can pipeline a number of databases through the script. Database owners are maintained so you will need to migrate those logins prior to using the script. Also, the script does not reattach the databases on the source server. At some point I may update the script to accommodate reattaching the databases. For my current purpose, I did not need to do so. However, a switch is provided which informs the script to reattach the source database after it has been copied.

There is an assumption the script is run by a member of the ‘Domain Admins’ AD group. It shouldn’t be too difficult to change the script to use “shares” other than admin shares (i.e. Drives with a $ sign).

The code contains several references to functions which I have in an imported module. Over time I’ve developed a long list of my own helper functions and found it easier to have them in a module. The functions I reference are listed individually after the main source code.

Here is the mainline source for the script.

<#
.SYNOPSIS
	Copy SQL databases to another server.
.DESCRIPTION
	Uses the Detach and Attach method to copy or move a database or databases
	from one SQL Server to another. 
.PARAMETER Database
	Name of the database to be copied
.PARAMETER SourceServer
	Server and instance name of the source SQL Server
.PARAMETER DestinationServer 
	Server and Instance name of the destination SQL Server
.EXAMPLE
	$dblist | Migrate-Databases.ps1 -SourceServer 'SQLSERVER1' -DestinationServer 'SQLSERVER2\SQL2'
	Pipelines an array list of database names into the migration script with
	SQLSERVER1 as the source and SQLSERVER2\SQL2 as the destination.
.EXAMPLE 
	Migrate-Databases.ps1 -database 'MyDB' -SourceServer 'SQLSRV1' -DestinationServer 'SQLSRV2'
	Copies MyDB from SQLSRV1 to SQLSRV2
.NOTES
	Script uses administrative UNC shares names for all files to be copied and must be run by someone
	in the 'Domain Admins' group. Many of the functions called are part of an Imported 
	module containing many helper funtions. The 
	Author: 	John P. Wood
	Company:	ATOS, Inc\Lee County, FL BOCC
	Date:		June, 2013
	This code can be copied and used as long as proper attribution is given and
	these notes are kept intact.
#>
[CmdletBinding()]
Param(
	[Parameter(Mandatory=$true,Position=0,ValueFromPipeline=$true)]
	[String]$Database,
	[Parameter(Mandatory=$true,Position=1)]
	[string]$SourceServer,
	[Parameter(Mandatory=$true,Position=2)]
	[string]$DestinationServer,
	[Parameter(Mandatory=$false,Position=3)]
	[switch]$ReattachSource
)

Begin
{
	Set-StrictMode -Version Latest
	Try
	{
		$smosrc = New-SMOconnection -server $SourceServer
		$smodest = New-SMOconnection -server $DestinationServer
		$filedest = Get-UncPath -server $DestinationServer -path $smodest.DefaultFile
		$logdest = Get-UncPath -server $DestinationServer -path $smodest.DefaultLog
	}
	Catch
	{
		Throw
	}
}
Process
{
	Try
	{
		$db = $smosrc.Databases[$database]

		# Break the mirror
		If($db.IsMirroringEnabled -eq $true) {
			Write-Verbose "Breaking mirror for $($db.Name)"
			$db.ChangeMirroringState([Microsoft.SqlServer.Management.Smo.MirroringOption]::Off)
			$db.Alter()
			$db.Refresh()
		}

		# Get Data files
		$files = @(Get-DatabaseDataFiles -Database $db.Name -Server $SourceServer |
			ForEach-Object{$_.FileName})
		$files | Write-Verbose 

		# Get log files
		$logfiles = @($db.LogFiles | ForEach-Object{$_.Filename})
		$logfiles | Write-Verbose

		# get Owner
		$owner = $db.Owner
		$owner | Write-Verbose

		# Detach database
		$smosrc.KillAllProcesses($db.Name)
		$smosrc.DetachDatabase($db.Name,$false)

		# Copy files to destination
		$destfiles = @()
		$destlogs = @()

		$files | Get-UncPath -Server $SourceServer | Copy-Item -Destination $filedest
		$files | ForEach-Object {
			$destfiles += [System.IO.Path]::Combine($filedest,
				[System.IO.Path]::GetFileName($_))}

		$logfiles | Get-UncPath -Server $SourceServer | Copy-Item -Destination $logdest 
		$logfiles | ForEach-Object {
			$destlogs += [System.IO.Path]::Combine($logdest,
				[System.IO.Path]::GetFileName($_))
		}

		# Attach database
		$sc =  New-Object System.Collections.Specialized.StringCollection

		$destfiles | ForEach-Object{
			$sc.Add($(Get-LocalPath -uncpath $_ ))
		}
		$destlogs | ForEach-Object{
			$sc.Add($(Get-LocalPath -uncpath $_ ))
		}
		$smodest.AttachDatabase($db.Name, $sc, $owner, 
			[Microsoft.SqlServer.Management.Smo.AttachOptions]::None)

		# Reattach the source databases if switch is on
		If($ReattachSource.IsPresent)
		{
			$sc.Clear()
			$files | ForEach-Object{$sc.Add($_ )}
			$logfiles | ForEach-Object{$sc.Add($_ )}
			$smosrc.AttachDatabase($db.Name, $sc, $owner, 
				[Microsoft.SqlServer.Management.Smo.AttachOptions]::None)
		}
		Return $smodest.Databases[$database]
	}
	Catch 
	{
		$ex = $_.Exception
		Write-Error $ex.Message
		While($ex.InnerException) 
		{
			$ex = $ex.InnerException
			Write-Error $ex.Message
		}
	}	
}
End
{
	$smosrc.ConnectionContext.Disconnect()
	$smodest.ConnectionContext.Disconnect()

}

A small function to get a UNC path

Function Get-UncPath {
<#
.Synopsis
	Returns a windows UNC path when given a server and path on the server.
.Description
	Returns a windows UNC path when given a server and path on the server.
.Parameter ComputerName
	The name of a computer or full dns name. This will also take an SQL Instance
	and remove the Instance name leaving the server name.
.Parameter Path
	The local path name of a a path on the computer
.Example Get-UncPath -ComputerName "LCFSQL07" -Path "D:\Logs"
	Returns a UNC path of "\\LCFSQL07\D$\Logs"
.Example Get-UncPath -ComputerName "LCFSQL07\SQL01" -Path "D:\Logs"
	Returns a UNC path of "\\LCFSQL07\D$\Logs"
#>
	[CmdletBinding()]
	Param(
		[Parameter(Position=0,Mandatory=$true)]
		[Alias("ComputerName")]
		[string]$Server,
		[string]$path
	)
	Process{
	Join-Path "\\$($server.split('\')[0])" $path.replace(':','$')
	}
}

Function to get an SMO connection

Function New-SMOconnection
{
	[CmdletBinding()]
    Param (
		[Parameter(Mandatory=$true)]
		[string]$Server,
		[Parameter(Mandatory=$false)]
		[string]$ApplicationName = "Powershell SMO",
		[Parameter(Mandatory=$false)]
		[int]$StatementTimeout=0
	)
	$conn = New-Object Microsoft.SqlServer.Management.Common.ServerConnection($Server)
	$conn.ConnectTimeout = 5
	$conn.applicationName = $ApplicationName
	$conn.StatementTimeout = $StatementTimeout
	Try 
	{
		$conn.Connect()
		Return New-Object Microsoft.SqlServer.Management.Smo.Server($conn)
	}
	Catch 
	{
		Throw 
	}
}

A function to get physical files associated with a database

Function Get-DatabaseDataFiles
{	
	[CmdletBinding()]
	Param(
		[string]$Server,
		[string]$Database
	)
	Try
	{
		$smo = New-SMOconnection -server $Server
	}
	Catch
	{
		Throw $_.Exception
	}
	Try
	{
		$files = @()
		$db = $smo.Databases[$Database]
		If(!($db)) { Throw "$Database was not found" }
		Foreach($group in $db.FileGroups)
		{
			Foreach($datafile in $group.Files)
			{
				$hashtable = @{Group = $group.Name;
					Name = $datafile.Name;
					FileName = $datafile.Filename
				}
				$file = New-Object -TypeName PsObject -Property $hashtable
				$files += $file
			}
		}
	}
	Finally
	{
		$smo.ConnectionContext.Disconnect()
	}
		Return $files
}

Convert a UNC path back to a local path

Function Get-LocalPath {
	[CmdletBinding()]
	Param(
		[Parameter(Mandatory=$true)]
		[string]$uncpath
	)
	Process{
	$dirs = ($uncpath.Remove(0,2)).Split('\')
	$dir = ($dirs[1]).Replace('$',':')
	For([int]$ix=2; $ix -lt $dirs.Count; $ix++) {
		$dir += "\$($dirs[$ix])"
	}
	$dir
	}
}

Superfast Powershell Script Gets SQL Server Log Utilization

This is a nifty Powershell script which uses DBCC sqlperf(logspace) to get the log utilization for all your databases on all your servers. Best of all, it is super quick! I have over a dozen servers from 2000 to 20008 with numerous databases on each. This routine returns all my log information in a matter of seconds! It is also an example of developing your own Powershell for piping objects in your scripts. This little endeavor has taught me the real value of returning objects from the “GETS” I develop.

Measure-Command determined the collection process for ALL my servers took less than a second!

Measure_GetLogSpace

The code is presented in several blocks because of the modular design whereby I can possibly use some of the pieces in other ways. The functions are imbedded in a PowerShell module which has many other SQL helper functions not pertinent to this topic. Take a look at An Introduction to PowerShell Modules which is a really good start for developing and using modules in version 2. Using modules to help centralize my admin functions has been of enormous benefit especially when developing new scripts. I like putting my helper functions into a module because my PowerGUI editor is able to pick them up for intellisense. Love that PowerGUI! Also note: everything presented is dependent on at least Powershell V 2.

This first block is the main function which does the actual work of getting and delivering the log information. You will notice the use of Begin… Process… and End… blocks and  a parameter which allows pipeline input.

Function Get-LogSpaceUsed {
<#
.SYNOPSIS
Gets Log space utilization for all databases on an SQL Server instance.

.DESCRIPTION
Uses DBCC sqlperf(logspace) to get log space utilization for all instance databases
for one or multiple servers. Server names can be piped to this function. Output is 
an object or array of objects with ServerName, DatabaseName, PercentLogSpaceUsed,
and LogSizeMb.

.PARAMETER Server
Mandatory: The SQL Server instance which is to be evaluated. Accepts pipeline input.

.EXAMPLE
Get-LogSpaceUsed -Server 'Server\Instance'
Returns log utilization for all databases on an instance.

.EXAMPLE
Get-Content "C:\Scripts\PoSH\Servers.txt" | Get-LogSpaceUsed 
Pipes a list of server names to the function and returns log utilization for
all databases on each server in the list.

.NOTES
Author: John Wood
Date: August, 2012
WEB: http://www.webofwood.com/
email: Johnp.wood@gmail.com
Copyright: (c) 2012 John P. Wood
You are free to copy/use this script as long as you keep these notes and 
attibute credit to the original author
#>	[CmdletBinding()]
	Param(
	[Parameter(Position=0, Mandatory=$true,ValueFromPipeline = $true)] 
	[string]$server
	)

	Begin {
		$alldata = @()
		$query = @"
DECLARE 
	@dbcc varchar(250),
	@scom varchar(250)
	set @dbcc = 'dbcc sqlperf(logspace)'

Create Table #Logspace 
	(
		DatabaseName varchar(250),
		LogSizeMB dec(10,2),
		PercentLogSpaceUsed dec(5,2),
		[Status] int
	)

Insert Into #LogSpace
	EXEC (@dbcc)

SELECT @@SERVERNAME As 'ServerName', DatabaseName, PercentLogSpaceUsed, LogSizeMB 
FROM #LogSpace 
ORDER BY LogSizeMB desc

Drop Table #LogSpace 
"@
	}

	Process{
		Try {
			# Get a new connection
			$conn = New-SqlConnection -server $server
			$ds=New-Object system.Data.DataSet
			$da=New-Object system.Data.SqlClient.SqlDataAdapter($query,$conn)
			$da.fill($ds) | Out-Null
			$da.Dispose()
		}
		Catch { 
			Throw $_.Exception 
		}
		Finally {
			Close-SqlConnection -conn $conn
			$conn = $null

		}
		Foreach($row in $ds.Tables[0].Rows) {
			$alldata += $row
		}
	}
	End {
		Return $alldata
	}
}

A helper function to create a new SQL Connection suitable for running a query

Function New-SqlConnection {
	[CmdletBinding()]
	Param(
		[string]$server=$(Throw "Missing server param"),
		[string]$database="master"
	)
	Process{
		Try {
		    $conn = new-object ('System.Data.SqlClient.SqlConnection')
		    $connString = "Server=$server;Integrated Security=SSPI;Database=$database;Application Name='Powershell App'"
		    $conn.ConnectionString = $connString
		    $conn.StatisticsEnabled = $true
		    $conn.Open()
			$conn
		}
		Catch {Throw $_.Exception }
	}
}

AND….. the script to bring it together and deliver the results

<#
.SYNOPSIS
Gets Log space utilization for all databases on one or more SQL Server instance(s).

.DESCRIPTION
Pipes an array of server names to a module funtion which obtains log utilization
information for each database and presents the rusults

.PARAMETER Servers
Optional: The SQL Server instance which is to be evaluated. If a server or list 
of servers is not presented, the script will use a default list of servers from
a local file.

.EXAMPLE
Get-LogSpaceUsed 
Uses a default servers list from a local file to gather log information 

.EXAMPLE
Get-LogSpaceUsed -servers "Myserver1","myserver2\instance"
Gets log information for databases on two specific servers 

.NOTES
Author: John Wood
Date: August, 2012
WEB: http://www.webofwood.com/
email: Johnp.wood@gmail.com
Copyright: (c) 2012 John P. Wood
You are free to copy/use this script as long as you keep these notes and 
attibute credit to the original author
#>	
[CmdletBinding()]
Param(
	[Parameter(Position=0, Mandatory=$false)] 
	[string[]]$servers = $(%{Get-Content "C:\Scripts\PoSH\Servers.txt"}),
	[Parameter(Position=1, Mandatory=$false)] 
	[int]$maxlogsize = 100
)
$rows = @()
Try{
	$logs = $servers | Get-LogSpaceUsed | Where-Object {$_.LogSizeMb -ge $maxlogsize}
}
Catch {
	Write-Error "$server Server error:"
	$ex = $_.Exception
	Write-Error $ex.Message
	While($ex.InnerException) {
		$ex = $ex.InnerException
		Write-Error $ex.Message
	}
}
$logs

Failover SQL Server with Powershell

To failover an SQL mirrored database is fairly easy using SSMS or T-SQL. However, in a DR situation when things are happening quickly and time is of the essence, there is not always the luxury of having the resources available to accommodate failing over a large number of databases. If at all possible it is most desirable to fail over all over your databases while the principal server is still available.

In preparation for our next DR test I developed a Powershell script which would identify all mirrored databases and failover those databases. In the event the principal server is unavailable, the script can also be used for a forced failover on the mirrored server. Our environment consists of mirroring databases on one server to another like server at our DR site. Although you could mirror various databases from one instance to a multitude of instances, we adhere to a one-to-one instance environment. The script assumes this one-to-one placement but it could be easily changed to accommodate a one-to-many or even a many-to-many environment. That’s the beauty of Powershell scripting.

The script is developed on a Windows 7 platform with SQL 2008 R2 Tools. It was tested on Windows Server 2008 and 2008 R2 with SQL 2008 R2 EE. You are free to use the script as is or to modify it as needed. However, I do ask you maintain the attribution to the author


Param(
	[CmdletBinding()]
	[Parameter(Mandatory=$true,Position=0)]
	[string]$PrincipalServer,
	[Parameter(Mandatory=$true,Position=1)]
	[string]$MirrorServer,
	[Parameter(Mandatory=$false,Position=2)]
	[String[]]$Database,
	[Parameter(Mandatory=$false,Position=3)]
	[switch]$Force
)

Function New-SMOconnection {
	[CmdletBinding()]
    Param (
		[Parameter(Mandatory=$true)]
		[string]$server,
		[int]$StatementTimeout=0
	)
	Process {
	If(!(Test-Connection -ComputerName ($server.Split('\')[0]) -Quiet -Count 1)) {
		Throw "Could not connect to Server Machine $($server.Split('\')[0])."
	}
	$conn = New-Object Microsoft.SqlServer.Management.Common.ServerConnection($server)
	$conn.applicationName = "PowerShell SMO"
	$conn.StatementTimeout = $StatementTimeout
	Try {$conn.Connect()}
	Catch {
		Write-Exception $_.Exception
	}
	if ($conn.IsOpen -eq $false) {
		Throw "Could not connect to $server SQL Instance."
	}
	$smo = New-Object Microsoft.SqlServer.Management.Smo.Server($conn)
	$smo
	}
}

Function Write-Exception {
	Param($exception)
	Write-Host $exception.Message
	While ($exception.InnerException) {
		$exception = $exception.InnerException
		Write-Host $exception.Message
	}
}

Function Force-FailOver {
Param(
	[CmdletBinding()]
	[Parameter(Mandatory=$true,Position=0,ValueFromPipeLine=$true)]
	$database
)
	Process {
		Try{
			$database.ChangeMirroringState([Microsoft.SqlServer.Management.Smo.MirroringOption]::ForceFailoverAndAllowDataLoss)
		}
		Catch {
			Write-Exception $_.Exception
			Return "`nUnsuccessful Failover for $($database.Name)"
		}
		Return "Database mirror $($database.Name) is failed over with possible data loss"
	}
}

Function Normal-FailOver {
<#
	Normal failover executes on the Principal database. Once the failover completes,
	we then need to adjust our Mirroring Safety Level back to OFF. We have to switch
	to the Principal database which is now on the mirror server and alter the Saftey
	level there.
#>
Param(
	[CmdletBinding()]
	[Parameter(Mandatory=$true,Position=0)]
	$smo,
	[Parameter(Mandatory=$true,Position=0,ValueFromPipeLine=$true)]
	$database
)
	Process {
		Try{
			$database.MirroringSafetyLevel = [Microsoft.SqlServer.Management.Smo.MirroringSafetyLevel]::Full
			$database.Alter()
			# We have to wait to do the actual failover. The database needs to
			# catch up after changing the safety level and altering the database
			Start-Sleep -Seconds 3
			$database.ChangeMirroringState([Microsoft.SqlServer.Management.Smo.MirroringOption]::Failover)

			# Switching to server where the Principal now resides
			$db = $smo.Databases[$($Database.Name)]
			$db.MirroringSafetyLevel = [Microsoft.SqlServer.Management.Smo.MirroringSafetyLevel]::Off
			$db.Alter()
		}
		Catch {
			Write-Exception $_.Exception
			Return "`nUnsuccessfull failover for $($database.Name)"
		}
		Return "Database mirror $($database.Name) is failed over"
	}
}
# Initialize process
Set-StrictMode -Version Latest
[Void][System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.ConnectionInfo")
[Void][System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMO")
[Void][System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SmoExtended")
[Void][System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SqlEnum")

# Get a connection to the Mirror server
Try {
	$smoMirror = New-SMOconnection -server $MirrorServer
}
Catch {
	Write-Exception $_
	Throw "`nMirror server unavailable. Failover can not be run for $MirrorServer"
}

If($Force -eq $false) {
	# Get a connection to the Principal Server
	Try {
		$smoPrincipal = New-SMOconnection -server $PrincipalServer
	}
	Catch {
		Write-Exception $_.Exception
		Throw "Principal server $PrincipalServer is unavailable. Try running with the -Force parameter"
	}
	# Get a collection of mirrored databases from the principal server
	$databases = @($smoPrincipal.databases | Where-Object {$_.IsMirroringEnabled -and
		$_.Status -eq 'Normal' -and
		$_.MirroringStatus -eq [Microsoft.SqlServer.Management.Smo.MirroringStatus]::Synchronized})
}
Else {
	# Get a collection of mirrored databases from the mirrored server
	$databases = @($smoMirror.databases | Where-Object {$_.IsMirroringEnabled -AND ($_.Status -eq 'Restoring')})
}

# Use the database parameter list (if supplied) to limit the databases to the list
If($database) {
	$databases = @($databases | Where-Object {$database -contains $_.Name})
}

# Use a function to fail over the databases depending on the type of failover
If($Force -eq $true) {
	$databases | Force-FailOver
}
Else {
	$databases | Normal-FailOver -smo $smoMirror
}

If($smoPrincipal) {$smoPrincipal.ConnectionContext.Disconnect()}
$smoMirror.ConnectionContext.Disconnect()
Write-Host "`nScript completed for $($databases.Count) database(s)"

Powershell Function to Get Last SQL Server Backup File

Powershell is a great tool to use for a multitude of SQL Server administrative functions. I frequently need to know or get the file name of the last backup of a database. Sometimes I just need it for a report or to pass it off to another function. I created a Powershell function to obtain the data for me and had been using it for some time. Unfortunately, it recently broke down.

After some debugging, I was able to find the cause of my problem and I thought it would be a good idea to post my new and improved function. The function had to be changed because of snapshots being taken by VEEAM backups. We recently built a new SQL Server on VMWare and I implemented my normal backup methodology. However, when I recently wanted a report of the latest backups, my Powershell function failed! VEEAM snapshots are reported as backups but there is no file name associated with it. The new function excludes all backups which are snapshots.

I’ll first post the T-SQL to obtain the latest backup for a database and then I’ll show you how to use it in a Powershell function.

DECLARE @dbname sysname
SET		@dbname = 'YOURDB'
SELECT	f.physical_device_name as [backup]
FROM	msdb.dbo.backupset AS s WITH (nolock) INNER JOIN
			msdb.dbo.backupmediafamily AS f WITH (nolock) ON s.media_set_id = f.media_set_id
WHERE	(s.database_name = @dbname) AND (s.type = 'D') AND (f.device_type <> 7)
			AND (s.backup_finish_date = (SELECT     MAX(backup_finish_date)
FROM        msdb.dbo.backupset WITH (nolock)
WHERE	(database_name = @dbname) AND (type = 'D') AND (is_snapshot = 0)))

And now the Powershell use of it.

Param(
	[Parameter(Mandatory=$true,Position=0)]
	[string]$server,
	[Parameter(Mandatory=$true,Position=1)]
	[string]$database
)
Function New-SMOconnection {
    Param (
		[Parameter(Mandatory=$true)]
		[string]$server,
		[int]$StatementTimeout=0
	)
	If(!(Test-Connection -ComputerName ($server.Split('\')[0]) -Quiet -Count 1)) {
		Throw "Could not connect to SQL Server $server."
	}
	$conn = New-Object Microsoft.SqlServer.Management.Common.ServerConnection($server)
	$conn.applicationName = "PowerShell SMO"
	$conn.StatementTimeout = $StatementTimeout
	Try {$conn.Connect()}
	Catch {Throw $Error}
	if ($conn.IsOpen -eq $false) {
		Throw "Could not connect to SQL Instance $server."
	}
	$smo = New-Object Microsoft.SqlServer.Management.Smo.Server($conn)
	$smo
}
Function Get-LastBackupFile {
	Param(
		[string]$server,
		[string]$database
	)
	<# 	Use a hereto to construct the T-SQL
		You will notice the query eliminates any snapshots. This is because we
		sometimes have VEEAM backups on some servers.
	#>
	$qry = @"
DECLARE @dbname sysname 
SET @dbname = '$database'
SELECT	f.physical_device_name as [backup]
FROM	msdb.dbo.backupset AS s WITH (nolock) INNER JOIN
			msdb.dbo.backupmediafamily AS f WITH (nolock) ON s.media_set_id = f.media_set_id
WHERE	(s.database_name = @dbname) AND (s.type = 'D') AND (f.device_type <> 7) 
		AND (s.backup_finish_date = (SELECT MAX(backup_finish_date)
FROM         msdb.dbo.backupset WITH (nolock)
WHERE     (database_name = @dbname) AND (type = 'D') AND (is_snapshot = 0)))
"@

	# Get an SMO Connection
	$smo = New-SMOconnection -server $server
	# most appropriate to use MSDB
	$db = $smo.Databases["msdb"]
	# Execute query with results
	$rs = $db.ExecuteWithResults($qry)
	# SMO connection is no longer needed
	$smo.ConnectionContext.Disconnect()
	# Return the result
	$rs.Tables[0].Rows[0].Item('backup')
}
# Load SMO Assemblies
[Void][System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMO")
[Void][System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SmoExtended")

# Call the function and trap any error
Try {$backup = Get-LastBackupFile -server $server -database $database}
Catch {
	$ex = $Error[0].Exception
	Write-Host $ex.Message
	While($ex.InnerException) {
		$ex = $ex.InnerException
		Write-Host $ex.Message
	}
}
<# Verify the file  
	NOTE: most developent and run-time is performed on remote servers
	so there may be a need to convert to UNC format
#>
$backup = Join-Path "\\$($server.split('\')[0])" $backup.replace(':','$')

if(!(Test-Path $backup)) {
	Throw "Database backup $backup not found"
}
Write-Host $backup

Restore All SQL Transaction Logs using Powershell

I came across another opportunity for using Powershell when setting up mirroring for a large database at our DR site. The database was over 73Gig. The problem arose when it took a very long time to copy the backup and transaction logs and run the database restore. By the time the copy had finished another several transaction logs had been produced. While applying the *.bak  restore, yet another several transaction logs had been produced. Continue reading