SQL: Last backups for all databases

\ by 
-- Last backups for all databases 

SELECT 
    CAST(DATABASEPROPERTYEX([d].[name], 'Recovery') AS VARCHAR(10)) [RecoveryModel], 
    LEFT([d].[name], 50) [DatabaseName], 
    MAX([s].[backup_start_date]) [StartDate], 
    DATEDIFF(HOUR, MAX([s].[backup_start_date]), GETDATE()) [HoursAgo], 
    CASE [s].[type] 
    WHEN 'D' THEN 
        'Full' 
    WHEN 'I' THEN 
        'Differential' 
    WHEN 'L' THEN 
        'Log' 
    WHEN 'F' THEN 
        'File or Filegroup' 
    END [LastBackupType] 
FROM [master]..[sysdatabases] [d] 
LEFT OUTER JOIN [msdb]..[backupset] [s] 
    ON [d].[name] = [s].[database_name] 
JOIN ( 
    SELECT 
        [d].[name], 
        MAX([a].[backup_start_date]) [StartDate] 
    FROM [master]..[sysdatabases] [d] 
    LEFT OUTER JOIN [msdb]..[backupset] [a] 
        ON [d].[name] = [a].[database_name] 
    WHERE [d].[name] NOT IN ( 
              'tempdb', 'Northwind', 'pubs', 'model' 
          ) 
          AND [a].[type] = 'D' 
    GROUP BY [d].[name] 
) [x] 
    ON [x].[name] = [d].[name] 
WHERE [d].[name] NOT IN ( 
          'tempdb', 'Northwind', 'pubs', 'model' 
      ) 
GROUP BY [d].[name], 
         [s].[database_name], 
         DATABASEPROPERTYEX([d].[name], 'Recovery'), 
         [s].[type], 
         [x].[StartDate] 
HAVING MAX([s].[backup_start_date]) >= [StartDate] 
ORDER BY [d].[name], 
         MAX([s].[backup_start_date])
Posted in: SQL

SQL Agent: Use a batch file to run RoboCopy

\ by

It’s really hard to run RoboCopy in a SQL Agent command step. We want to use CmdExec so we can execute using a DOS proxy. But RoboCopy has a lot of weird exit values.

Easily handle the exit codes by calling a batch file. I’ve hard coded our options to make it easy and standard. 

@ECHO ON

IF {%3}=={} (
  @ECHO USAGE
  @ECHO RoboCopyFiles SourcePath DestinationPath Files
  @ECHO.
  GOTO FINISHED
) ELSE (

IF NOT {%4}=={} (
  (robocopy %1 %2 %3 /COPY:DAT /NP /R:0 /A+:A) 
) ELSE (
  (robocopy %1 %2 %3 /COPY:DAT /NP /R:0 /A+:A  /L) 
)

)
:FINISHED
IF %ERRORLEVEL% GEQ 8 exit /B %ERRORLEVEL%
exit /B 0
Posted in: Coding, DOS

SQL SMO Scripting Options broken in versions 2012-2016

\ by

Some notes about SSMS.

Here’s where the problem lies. If you are installing SQL 2012-2016 your Scripting Options may not work.

For SQL Server 2014 SSMS is included in the SQL install and SP.

The fix:

Even though you might only be installing SQL Server 2014 because SMO Options didn’t always work between versions 2012-2016 we need to install SQL Server 2017 SSMS 17.9.1.

e.g. What was broken was the SMO Scripting Option for
$Scripter.Options.NoFileGroup = $false
which is the default but even explicitly forcing it had no effect.

Simply adding SSMS 17.9.1 allowed correct scripting from the server.

https://docs.microsoft.com/en-us/sql/ssms/download-sql-server-management-studio-ssms?view=sql-server-2017

SSMS 17.9.1 is the current General Availability (GA) version of SSMS
1) download Download SQL Server Management Studio 17.9.1

2) download Download SQL Server Management Studio 17.9.1 Upgrade Package (upgrades 17.x to 17.9.1)

Version Information
Release number: 17.9.1
Build number: 14.0.17289.0
Release date: November 21, 2018

Posted in: Configuration, PowerShell, SQL Server

PowerShell: Get .NET PS and OS versions on all computers

\ by

Run this from an account which has access to all servers in the list.


