Skip to content

Instantly share code, notes, and snippets.

Avatar
🎉
powershell and chill

Chrissy LeMaire potatoqualitee

🎉
powershell and chill
View GitHub Profile
View sap_help_revlogin.sql
-- Downloaded from http://blogs.msdn.com/b/saponsqlserver
EXEC('IF OBJECT_ID (''sp_hexadecimal'') IS NOT NULL DROP PROCEDURE sp_hexadecimal')
EXEC('CREATE PROCEDURE sp_hexadecimal
@binvalue varbinary(256),
@hexvalue varchar (514) OUTPUT
AS
DECLARE @charvalue varchar (514)
DECLARE @i int
DECLARE @length int
@potatoqualitee
potatoqualitee / Restore-HallengrenBackups.ps1
Last active Aug 29, 2015
Restore-HallengrenBackups.ps1
View Restore-HallengrenBackups.ps1
<#
.SYNOPSIS
Restores SQL Server databases from the backup directory structure created by Ola Hallengren's database maintenance scripts.
.DESCRIPTION
Many SQL Server database administrators use Ola Hallengren's SQL Server Maintenance Solution which can be found at http://ola.hallengren.com
Hallengren uses a predictable backup structure which made it relatively easy to create a script that can restore an entire SQL Server database instance, down to the master database (next version), to a new server. This script is intended to be used in the event that the originating SQL Server becomes unavailable, thus rendering my other SQL restore script (http://goo.gl/QmfQ6s) ineffective.
.PARAMETER ServerName
Required. The SQL Server to which you will be restoring the databases.
View gist:7fbfbd0976bace497838
1. Took note at the exact version of SQL Server, down to the build number
2. Made backups of all databases, including the system databases
3. Stopped the SQL server role within the Failover Cluster Manager
4. Once the disks went offline within Windows, took a storage-level snapshot of the shared disks. I named this snap “ent version”.
5. Copied the system databases, and their logs (mdfs and ldfs) to backup directories just in case the snapshot didn’t work (one can never be too safe)
6. Used VMware to snapshot both Windows nodes (alternatively, you can shut them down and snap them at the storage level)
7. Uninstalled SQL Server from both nodes using the Remove Node from Cluster option. This will delete the system dbs, but not the user dbs.
8. Deleted templog.ldf from my system directory (it was the only one that the uninstall didn’t get rid of)
9. Reinstalled SQL Server Standard exactly as Enterprise was before on both nodes – down to the IP, service account, name, directory structure, filestream share etc.
10
View SQL Server ISO 3166.sql
CREATE TABLE countries (
code char(2) PRIMARY KEY NOT NULL,
name_en nvarchar(255),
name_fr nvarchar(255),
)
INSERT INTO countries VALUES ('AD','Andorra','Andorre'),('AE','United Arab Emirates','Émirats arabes unis'),('AF','Afghanistan','Afghanistan'),('AG','Antigua and Barbuda','Antigua-et-Barbuda'),('AI','Anguilla','Anguilla'),('AL','Albania','Albanie'),('AM','Armenia','Arménie'),('AO','Angola','Angola'),('AQ','Antarctica','Antarctique'),('AR','Argentina','Argentine'),('AS','American Samoa','Samoa américaine'),('AT','Austria','Autriche'),('AU','Australia','Australie'),('AW','Aruba','Aruba'),('AX','Åland Islands','Îles d''Åland'),('AZ','Azerbaijan','Azerbaïdjan'),('BA','Bosnia and Herzegovina','Bosnie-Herzégovine'),('BB','Barbados','Barbade'),('BD','Bangladesh','Bangladesh'),('BE','Belgium','Belgique'),('BF','Burkina Faso','Burkina Faso'),('BG','Bulgaria','Bulgarie'),('BH','Bahrain','Bahreïn'),('BI','Burundi','Burundi'),('BJ','Benin','Bénin'),('BL','Saint Barthélemy','Saint-Barthélemy'),('BM','Bermud
View csvtodt.csv
We can make this file beautiful and searchable if this error is corrected: No commas found in this CSV file in line 0.
$csvfile = "C:\perf\bigperf.csv"
$csvfile = "C:\perf\tab.csv"
$csvdelimiter = "`t" # `t for tab
$firstrowcolumnnames = $false
Write-Output "Script started..."
[void][Reflection.Assembly]::LoadWithPartialName("Microsoft.VisualBasic")
$elapsed = [System.Diagnostics.Stopwatch]::StartNew()
$csv = New-Object Microsoft.VisualBasic.FileIO.TextFieldParser($csvfile)
View Get-LocateLite.ps1
$GetDll = $true
if ($PSScriptRoot) { $location = $PSScriptRoot } else { $location = (Get-Location).Path }
$dll = "$location\System.Data.SQLite.dll"
if ($GetDll) {
$wc = New-Object System.Net.WebClient
$url = "https://netnerds.net/System.Data.SQLite.dll"
$wc.DownloadFile($url,$dll)
}
View Join-AdminUNC.ps1
Function Join-AdminUNC {
<#
.SYNOPSIS
Parses a path to make it an admin UNC.
.EXAMPLE
Join-AdminUNC sqlserver C:\windows\system32
Output: \\sqlserver\c$\windows\system32
.OUTPUTS
@potatoqualitee
potatoqualitee / New-TextIcon.ps1
Created Sep 14, 2015
Creates Text Icon. Useful for NotifyIcons.
View New-TextIcon.ps1
Function New-TextIcon {
[CmdletBinding()]
Param(
[Parameter(Mandatory=$true)]
[string]$Text,
[string]$Path,
[int]$Height = 16,
[int]$Width = 16,
[int]$FontSize = 9
@potatoqualitee
potatoqualitee / Show-SystemColors.ps1
Last active Sep 16, 2015
PowerShell function Show-SystemColor to get GUI list of System.Windows.SystemColors
View Show-SystemColors.ps1
Function Show-SystemColors {
<#
.SYNOPSIS
Shows a visual representation (grid with color block, name, and hex code)) of system colors.
.NOTES
Author : Chrissy LeMaire
Requires: PowerShell 3.0
Version: 1.0
DateUpdated: 2015-Sep-16
@potatoqualitee
potatoqualitee / Show-WPFExample.ps1
Created Nov 27, 2015
WPF + PowerShell + SQL super simple example
View Show-WPFExample.ps1
# Load WPF Assemblies
Add-Type -AssemblyName PresentationFramework
# Get the info you need from SQL Server
$sqlserver = "sqlserver2014a"
$query = "select name from master.dbo.sysdatabases order by name"
$conn = New-Object System.Data.SqlClient.SqlConnection
$conn.ConnectionString = "Data Source=$sqlserver;Integrated Security=True;Connection Timeout=3"
$conn.Open()