Silently Download and Install SQL Server Management Studio (SSMS)

SSMS is now free and no longer requiring licensing.
It is a separate install and has a more frequent release cycle, usually around 30 days.
To be able to maintain up to date a larger number of SSMS installations I wrote a PowerShell Script for the task.
Of course that a better approach will be to make the download once and then propagate it to other servers, but for now, this works best for me.

<#
.SYNOPSIS
    Silently Download and Install SQL Server Management Studio (SSMS).
.DESCRIPTION
    This script will download and install the latest available SSMS from Microsoft.
.PARAMETER WriteLog
    You want to log to a file. It will generate more than a few files :)
.PARAMETER UseProxy
    You can use a proxy to perform the download
.EXAMPLE
    .\Get-LastSSMS -UseProxy "192.168.1.1:8080" -WriteLog 1
.NOTES
    Author: Viorel Ciucu
    Date: January, 2017
.LINK
    http://www.cviorel.com/
#>
 
 
#Requires -Version 4.0
#Requires -RunAsAdministrator
 
[CmdletBinding()]
param (
	[parameter(Mandatory = $false)]
    [int]$WriteLog = 0,
    [parameter(Mandatory = $false)]
    [string]$UseProxy = ""
)
 
$ErrorActionPreference="SilentlyContinue"
 
if(-not $PSScriptRoot)
{
    $PSScriptRoot = Split-Path $MyInvocation.MyCommand.Path -Parent
}
 
$msg = ""
$args = @()
$args += "/install /quiet /norestart" 
 
if($WriteLog -eq 1) 
{ 
    $args += "/log SSMS_$(Get-Date -Format `"yyyyMMdd_HHmm`").txt"
	$msg = "InstallationLog: $PSScriptRoot\SSMS_$(Get-Date -Format `"yyyyMMdd_HHmm`").txt"
}
 
if($UseProxy -ne $null) { 
    Set-ItemProperty -path "HKCU:\Software\Microsoft\Windows\CurrentVersion\Internet Settings" ProxyEnable -value 1
    Set-ItemProperty -path "HKCU:\Software\Microsoft\Windows\CurrentVersion\Internet Settings" ProxyServer -value $UseProxy
    Set-ItemProperty -path "HKCU:\Software\Microsoft\Windows\CurrentVersion\Internet Settings" -Name ProxyOverride -Value "<local>"
}
 
# Start the download
$Domain = "https://msdn.microsoft.com/en-us/library/mt238290.aspx"
$url = ((Invoke-WebRequest -uri $Domain).Links | Where innerHTML -match "Download SQL Server Management Studio \(\d{2}\.\d{1}.\d{1}\)").href
$job = Start-BitsTransfer -Source $url -DisplayName SSMS -Destination "SSMS-Setup-ENU.exe" -Asynchronous
 
while (($Job.JobState -eq "Transferring") -or ($Job.JobState -eq "Connecting")) { 
	Start-Sleep 5;
} # Poll for status, sleep for 5 seconds, or perform an action.
 
Switch($Job.JobState) {
	"Transferred" {Complete-BitsTransfer -BitsJob $Job; Write-Host "Download completed!"}
	"Error" {$Job | Format-List } # List the errors.
	default {"Other action"} #  Perform corrective action.
}
 
# We close running SSMS processes
if (Get-Process 'Ssms') {
	Stop-Process -Name Ssms
}
 
# Install silently
Start-Process -FilePath SSMS-Setup-ENU.exe -ArgumentList $args -Wait -Verb RunAs
 
if($UseProxy -ne $null) { 
    # Disable Proxy
    Set-ItemProperty -path "HKCU:\Software\Microsoft\Windows\CurrentVersion\Internet Settings" ProxyEnable -value 0
    Remove-ItemProperty -path "HKCU:\Software\Microsoft\Windows\CurrentVersion\Internet Settings" -name ProxyServer
    netsh --% winhttp reset proxy
}
 
$msg
Write-Host "All done!"

Hope you’ll find it useful.

Add syntax color to crontab editor

You might be wondering why you don’t have syntax coloring when you are editing crontab entries.
This can be solved very simply by specifying your editor before starting crontab:

export EDITOR=vim

The change can be made persistent across the whole system if you add that line on the /etc/bashrc file.
You you only need this change for certain users only, add the line to their local ~/.bashrc files.

DTExec: The package execution returned DTSER_FAILURE (1)

What to do when a SQL backup job created with SQL server Maintenance Plans fails with this error?

DTExec: The package execution returned DTSER_FAILURE (1).

Investigate! No useful information in the logs.
Previously that day I dropped a few unneeded databases so maybe the Maintenance Plan was still referencing them. Everything looked good when the Maintenance Plan was opened in SSMS (it was configured to backup “SELECTED” databases), I could not find any reference to the deleted databases. Under the hood, the Maintenance Plan was still trying to backup those deleted databases because when I clicked OK and then hit Save, that Maintenance Plan was refreshed and everything worked just fine.

So always try to find the root cause.

