Temporary Windows file

Sometimes it can be usefull to create a temporary Windows file, that is more general available than a personal temporary user file.
If you use the environmental variables „TEMP“ or „TMP“ you get your personal folder for temporary files, e.g. „C:\Users\Niels\AppData\Local\Temp“. Both variables gives the same folder name.

To get the path of the more general folder of temporary files „C:\Windows\Temp“ you can use the environmental variables „SystemRoot“ or „windir“ and add the name of the Temp-folder in PowerShell like
"$Env:SystemRoot\Temp"

To create a unique and identifiable temporary file I usually add a timestamp to the filename like
.$(("{0:s}Z" -f $([System.DateTime]::UtcNow)).Replace(':', '_')).
The Z is to indicate that the timestamp is UTC. That is timezone Z also called Zulu time.
The Replace method of the formatted String-object is used to get rid of the colons in the standard format. A colon is not acceptep by Windows in a file name.

The name of a temporary file can be created in a single PowerShell statement
$TempFile = "$Env:SystemRoot\Temp\_temp.$(("{0:s}Z" -f $([System.DateTime]::UtcNow)).Replace(':', '_')).ps1"

Writing to the temporary file can be done in PowerShell with the cmdlet Out-File or PowerShell redirection operators.
The first line can be written without any terms, but the following lines must be added to preserve the existing contents of the temporary file.
The first line, that also creates the temporary file, can be written in PowerShell like
'1st line' | Out-File -FilePath $TempFile
A second line can be added using the cmdlet Out-File like
'2nd line' | Out-File -FilePath $TempFile -Append
A third line can be added with the PowerShell appending redirection operator like
'3rd line' >> $TempFile

With the three examples above a temporary file is created
C:\Windows\Temp\_temp.2013-12-11T15_33_50Z.ps1
The contents of the temporary file is
1st line
2nd line
3rd line

This simple technique can be used to create other temporary Windows files like text files or data (csv) files.

xp_instance_regwrite syntax

The stored procedure "master.sys.xp_instance_regwrite" is not documented in SQL Server Books Online (BOL), but is usefull. In general the syntax is
EXECUTE [master].[sys].[xp_instance_regwrite]
  @rootkey = N'<name of root key>'
 ,@key = N'<key name>'
 ,@value_name = N'<key name value>'
 ,@type = N'<key type>'
 ,@value = N'<key value>';

For example the number of SQL Server Errorlog files can be updated by the statement
EXECUTE [master].[sys].[xp_instance_regwrite]
  @rootkey = N'HKEY_LOCAL_MACHINE'
 ,@key = N'Software\Microsoft\MSSQLServer\MSSQLServer'
 ,@value_name = N'NumErrorLogs'
 ,@type = N'REG_DWORD'
 ,@value = 42;

Use with caution!

The parameter @type takes a value without N'' around (@type = <key type>), but I like the Unicode framing. This way I try to avoid difficulties when building the values outside the call.

The type of the value for the parameter @value depends on the registry value type.
If the registry value type is a integer like REG_DWORD, the parameter value must be a integer.
 ,@value = 42;
If the registry value type is a string like REG_SZ, the parameter value must be a string.
 ,@value = N'John';

PowerShell usage

