Uncategorized

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  from Dev to Staging ASAP. So powershell to the rescue for a nice quick solution.

First up I’m assuming you’ve got a nice list of table names like:

vcstr
numbers

Stored in a file somewhere (assuming c:\tables.txt for this example). Now for the PowerShell script:

Import-Module "SQLPS" -DisableNameChecking

$sqlsrv = New-Object Microsoft.SqlServer.Management.Smo.Server($args[0])
$db = New-Object Microsoft.SqlServer.Management.Smo.Database
$db = $sqlsrv.Databases.Item($args[1])
$scrptr = New-Object Microsoft.SqlServer.Management.Smo.Scripter($sqlsrv)
$scrptr.Options.includeifnotexists = $TRUE
$scrptr.Options.WithDependencies = $TRUE
$scrptr.Options.Indexes = $true

$tbllist = get-content c:\tables.txt

foreach ($tbl in $tbllist)
{
$tbobj = New-Object Microsoft.SqlServer.Management.Smo.Table($db, $tbl)

      $sc = $scrptr.Script($tbobj.urn)
      foreach ($st in $sc)
      {
        $st >> C:\scripts\create_tables.sql
      }
}

This is designed to be saved as tables.ps1 and called as follows:

 c:\scripts\tables.ps1 "spiggy\sql2012" "resourcedb"

And this will churn you out a nice set of create table statements, with the indexes as well:

SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[vcstr]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[vcstr](
	[int_id] [int] NULL,
	[int_1] [int] NULL,
	[int_2] [int] NULL,
	[txt] [varchar](250) COLLATE Latin1_General_CI_AS NULL
) ON [PRIMARY]
END
SET ANSI_PADDING ON

IF NOT EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[vcstr]') AND name = N'ix_vcstr')
CREATE CLUSTERED INDEX [ix_vcstr] ON [dbo].[vcstr]
(
	[int_1] ASC,
	[int_2] ASC,
	[txt] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[numbers]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[numbers](
	[number] [int] NULL
) ON [PRIMARY]
END

The $scrptr.Options list is rather huge microsoft.sqlserver.management.smo.scriptingoptions, but should offer enough scope to cover everything you might want to include in your scripts

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

SQL Server, SFTP and SCP

A common requirement for SQL Server DBAs is sending or fetching data from remote unix/linux systems. And quite rightly the customers want their data transmitting securely.  For these cases SFTP (Secure File Transfer Protocol) or SCP (Secure Copy Protocol) are the methods you should be considering.

The protocols (and the tools that use them) were built from the ground up to ensure ALL data and information is transmitted securely and encrypted to the expected destination. When try to automate these tools from within SQL Sever, it’s normally that 3rd requirement that trips people up.

When a connection is made, the tool will ask the user to confirm that the server they are connecting to is the correct one, for example:

C:\winscp lfnode@example.server:/home/stuart/test.txt ./
Searching for host...
Connecting to host...
Authenticating...
The server's host key was not found in the cache. You have no
guarantee that the server is the computer you think it is.
The server's rsa2 key fingerprint is:
ssh-rsa 2048 9c:bf:c5:34:59:d9:48:4c:d3:87:70:90:aa:8c:a7:bc
If you trust this host, press Yes. To connect without adding 
host key to the cache, press No. To abandon the connection 
press Cancel.
Continue connecting and add host key to the cache?
(Y)es, (N)o, C(a)ncel, (C)opy Key:

The reason for this is that the fingerprint is an condensed representation of the server’s private key, which uniquely identifies the server at the other end of the connection. This means that once you’ve set this up, man in the middle attack becomes a very tricky proposition. Really security conscious DBAs will have want to have that server fingerprint confirmed by a trusted source (ie; via phone from a trusted colleague, or via PGP signed/encrypted email) to really ensure they’ve got the right one.

The problem with SQL Server using this comes from the fact that every user has their own ‘cache’,  again as a security precaution to prevent Bob from tricking Colin into sending the sensitive files to the wrong server. So this all works fine in testing with interactive accounts, but once you drop it into a SQL Agent job it fails to work.

There are a number of workarounds.

Simplest, if you’re using an interactive user account to run SQL Server Agent, then log in to the Server, run the command line and accept the fingerprint. All nice and simple. But it means that it may not be obvious to following DBAs what they should do if the remote server changes, and they need to change the fingerprint. It can also cause problems on clustered installs, where the fingerprint needs to be cached for the Agent user on each node that can host the instance. And in a DR situation you need to ensure it’s also setup on your failover site.

Some clients will allow you to pass in the server fingerprint as part of the command line. For example in WinSCP (my preferred client for this sort of work):

C:\winscp lfnode@example.server:/home/stuart/test.txt ./ /hostkey="ssh-rsa 2048 9c:bf:c5:34:59:d9:48:4c:d3:87:70:90:aa:8c:a7:bc"

When WinSCP sees this parameter it doesn’t check the cache, and as long as the server presents the same fingerprint it won’t prompt and your connection will go through. The benefit of this, is that the hostkey can be stored within the database. So for a cluster instance it’ll be available on every node. And once you’ve recovered from your disaster you’ll have the information to hand as normal

This always seems to be the most common problem I see people having with setting up a SCP and SFTP under SQL Server agent. Once this hurdle is surmounted it’s all pretty simple. Though that’s not to say you won’t see a lot of passwords kicking around in plain text, which is the next most common issue I see with secure connection attempts. I’ll cover techniques for hiding those in a later post.

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

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

End of working 2011

Well, just about to wrap up working for 2011. For the first time in years I’ve actually got a completely clear christmas break with no planned work. And hopefully the last year’s work will help ensure no unplanned work callouts either.

Looking forward to 2012; I’ve just sorted my registration for all 3 days of SQLBits in London at the end of March, and it’s looking likely that I’ve scored some Dynamics CRM 2011 training off the back of a project.

Sort of dreading having to look at rebuying my ‘work’ library next year as tle SQL 2012 books start hitting the shelves. There is a temptation to look at an E-reader to save on space/cost, but the Kindle has never really impressed, and initial reports on the Kindle Fire for technical documents aren’t promising, this could finally push me over the edge in iPad ownership.

One of the last jobs for this year was setting up mirroring for an application between 2 geo-clustered SQL installs. Really looking forward to the AlwaysOn availability groups coming in SQL Server 2012 as it looks like it will remove a lot of the pain and overhead involved in these sort of scenarios, (In fact I’ve picked this session “AlwaysOn: Maximizing High Availability with SQL Server 2012” by Allan Hirt for my Thursday session at SQLBits.