T-SQL to generate backup and restore commands

Hi guys,

Since my day to day job is a DBA, I figured I will share stuff that others might find useful.
For the first time I will share a small script which will help you generate T-SQL commands in order to script the backup/restore process of your SQL Server.

This was tested on Microsoft SQL Server 2012 (SP1) – Express Edition (64-bit). Feel free to send your comments.

/*
 
This script will list all the databases on the server and generate backup and restore commands for each database, based on the recovery model.
The script will also determine if your SQL Server version supports compressed backups and will generate the commands accordingly.
 
(C) viorel.ciucu@gmail.com, 28 Aug 2014
 
*/
 
DECLARE @backup_location VARCHAR(254) = 'C:\TEMP\' -- full, UNC path
DECLARE @compresion VARCHAR(13) = ''
DECLARE @a nvarchar(max) = ''
 
DECLARE @CR AS CHAR(1)    -- Carriage Return (CR)
DECLARE @LF AS CHAR(1)    -- Line Feed (LF)
DECLARE @CrLf AS CHAR(2)  -- Carriage Return / Line Feed
 
SET @CR = CHAR(10)
SET @LF = CHAR(13)
SET @CrLf = @CR + @LF
 
IF (SERVERPROPERTY('EngineEdition') = 3)
	BEGIN
		PRINT 'Enterprise Edition detected, backup compression IS SUPPORTED!';
		SET @compresion = ' ,COMPRESSION';
	END
ELSE
	BEGIN
		PRINT 'Enterprise Edition NOT detected, backup compression IS NOT SUPPORTED!';
		SET @compresion = '';
	END
 
 
SELECT @@SERVERNAME as ServerName, database_id, name, --recovery_model, recovery_model_desc,
--CONCAT('ALTER DATABASE  [', name, '] SET READ_ONLY') AS read_only_command,
CONCAT('BACKUP DATABASE [', name, '] TO DISK = N''', @backup_location+name, '.bak''', ' WITH NOFORMAT, NOINIT', @compresion, ', STATS = 10;') +  @CR +
CASE WHEN recovery_model in (1, 2) -- the recovery model is either FULL or BULK_LOGGED
THEN
	CONCAT('BACKUP LOG  [', name, '] TO DISK = N''', @backup_location+name, '.trn'' WITH NOFORMAT, NOINIT, STATS = 10') + @LF
ELSE
	''
END AS backup_command, 
--CONCAT('ALTER DATABASE  [', name, '] SET  READ_WRITE WITH NO_WAIT') AS read_write_command,
CONCAT('RESTORE DATABASE [', name, '] FROM DISK = N''', @backup_location+name, '.bak''', ' WITH REPLACE, NORECOVERY;') +  @CR +
CASE WHEN recovery_model in (1, 2) -- the recovery model is either FULL or BULK_LOGGED
THEN
	CONCAT('RESTORE LOG [', name, '] FROM DISK = N''', @backup_location+name, '.trn''', ' WITH NORECOVERY;') + @LF +
	CONCAT('RESTORE DATABASE [', name, '] WITH RECOVERY;') + @LF
ELSE
	CONCAT('RESTORE DATABASE [', name, '] WITH RECOVERY;') + @LF
END AS restore_command
FROM sys.databases WITH (NOLOCK) WHERE database_id > 4
ORDER BY name

DriveDroid

DriveDroid is an Android application that allows you to boot your PC from ISO/IMG files stored on your phone.
Install it on your android phone and use it to boot any computer.
You can store any number of ISO/IMG files on your SD card.
Nice!

Disable recent documents in Gtk2/Gtk3

Use the following to disable recent documents in Gtk2/Gtk3:

echo ‘gtk-recent-files-max-age=0’ | tee -a $HOME/.gtkrc-2.0
echo ‘gtk-recent-files-max-age=0’ | tee -a $HOME/.config/gtk-3.0/settings.ini

The ugly part is that there is no way one can disable ‘Recently Used’ in the GtkFileChooser dialog box.

Quick way to convert Mbox mailboxes to Maildir format

A quick way to convert Mbox mailboxes to Mbox format […] Continue Reading…

Bash script to backup my configs (dotfiles)

I needed some script to take care of my config files and put them in a single directory. So here it is.

#!/bin/bash

# Display All Hidden Dot Files In a Directory
# ls -a | egrep “^\.” > backup_dotfiles.sh

DESTINATION=”/storage/dotfiles” # […] Continue Reading…

Moved to ArchLinux!

Moved my ThinkPad T61 to ArchLinux! (Still using Ubuntu on many other machines.)

Screenshot:

I will post some install script I used to configure my system.

Liferea custom stylesheet

I don’t like very much the default font used by Liferea, so I decided to use my custom font. Here is how:
1. Copy the default stylesheet to liferea folder in my $HOME:

cp /usr/share/liferea/css/liferea.css $HOME/.liferea_1.6

2. Add this:

{
font-family: Aurulent Sans;
}

at the […] Continue Reading…