Tag Archives: Powershell

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(':','$')
	}
}

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
	}
}
Share

Capture Powershell Output in Windows Scheduled Task

Ever see the Geico commercial where the guy says it took three months to teach a guinea pig to say “Row”? “Such a simple word”!

Well, I’ve hunted around, tried all kinds of methods, and spent hours on what should have been a simple task! Finally, I think I’ve found the only reliable method for capturing PowerShell output when it is run as a Windows Scheduled Task.

The long and short of it is: Use a CMD or BAT program to call the PowerShell script. You may not have to use a CMD program but in some cases you will. A lot of it depends on the parameters being passed and if those parameters contain a “List” item or whether there are single quotes or double quotes and who knows what else. I tried many variations of supplying PowerShell parms in task scheduler. Some worked in some cases and not in others. The only consistent method which worked in every case was to use a CMD program.

With this method you can not only capture all the PowerShell output (stderr, error, verbose, etc.) to a file using redirection but you can also capture any return code and thereby have the Windows Task “know” the script succeeded or failed.

There are no changes necessary to your PowerShell script unless you want to capture an Exit code.  What you do need to do is create a CMD program which calls your Posh using a format such as:

Echo off
::Execute a Powershell script from a CMD
If (%1)==() GOTO Missing
Powershell.exe -NonInteractive -NoLogo -NoProfile -Command ""D:\Scripts\MyPosh.ps1"" -Server %1 -Warehouse ""%2"" 2>&1> ""D:\logs\PoshLog_%1.txt"";Return $LASTEXITCODE"

:Missing
Echo "Parms are missing"
Exit 4

The key here is the –Command syntax. I have found this does not work properly when using –File. You must use –Command and enter the entire command (including script parms, redirection and the Return $LASTEXITCODE) within double quotes. You must also double double-quote any parms containing spaces. My example Command can be broken down as:

      1. The complete path and name of the PowerShell script to run
      2. Three parms I am supplying to the script as indicated by %1-%3. These parms are entered in the Windows Scheduled Task which calls this cmd script.
      3. The redirection of stderr to stdout and all redirected to a file using 2>&1> “C:\MyPath\log.txt”
      4. Command separator “;” the semicolon
      5. Capture the $LASTEXITCODE for the previous command (the Posh script) and throw it out to Windows Scheduler

 The Task Scheduler would look something like this  WinTaskSched.png The arguments portions is like this:

LCFSQLT04 "W:\SQL Backups" "'DOTProperty','Property'"

It has three arguments of which the last is a “list”

Emitting an Error

In order to capture an error from your Powershell script, you have to emit that error by using an EXIT statement. A RETURN statement will not work.

Share

Powershell SQL Backup with Event Handler

A persistent problem I’ve had is capturing the output messages when executing SQL procedures. That is, until I recently learned how to capture those messages for output using an event handler.

The example function, provided below, executes an SQL Backup, uses an event handler to capture the output messages, and passes the information back to the calling routine.

Function Backup-SqlDatabase
<#
.SYNOPSIS
    Backs up an sql database to local or remote disk.

.DESCRIPTION
    This script will perform a full back up of the specified database to a local or network disk.

.PARAMETER  Server
    Specifies the sqlserver or instance name you wish to connect to and restore.

.PARAMETER Database
    Specifies the Database name you wish to connect to and backup.

.PARAMETER Path
    Specifies the path to the back up file.

