Category Archives: Uncategorized

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

Easy Oracle Backup Deployment with Grid Control

I recently installed GRID Control and while “playing around” with it, I discovered the fantastic capability to build a job once and deploy over many targets. I thought this is very cool after experiencing so many problems with Dbconsole.

I found you can create a Job with minimal effort and deploy it to various targets with minimal effort. Database backups fit this mold exceptionally well. In my shop, all of our backups are rather routine and all share common properties: complete backup, daily at 6PM. All of our databases are small enough for complete backups rather than incremental.

Continue reading

Fix a SQL Server Login which has MUST_CHANGE set to ON

This is one of those nasty little thorns that sticks you every once in a while.

You add a new SQL Server authentication login with Server Manager and you forget to uncheck the boxes for User must change password, Enforce password, and Enforce Expiration. Being this is an application user for which you don’t want to enforce password restrictions and which uses ODBC, therefore never seeing a login dialog, they never get an opportunity to change the password. Consequently the login can’t be used as is.
image

Ok, you realize your mistake too late. The login has been created. It has been associated with one or more databases and/or schemas. Permissions have been granted, etc., etc., etc. How do you fix it? Guess what? If you try to just want to uncheck the “User must change” check box, you can’t! It is greyed out.

This conundrum perplexed me to no end when first encountered. Searching the internet turned up several solutions like this one:
ALTER LOGIN test_must_change WITH PASSWORD = ‘newpass’,
      CHECK_POLICY = OFF,
      CHECK_EXPIRATION = OFF;

Guess what happens (you have three choices and the first two don’t count). You get this error message
Msg 15128, Level 16, State 1, Line 1
The CHECK_POLICY and CHECK_EXPIRATION options cannot be turned OFF when MUST_CHANGE is ON.

If you try to user Server Manager and just uncheck the ‘Enforce password policy’ or ‘Enforce password expiration’ boxes, you get this

THE FIX

You MUST change the password first and it must be done without changing the other password policy options. You CAN change the password to be the same as the existing one. However, you do it, you still must effect a change on the password an then change the policy options as a separate step. If you choose to use the Server Manager wizard, when you change the password, the ‘User must change password’ check box will become available and you can then uncheck it (do not uncheck the other policy check boxes if they already were). You can then save the change, open the wizard again, uncheck the other policy restrictions, and save it again.

If you decide to use a query, you can use code similar to this
USE Master
GO
ALTER LOGIN test_must_change WITH PASSWORD = ‘samepassword’
GO
ALTER LOGIN test_must_change WITH
      CHECK_POLICY = OFF,
      CHECK_EXPIRATION = OFF;

eHarmony spurns Microsoft, finds match with Oracle 10g

eHarmony’s Vice President of Technology, Mark Douglas, cites SQL Server’s row locking mechanics as the biggest detractor. It appears this� was a major roadblock to scaling their application enough to accommodate their fast growth.� Find out more from the entire article here…

eHarmony spurns Microsoft, finds match with Oracle 10g