# Get .NET Version and PowerShell Versions for all computers in AllComputersList.
#
$remoteCommand = @"
Get-ChildItem 'HKLM:\SOFTWARE\Microsoft\NET Framework Setup\NDP' -recurse |
	Get-ItemProperty -name Version,Release -EA 0 |
		Where { `$_.PSChildName -match '^(?!S)\p{L}'} | sort Version -Descending |
			Select-Object -first 1 -ExpandProperty Version
"@

$VersionReport = @()
$AllComputersList |%{
	$CurrentServer = $_
    $dotNetVersion = $(Invoke-Command  -Computername $CurrentServer -Scriptblock $ScriptBlock -ErrorAction SilentlyContinue)
    $PsVersion = $(Invoke-Command  -Computername $CurrentServer -Scriptblock {$PSVersionTable.psversion} -ErrorAction SilentlyContinue)
    $os = Get-WmiObject -class Win32_OperatingSystem -computername $CurrentServer
    $Versions = [pscustomobject]@{
        Computer = $CurrentServer
        NETVersion = $dotNetVersion
        PSVersion = $PsVersion
        OSVersion = $os.Version + "`t" + $os.Caption 
    }
    $VersionReport += $Versions

	Write-Host   "$(Get-Date -Format g)	[$CurrentServer] `t.NET Version $dotNetVersion `tPSVersion $PsVersion `t$($os.Version)"
}

$VersionReport | sort PSVersion,Computer |Out-GridView
Posted in: PowerShell, Reports

Powershell: Get all installed applications

\ by

Produce a nice output of our best guess at all the installed applications on a server or PC.

$ComputerName = 'SQLPROD'

 

# Get Software list for a 64-bit computer SOFTWARE
$remoteCommand = @"
Get-ItemProperty HKLM:\SOFTWARE\Microsoft\Windows\CurrentVersion\Uninstall\* 
"@

$scriptBlock = [Scriptblock]::Create($remoteCommand)
# Test 1
$Software1 = Invoke-Command  -Computername $ComputerName -Scriptblock $ScriptBlock

# Get Software list for a computer Wow6432Node
$remoteCommand = @"
Get-ItemProperty HKLM:\Software\Wow6432Node\Microsoft\Windows\CurrentVersion\Uninstall\* 
"@

$scriptBlock = [Scriptblock]::Create($remoteCommand)
# Test 2
$Software2 = Invoke-Command  -Computername $ComputerName -Scriptblock $ScriptBlock
 

$ComputerSoftwareList = [Pscustomobject]@()
if ($Software1) {
    $ComputerSoftwareList += $Software1 | Sort-Object DisplayName
    $ComputerSoftwareList.Count
}
if ($Software2) {
    $ComputerSoftwareList += $Software2 | Sort-Object DisplayName
    $ComputerSoftwareList.Count 
}
$ComputerSoftwareList.Count 

$OutGrid = $ComputerSoftwareList | 
    Sort-Object DisplayName |
        Select-Object DisplayName, DisplayVersion, Publisher, InstallDate -Unique  
        
$Global:seq = 1
$OutGrid | 
    Select-Object @{n=“Seq”; e={$Global:seq; $Global:seq++;}}, 
        @{n="Computer";e={"$ComputerName"}}, DisplayName, DisplayVersion, Publisher, InstallDate |
            Out-GridView
Posted in: PowerShell, Reports, Windows

REF: Compression and its Effects on Performance

\ by

I want to remember this study by Erin Stellato.

The summary:

If you’re in an OLTP system, you don’t want to return that many rows, so the tests here should give you an idea of how compression may affect performance. If you’re in a data warehouse, then you will probably see higher duration along with the higher CPU when returning large data sets. While the COMPRESS function provides significant space savings compared to page and row compression, the performance hit in terms of CPU, and the inability to index the compressed columns due to their data type, make it viable only for large volumes of data that will not be searched.

Posted in: Performance, SQL

REF: Page allocation changes in Sql Server 2016

\ by

While searching for information about MIXED_PAGE_ALLOCATION in SQL 2016+ and TF1118 I came across great examples about Page allocation changes in Sql Server 2016 here

Summary: For user databases in the versions of Sql Server older than 2016, the first 8 data pages were allocated from the mixed extent and next pages from uniform extent. In Sql Server 2016 even the first eight data pages were allocated from the uniform extent. Below image summaries this page allocation mechanism comparison between Sql Server 2016 and older versions of Sql Server

Posted in: Configuration, Documentation