If the usage of this (undocumented) procedure should be included in a PowerShell script, it could be with ADO.NET in a function like this
function Write-MsSqlDbInstanceRegistry
{
  [CmdletBinding()]
  Param (
    # help description
    [Parameter(Mandatory=$true, Position=0)]
    [System.Data.SqlClient.SqlConnection]$MsSqlDbCnn,

    # Registry key path
    [Parameter(Mandatory=$true, Position=1)]
    [String]$RegKey,

    # Registry key name
    [Parameter(Mandatory=$true, Position=2)]
    [String]$RegName,

    # Registry key type
    [Parameter(Mandatory=$true, Position=3)]
    [String]$RegType,

    # Registry key value
    [Parameter(Mandatory=$true, Position=4)]
    [String]$RegValue
  )

  Begin {
    "{0:s}Z Write-MsSqlDbInstanceRegistry( '$RegKey', '$RegName', '$RegType', '$RegValue')" -f $([System.DateTime]::UtcNow) | Write-Verbose
  }

  Process {
    $MsSqlDbCmd = New-Object System.Data.SqlClient.SqlCommand
    $MsSqlDbCmd.Connection = $MsSqlDbCnn
    $MsSqlDbCmd.CommandText = '[master].[sys].[xp_instance_regwrite]'
    $MsSqlDbCmd.CommandType = [System.Data.CommandType]::StoredProcedure

    $MsSqlDbCmd.Parameters.Add("@rootkey", [System.Data.SqlDbType]::NVarChar, 128) | Out-Null
    $MsSqlDbCmd.Parameters['@rootkey'].Direction = [System.Data.ParameterDirection]::Input
    $MsSqlDbCmd.Parameters['@rootkey'].Value = 'HKEY_LOCAL_MACHINE'

    $MsSqlDbCmd.Parameters.Add("@key", [System.Data.SqlDbType]::NVarChar, 128) | Out-Null
    $MsSqlDbCmd.Parameters['@key'].Direction = [System.Data.ParameterDirection]::Input
    $MsSqlDbCmd.Parameters['@key'].Value = $RegKey

    $MsSqlDbCmd.Parameters.Add("@value_name", [System.Data.SqlDbType]::NVarChar, 128) | Out-Null
    $MsSqlDbCmd.Parameters['@value_name'].Direction = [System.Data.ParameterDirection]::Input
    $MsSqlDbCmd.Parameters['@value_name'].Value = $RegName

    $MsSqlDbCmd.Parameters.Add("@type", [System.Data.SqlDbType]::NVarChar, 128) | Out-Null
    $MsSqlDbCmd.Parameters['@type'].Direction = [System.Data.ParameterDirection]::Input
    $MsSqlDbCmd.Parameters['@type'].Value = $RegType

    switch -CaseSensitive ($RegType) {
      'REG_DWORD' {
        $MsSqlDbCmd.Parameters.Add("@value", [System.Data.SqlDbType]::Int) | Out-Null
        $MsSqlDbCmd.Parameters['@value'].Value = [Int]$RegValue
      }
      'REG_SZ' {
        $MsSqlDbCmd.Parameters.Add("@value", [System.Data.SqlDbType]::NVarChar, 128) | Out-Null
        $MsSqlDbCmd.Parameters['@value'].Value = $RegValue
      }
      default {
        "{0:s}Z Unknown Registry Value Type '$RegKeyType'." -f $([System.DateTime]::UtcNow) | Write-Error
        return
      }
    }
    $MsSqlDbCmd.Parameters['@value'].Direction = [System.Data.ParameterDirection]::Input

    $MsSqlDbCnn.Open()
    [Int]$RowCount = $MsSqlDbCmd.ExecuteNonQuery()
    $MsSqlDbCnn.Close()
  }

  End {}
}
The PowerShell function can be used like this
$Cnn = New-Object System.Data.SqlClient.SqlConnection
$Cnn.ConnectionString = 'Data Source=(local);Integrated Security=SSPI;Application Name=MsSqlDbInstanceRegistry'

Write-MsSqlDbInstanceRegistry `
  -MsSqlDbCnn $Cnn `
  -RegKey 'Software\Microsoft\MSSQLServer\MSSQLServer' `
  -RegName 'NumErrorLogs' `
  -RegType 'REG_DWORD' `
  -RegValue '42' `
  -Verbose

History

2010-09-15  First entry.
2013-09-09  PowerShell added.
2013-11-19  Registry Value Types handled correct.

Backup failed - nonrecoverable I/O

