As previously stated here the Blog, I spend quite some time doing automation of SQL Server maintenance.
One challenge has been to detect if a given SQL Server was currently locking a specific device file (backup file). To solve this problem I came up with this small function:
function TestFileLock {
## Attempts to open a file and trap the resulting error if the file is already open/locked
param ([string]$filePath )
$filelocked = $false
$fileInfo = New-Object System.IO.FileInfo $filePath
trap {
Set-Variable -name locked -value $true -scope 1
continue
}
$fileStream = $fileInfo.Open( [System.IO.FileMode]::OpenOrCreate, [System.IO.FileAccess]::ReadWrite, [System.IO.FileShare]::None )
if ($fileStream) {
$fileStream.Close()
}
$obj = New-Object Object
$obj | Add-Member Noteproperty FilePath -value $filePath
$obj | Add-Member Noteproperty IsLocked -value $filelocked
$obj
}
Usage:
PS> TestFileLock "c:\pagefile.sys"
PS> TestFileLock "\\Server01\backup\MyInstance05\devDATABASE_00A1.bak"
Wednesday, July 16, 2008
Subscribe to:
Post Comments (Atom)
3 comments:
A more common way of doing a test is to have the cmdlet/script return either true or false. For an example of that see Test-Path. If you were to return the same type of output, it would be more intuitive.
I agree that a simple true/false might be more intuitive, however the way the function is currently written it is easier to use it against a whole range of files and get a list of Filenames & IsLocked status back.
Fix :
Set-Variable -name Filelocked -value $true -scope 1
Post a Comment