.EXAMPLE
    Run-SqlBackup -Server "Server\MySql" -Database "MyDb" `
		-Path "\\server\D$\Backups\MySql.bak"
    Backs up database "MyDb" on SQL Server instance "Server\MySql" to a file on
	a network share.
.NOTES
	When backing up to a network share, permissions must be set appropriately 
	for the SQL Service Account.
#>
{
	[CmdletBinding()]
    param(
        [Parameter(Mandatory=$true)]
        [string]$Server,
        [Parameter(Mandatory=$true)]
        [string]$Database,
        [parameter(Mandatory=$true)]
        [string]$Path
    )
	$output = ""
	$handler = [System.Data.SqlClient.SqlInfoMessageEventHandler] {
		param($sender, $event) 
	   	$output += $event.Message;
	} 
    $SQLConn = New-Object System.Data.SQLClient.SQLConnection
    $SQLConn.ConnectionString = "Server=$Server; Trusted_Connection=True"
    Try 
	{
		$SQLConn.add_InfoMessage($handler); 
		$SQLConn.FireInfoMessageEventOnUserErrors = $false;
        $SQLConn.Open()
    }
    Catch 
	{
		Throw $_
    }

    Write-Verbose "Connected to server $server"
    $SQLCmd = New-Object System.Data.SQLClient.SQLCommand
    $SQLcmd = $SQLconn.CreateCommand()
    $sqlcmd.commandtimeout=0
    $SQLcmd.CommandText="BACKUP DATABASE [$database] 
		TO  DISK = N'$path' 
		WITH  DESCRIPTION = N'$database Full Backup', 
		NOFORMAT, NOINIT,  
		NAME = N'$database-Full Database Backup', 
		SKIP, NOREWIND, NOUNLOAD"
    Try
	{
        $SQLcmd.Executenonquery() | Out-Null
  	}
    Catch
	{
		Throw $_
    }
    Finally
	{
        $SQLconn.close()
    }

    $backup = new-object PSObject -Property @{
        Server = $Server;
        Database = $Database;
		BackupFile = $path;
		Messages = $output}
	Return $backup
}

Calling the Function

$path = "\\MyServer\G$\SQL Backups\MyDB.bak"
Backup-SqlDatabase -Server ThisServer -Database "MyDB" -Verbose -Path $path |
	Format-List Server,Database,Path,Messages

The output

Event Messages Captured

EventMessages Captured

Share

Powershell Impersonation – the easy way

Have you ever had a script which runs great against multiple SQL instances but then you find it failing on a new instance?

I have a script which backs up all my SQL Server databases and then copies the local backups to warehouse storage. The script runs as a Windows Scheduled task on our storage server for each SQL Instance.

Usually when I get a new SQL Server, I just add a new Windows Scheduled Task on our storage server, adjust the param to point to the new server, and the script backs up all the databases on the server\instance. Great! However, I recently found a job in which the backups were failing consistently. Not good! . It was a fairly new “test” server so I probably did not pay as close attention to it as I should have when I first set it up.

The bottom line causing the problem The only reason I knew of the failures was because of having I set up the script to write and event log for any error. However,, it was one of those “catch-all” errors with little significant information. If you don’t know already, debugging a Powershell script executed via a scheduled task is no easy task. Error codes are not easy to get. Traces are not easy to get. All in all, this type of debugging is a real PITA.

What made this even more confusing is the script ran perfectly when run from the console. No problems! BTW, I have at least 40 of these tasks running each evening without error. After a few hours of putting in numerous traps and write event logs I finally found the reason it would only fail when run as a Windows Task. The real cause of the problem was the “Log on as” user for the scheduled task did not have access to the SQL Instance! My normal testing used MY Windows credentials which has complete access rights.

Say hello to my little friend, RUNAS!

When starting a Powershell session, the run-time system sets up the environment with your Windows credentials and uses them for access to all resources. In order to have an environment whereby you don’t have to login to Windows as another user, you can use the RUNAS command (in most circumstances). Using RUNAS, I was able to start a PowerGUI session in which my credentials were the same as those being used as the logon for the Windows Task. Now, I was able to thoroughly debug the script using breaks, traces, etc. I now have a desktop Icon which starts up PowerGUI as that user for whenever I need it. You can find out more about RUNAS here, and the command I am using looks similar to this:

C:\Windows\System32\runas.exe /user:Domain\Login "C:\Program Files (x86)\PowerGUI\ScriptEditor.exe"
Share

Convenient Way of Restarting Powershell at a Functional Step

If you happen to have a Powershell script which contains several or more major functional processes, you may find your self having to restart your process at a particular process. With scripting, it is usually relatively easy to “rip” out the steps you need to rerun after a crash and then just run those steps. In fact, you may even end up creating a new script for each functional process.

I have a multi-step process which must run daily. The entire process is very fragile and can experience problems in any of the steps it performs. I’ve been able to identify and circumvent many of the problems but it can still break in various sections. Because each functional process is long running, I need to be able to restart the process at any of the functional steps and continue from there.

The easiest way for me to accomplish this is by using a Param in conjunction with a ‘Switch’ statement. The Param identifies which function you want to start with, a Hashtable is used to convert the  identifier to a number, and a Switch statement wraps all the functions. Because the nature of a Switch statement is to continue testing values after finding the first one (unless you include a Break), you can effectively begin at a point and continue processing each function.

My example is seriously scaled down version but I believe it conveys the idea. The functional areas of my process are:

  1. Download – I have to download a file from a vendor FTP site
  2. Decrypt – the downloaded file needs to be decrypted
  3. Extract – the decrypted file is a Zip and I need to extract the contents
  4. Format – I have to reconstruct an entire Database using SQL files contained in the Zip extraction. The existing database is deleted, created, and structures added.
  5. Import – BCP is used to load the database tables with data from files in the Zip extract.

Here is the example

[CmdletBinding()]
Param(
	[Parameter(Position=0, Mandatory=$false)]
	[ValidateSet("Download","Decrypt","Extract","Format","Import")]
	[string]$StartAt = "Download"
)
#requires –Version 2.0
Set-StrictMode -Version "Latest"
# Equate our StartWith parameter with a hashtable
$step = @{Download = 1; Decrypt = 2; Extract = 3; Format = 4; Import = 5}

Switch($step[$StartAt]){
	1 { Write-Output "Download function will run" }
	{$_ -le 2} { Write-Output "Decrypt Function will run" }
	{$_ -le 3} { Write-Output "Extract Function will run" }
	{$_ -le 4} { Write-Output "Format Function will run" }
	{$_ -le 5} { Write-Output "Import Function will run" }
}
Share