Tag Archives | powershell

Powershell to drop out create table scripts from a text file

So you’ve been handed a list of tables that need to be migrated

Stuart Moore

Stuart Moore

Nottingham based IT professional with over 15 years in the industry. Specialising in SQL Server, Infrastructure design, Disaster Recovery and Service Continuity management. Now happily probing the options the cloud provides

When not in front of a computer, is most likely to be found on the saddle of his bike

More Posts - Website

Follow Me:
TwitterLinkedInGoogle Plus

Stuart Moore

About Stuart Moore

Nottingham based IT professional with over 15 years in the industry. Specialising in SQL Server, Infrastructure design, Disaster Recovery and Service Continuity management. Now happily probing the options the cloud provides

When not in front of a computer, is most likely to be found on the saddle of his bike

List of SQL Server instances and version on Cluster Nodes

With a large windows cluster with many installed SQL Server instances it can get tricky to quickly check which instances are on which nodes, and a what patch level they’re running at. It’s also a good idea to check what revision level the instances think they’re on after a patching session.

In the grand tradition of lazyefficient DBAs I don’t want to be manually checking each node and instance for this information. So I use the following Powershell script:

Import-Module FailoverClusters
$Cluster=$args[0]
$clustnodes = Get-ClusterNode -Cluster $Cluster

foreach($node in $clustnodes){
echo "$node`n------------------"
	$reg = [Microsoft.Win32.RegistryKey]::OpenRemoteBaseKey('LocalMachine', $node)
	$regKey= $reg.OpenSubKey("SOFTWARE\\Microsoft\\Microsoft SQL Server\\Instance Names\\SQL")
	foreach($val in ($regkey.GetValueNames() | sort)){
		$regKey2= $reg.OpenSubKey("SOFTWARE\\Microsoft\\Microsoft SQL Server\\$val\\MSSQLServer\\CurrentVersion" )
		echo "$val `t`t $($regkey2.GetValue("CurrentVersion"))"
	}
echo "`n`n"
}

This can then be run as:

.\clust-version.ps1 my-clust

The returned results are in plain text, and need to be pairedup. But unless you’re a *LOT* of instances that’s the easy bit.

sql-01
------------------
INSTANCE1		10.50.1600.1
INSTAMCE2		10.0.2531.0
INSTANCE3		10.0.2531.0
INSTANCE4		10.50.1600.1

sql-02
------------------
INSTANCE1		10.50.1600.1
INSTAMCE2		10.0.2531.0
INSTANCE3		10.0.2531.0
INSTANCE4		10.50.1600.1

sql-03
------------------
INSTANCE1		10.50.1600.1
INSTAMCE2		10.0.2531.0
INSTANCE3		10.0.2531.0
INSTANCE4		10.50.1600.1
Stuart Moore

Stuart Moore

Nottingham based IT professional with over 15 years in the industry. Specialising in SQL Server, Infrastructure design, Disaster Recovery and Service Continuity management. Now happily probing the options the cloud provides

When not in front of a computer, is most likely to be found on the saddle of his bike

More Posts - Website

Follow Me:
TwitterLinkedInGoogle Plus

Stuart Moore

About Stuart Moore

Nottingham based IT professional with over 15 years in the industry. Specialising in SQL Server, Infrastructure design, Disaster Recovery and Service Continuity management. Now happily probing the options the cloud provides

When not in front of a computer, is most likely to be found on the saddle of his bike

Powershell script to build up restore commands for SQL Server

I often need to restore a standby server up to the latest production restore, either for testing or because I to get it ready for mirroring. As it’s on remote server there’s no easy list to click on in SQL Server Management Studio. So it’s time to look at the directory listing, and then play around with it in a text editor. With transaction logs every 5 minutes just getting close to completing the editing in time is a pain in the proverbial. So deciding to go the easy route (always my preference), here’s a powershell script that’ll build the statements for you:

$dbname=$args[0]
$folder=$args[1]
$start="restore database $dbname from disk='$folder"
$end="' with norecovery"

$holder = dir $folder *.bak
$tholder = dir $folder *.trn
"$start{0}$end" -f $holder[-1].name
$tholder | where {$_.lastwritetime -gt $holder[-1].lastwritetime} | foreach {"$start$_$end"}

Nothing too fancy there. I tend to keep more than a days worth of backups on disk if possible, so it looks in the specified folder for the oldest .bak file, and then lists all the .trn files created since then.

I normally just call it as:

./restore.ps1 ProdDB \\backupserver\backupshare$\proddb_backups > out.txt

If you wanted to be brave you could pipe the output into osql to avoid a cut ‘n’ paste, but I’m not usually that keen. For anything of an interesting size or backup frequency you’ll probably have to run it a couple of times to get up to date.

I’ve one in the works that talks to a SQL instance to get data file locations and the puts in the moves if needed. But it’s not quite ready for public eyes yet

Stuart Moore

Stuart Moore

Nottingham based IT professional with over 15 years in the industry. Specialising in SQL Server, Infrastructure design, Disaster Recovery and Service Continuity management. Now happily probing the options the cloud provides

When not in front of a computer, is most likely to be found on the saddle of his bike

More Posts - Website

Follow Me:
TwitterLinkedInGoogle Plus

Stuart Moore

About Stuart Moore

Nottingham based IT professional with over 15 years in the industry. Specialising in SQL Server, Infrastructure design, Disaster Recovery and Service Continuity management. Now happily probing the options the cloud provides

When not in front of a computer, is most likely to be found on the saddle of his bike