On a SQL 2008 SP2 (10.0.4000) I take database backup with a Maintenance Plan. This morning I had a failed database backup:
Executing the query "BACKUP DATABASE [thedatabase] TO  DISK = N'T:\\Backup\\th..." failed with the following error: "A nonrecoverable I/O error occurred on file "T:\\Backup\\thedatabase_backup_2011_01_18_221400_5137140.bak:" 112(failed to retrieve text for this error. Reason: 15105).
No nice :-(

When I looked in the log file generated by the Maintenance Plan, the error message was:
Task start: 2011-01-18T22:14:00.
Task end: 2011-01-18T22:14:01.
Failed:(-1073548784) Executing the query "BACKUP DATABASE [thedatabase] TO  DISK = N'T:\\Backup\\th..." failed with the following error: "A nonrecoverable I/O error occurred on file "T:\\Backup\\thedatabase_backup_2011_01_18_221400_5137140.bak:" 112(failed to retrieve text for this error. Reason: 15105).
BACKUP DATABASE is terminating abnormally.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

When I looked in the Windows System Log, there was no relevant entries.
But looking at the drive and the free space I saw that there was not space enough for the next backup file.
After a cleanup and manual execution of the Maintenance Plan everything was green.

The amount of free space can be looked up with PowerShell like this:
Get-WmiObject -Query "SELECT FreeSpace, Size FROM Win32_LogicalDisk WHERE DeviceID = 'T:'" -ComputerName SANDY.sqladmin.lan |
Format-Table @{Name='Freespace (GB)';Expression={"{0:N1}" -f($_.FreeSpace/1gb)}}, @{Name='Size (GB)';Expression={"{0:N1}" -f($_.Size/1gb)}} -AutoSize
The value of the parameter ComputerName should be changed to the actual databaseserver, also value in the WQL WHERE clause should be changed to the drive indicated in the error message.

The follow up is to order some additional storage.

I find the error message somewhat misleading. It looks like the ResultSet part is from the connectivity and not from the root error.

History

2011-01-19 : This is postede for the first time.
2013-08-22 : The PowerShell script is added.

SMO by SQLPS

With SQL Server 2012 SQLPS was changes from a mini-PowerShell to a PowerShell module. This makes is much simpler to import the functionality in a regular PowerShell script.
The examples below are written on a computer running Windows 8 and SQL Server 2012.
SQLPS is imported much simpler than we used to import SQL Server Management Objects SMO assemblies.
Import-Module 'SQLPS'
The parameter „DisableNameChecking“ can be added to avoid a message about unapproved verbs.
A list of exported Cmdlets in the module can be shown
(Get-Module 'SQLPS' -ListAvailable).ExportedCmdlets.Values.Name

Backup-SqlDatabase
Add-SqlAvailabilityDatabase
Disable-SqlAlwaysOn
Enable-SqlAlwaysOn
Switch-SqlAvailabilityGroup
Join-SqlAvailabilityGroup
New-SqlAvailabilityGroup
New-SqlAvailabilityReplica
New-SqlHADREndpoint
Remove-SqlAvailabilityGroup
Remove-SqlAvailabilityDatabase
Remove-SqlAvailabilityReplica
Resume-SqlAvailabilityDatabase
Set-SqlAvailabilityGroup
Set-SqlAvailabilityReplica
Set-SqlHADREndpoint
Suspend-SqlAvailabilityDatabase
Test-SqlAvailabilityGroup
Test-SqlAvailabilityReplica
Test-SqlDatabaseReplicaState
New-SqlAvailabilityGroupListener
Set-SqlAvailabilityGroupListener
Add-SqlAvailabilityGroupListenerStaticIp
Invoke-PolicyEvaluation
Restore-SqlDatabase
Invoke-Sqlcmd
Encode-SqlName
Decode-SqlName
Convert-UrnToPath

Server Object

To create a SMO Server object on a local default SQL Server database instance can be like
$SmoServer = New-Object Microsoft.SqlServer.Management.Smo.Server '(local)'
SMO only connects to the when you ask for something specific. For a start I would like to know the basics about the installation.
$SmoServer.Information

Parent                      : [TITANIUM]
Version                     : 11.0.3128
EngineEdition               : EnterpriseOrDeveloper
ResourceVersion             : 11.0.3000
BuildClrVersion             : 4.0.30319
BuildClrVersionString       : v4.0.30319
BuildNumber                 : 3128
Collation                   : Latin1_General_100_CI_AS_KS_WS_SC
...

The full set of Server property values can be accessed
$SmoServer | Format-List *

AuditLevel                  : Failure
BackupDirectory             : D:\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Backup
BrowserServiceAccount       : NT AUTHORITY\LOCALSERVICE
BrowserStartMode            : Disabled
BuildClrVersionString       : v4.0.30319
BuildNumber                 : 3128
...

Database Object

A SMO Database object can be created on a database with a known name
$SmoDatabase = $SmoServer.Databases['msdb']
This object can give access to some information
$SmoDatabase | Format-Table DataSpaceUsage,IndexSpaceUsage -AutoSize

DataSpaceUsage IndexSpaceUsage
-------------- ---------------
12080            3136

Table Object

Also the Database object can be used to create a SMO Table object. This can be direct on the table name alone
$SmoDatabase.Tables['sysjobhistory']
Or the Table object can be create more specific on the schema name with the table name
$SmoTable= ($SmoDatabase.Tables |
Where-Object -FilterScript { $PSItem.Schema -eq 'dbo' -and $PSItem.Name -eq 'sysjobhistory' })

Again the Table object gives access to some basic information
$SmoTable | Format-Table Schema,Name,DataSpaceUsed,IndexSpaceUsed,RowCount -AutoSize

Schema Name          DataSpaceUsed IndexSpaceUsed RowCount
------ ----          ------------- -------------- --------
dbo    sysjobhistory            32             32       84

Stored Procedure Object

The Database object can also be used to create a SMO Stored Procedure object. Like the Table object it can be created in two ways, but I will be (professional) lazy and only show the creation on the name alone
$SmoStoredProcedure = $SmoDatabase.StoredProcedures['sp_get_job_alerts']
A stored procedure usually has one or more parameters that can be described with
$SmoStoredProcedure.Parameters | Format-Table Name,DataType,DefaultValue,IsOutputParameter -AutoSize

Name      DataType         DefaultValue IsOutputParameter
----      --------         ------------ -----------------
@job_id   uniqueidentifier                          False
@job_name sysname                                   False

Server Job Object

With the installation of the SQL Server database service is also installed SQL Server Agent that is the SQL Server scheduler used for maintenance, monitoring and other automated tasks.
A SMO Job Server object can be created from a Server object property and gives a quick access to some basic SQL Server Agent information
$SmoServer.JobServer

AgentDomainGroup          : NT SERVICE\SQLSERVERAGENT
AgentLogLevel             : Errors, Warnings
AgentMailType             : SqlAgentMail
AgentShutdownWaitTime     : 15
DatabaseMailProfile       : 
ErrorLogFile              : D:\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Log\SQLAGENT.OUT
...

More interesting is the information om a job, where a SMO Server Job object can be created on the Server object
$SmoJob = $SmoServer.JobServer.Jobs['syspolicy_purge_history']
This gives access to some basic information on the job and its execution
$SmoJob | Format-Table CurrentRunStatus,LastRunDate,LastRunOutCome,NextRunDate -AutoSize

CurrentRunStatus LastRunDate         LastRunOutcome NextRunDate        
---------------- -----------         -------------- -----------        
            Idle 05-04-2013 02:00:00      Succeeded 21-05-2013 02:00:00

Reference

There are much more on SMO in MSDN Library, you can start with
„Microsoft.SqlServer.Management.Smo Namespace“
SMO is much larger which is indicated at
„SQL Server Management Objects Reference“, take a look at the index to the left.

Attending TechEd North America 2013