Personal Weblog of John Wood

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

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 ‘Restore All SQL Transaction Logs using Powershell’ »

иконографияиконикухненски маси

Some quick methods of obtaining a midnight DateTime value for almost any date.

Clear-Host
# Get Midnight for current date
Get-Date -Hour 0 -Minute 00 -Second 00
# Another way
[datetime]::Today

# Get Midnight for date a week ago
(Get-Date -Hour 0 -Minute 00 -Second 00).AddDays(-7)
# Or use this
([datetime]::Today).AddDays(-7)

# Get Midnight for date a month ago
Get-Date -Month $((Get-Date -Format "MM") - 1) -Hour 0 -Minute 0 -Second 0

# Get Midnight for a date one year ago
Get-Date -Year $((Get-Date -Format "yyyy") - 1) -Hour 0 -Minute 0 -Second 0

Beware of a new default when installing Oracle 11g! This default can raise its ugly head and bite you in the ass! It just happened to me.

The default has to do with enforcing a password expiration when creating a user. I don’t know about you but we generally have applications (Oracle based as well as SQL Server) which rely on user logins specific for that application. These applications also have configurations in which you specify the user login, sometimes in several locations. Users and application managers come and go and along with them the knowledge of application configurations and where they are. Consequently, we do not change application passwords! As soon as you do, applications break and it could take hours or even days to get them working again.

With this in mind, you might seriously think about changing the Oracle 11g default policy as one of your steps immediately after installation. I did not change the policy as I was unaware of it. I had several user logins become disabled duet to expiring passwords. These also included SYSTEM and DBSNMP! Talk about a real PITA!

Here’s how you can change the password policy:

ALTER PROFILE DEFAULT LIMIT
    FAILED_LOGIN_ATTEMPTS 10
    PASSWORD_LIFE_TIME UNLIMITED;

Oh! And by-the-way, if the password does expire, you cannot unexpire it. You are forced to supply a new password even if it is the same as the old one. Unfortunately in my case, the application was installed by a third party and they were not sure of the password.

I don’t often have to restore a database. However, while having to restore a few dozen to set up database mirroring, I came across a few with multiple database files. My first ‘mirroring’ script did not account for this. I quickly wrote a new restore script to accommodate the possibility of multiple files.

<#
     .SYNOPSIS
          Restore to a NEW Database
     .DESCRIPTION
          Restores an SQL Server backup file to a new database.
	   1) Uses existing Logical and physical file names and restores to the
	      SQL server default file locations.
	   2) Restores multiple files.
	   3) Can specify NoRecover (necessary for mirrored database)
     .PARAMETER  file
          Full path and file name for the backup file. Must be local
     .PARAMETER  Server
          The name\instance of the SQL Server.
     .PARAMETER  database
          The name of the database to be restored
     .EXAMPLE
          PS C:\> Invoke-SqlRestore -file 'D:\Backups\mydb.bak' `
		  	-server 'MyServr\SQLinstance' -database NEWDB
     .NOTES
         AUTHOR:    John P. Wood
         CREATED:   July, 2010
         VERSION:   1.0.3
     .LINK

http://www.webofwood.com

#>
Param(
	[string]$file,
	[string]$server,
	[string]$database,
	[switch]$norecovery=$true
	)
[Void][System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.ConnectionInfo")
[Void][System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMO")
[Void][System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SmoExtended")
Function New-SMOconnection {
    Param ([string]$server)
	$conn = New-Object Microsoft.SqlServer.Management.Common.ServerConnection($server)
	$conn.applicationName = "PowerShell SMO"
	$conn.StatementTimeout = 0
	$conn.Connect()
	if ($conn.IsOpen -eq $false) {
		Throw "Could not connect to server $($server)."
	}
	$smo = New-Object Microsoft.SqlServer.Management.Smo.Server($conn)
	$smo
}
Function Get-FileName {
	Param([string]$path)
	$names = $path.Split('\\')
	$names[$names.Count - 1]
}
Function Invoke-SqlRestore {
	Param(
		[string]$backupFile,
		[Microsoft.SqlServer.Management.Smo.Server]$smo
	)
	$backupDevice = New-Object("Microsoft.SqlServer.Management.Smo.BackupDeviceItem") `
		($backupFile, "File")
	# Get local paths to the Database and Log file locations
	If ($smo.Settings.DefaultFile.Length -eq 0) {$DBPath = $smo.Information.MasterDBPath }
	Else { $DBPath = $smo.Settings.DefaultFile}
	If ($smo.Settings.DefaultLog.Length -eq 0 ) {$DBLogPath = $smo.Information.MasterDBLogPath }
	Else { $DBLogPath = $smo.Settings.DefaultLog}

	# Load up the Restore object settings
	$Restore = new-object Microsoft.SqlServer.Management.Smo.Restore
	$Restore.Action = 'Database'
	$Restore.Database = $database
    $Restore.ReplaceDatabase = $true
	if ($norecovery.IsPresent) { $Restore.NoRecovery = $true }
	Else { $Restore.Norecovery = $false }
	$Restore.Devices.Add($backupDevice)

	# Get information from the backup file
	$RestoreDetails = $Restore.ReadBackupHeader($smo)
	$DataFiles = $Restore.ReadFileList($smo)

	# Restore all backup files
	ForEach ($DataRow in $DataFiles) {
        $LogicalName = $DataRow.LogicalName
        $PhysicalName = Get-FileName -path $DataRow.PhysicalName
		$RestoreData = New-Object("Microsoft.SqlServer.Management.Smo.RelocateFile")
		$RestoreData.LogicalFileName = $LogicalName
		if ($DataRow.Type -eq "D") {
			# Restore Data file
			$RestoreData.PhysicalFileName = $DBPath + "\" + $PhysicalName
		}
		Else {
			# Restore Log file
			$RestoreData.PhysicalFileName = $DBLogPath + "\" + $PhysicalName
		}
		[Void]$Restore.RelocateFiles.Add($RestoreData)

	}
	$Restore.SqlRestore($smo)
	# If there are two files, assume the next is a Log
	if ($RestoreDetails.Rows.Count -gt 1) {
		$Restore.Action = [Microsoft.SqlServer.Management.Smo.RestoreActionType]::Log
		$Restore.FileNumber = 2
		$Restore.SqlRestore($smo)
	}
}
Clear-Host
# Get a new connection to the server
$smo = New-SMOconnection -server $server
Write-Host "Starting restore to New Database $database on $server."
Try {
	Invoke-SqlRestore -backupFile $file -smo $smo
	}
Catch {
	$ex = $_.Exception
	Write-Output $ex.message
	$ex = $ex.InnerException
	while ($ex.InnerException) {
		Write-Output $ex.InnerException.message
		$ex = $ex.InnerException
	}
	Throw $ex
}
Finally {
	$smo.ConnectionContext.Disconnect()
}
Write-Host "Restore ended without any errors."

	 1) Uses existing Logical and physical file names and restores to the
	    SQL server default file locations.
	 2) Restores multiple files.
	 3) Can specify NoRecover (necessary for mirrored database)