Generate DB script using PowerShell

This is an example script that can be used to dump a SQL Server database schema into a creation script. The advantages of this method over using SQL Server Management Studio include:

• Scriptable. Can be included in a build or commit script.
• Repeatable. SQL Server Management Studio generated scripts vary from run to run, so it can be difficult to make comparisons with previous versions of the schema in source control.
• Repeatable. Using the SQL Server Management Studio GUI to create scripts relies on everyone ticking the same boxes to generate consistent and correct DB scripts.
• Configurable. This script ignores objects (e.g. tables and triggers) with names starting with 'test'.
  

<#
.SYNOPSIS
.NOTES
Author: 
#>

$root = resolve-path ((split-path -path $MyInvocation.MyCommand.Definition -Parent) + '\..\..');
$serverURL = 'SQL2005'
$databaseName = "DBToDump";
$scriptFile = "$root\Scripts\Create.sql"

#================== GetObjectsFromDB ===================
function GetObjectsFromDB(
[parameter(mandatory=$true)]$Database, 
[parameter(mandatory=$true)][string]$ObjectType)
{
    $objects = [Microsoft.SqlServer.Management.Smo.SqlSmoObject[]]($database.$ObjectType | Where-object {$_.IsSystemObject -ne $true -and $_.IsFixedRole -ne $true -and $_.Name -notmatch '^test' } );
    if($objects)
    {
        ,$objects
    }
    else
    {
        ,@()
    }
}
#=======================================================

[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMO") | out-null
$server = new-object Microsoft.SqlServer.Management.Smo.Server ($serverURL);
$server.SetDefaultInitFields([Microsoft.SqlServer.Management.SMO.StoredProcedure], "IsSystemObject")
$db = $server.Databases[$databaseName];

if(Test-Path $scriptFile)
{
    del $scriptFile
}

$scripter = new-object Microsoft.SqlServer.Management.Smo.Scripter ($server);
$options = $scripter.Options
$options.ScriptDrops = $false
$options.IncludeIfNotExists = $false
$options.ScriptData = $false
$options.NoCommandTerminator = $false
$options.FileName = $scriptFile
$options.SchemaQualify = $true
$options.ToFileOnly = $true
$options.AppendToFile = $true
$options.DriAllConstraints = $true
$options.Indexes = $true
$options.ClusteredIndexes = $true
$options.Statistics = $true
$options.IncludeDatabaseRoleMemberships = $true
$options.TargetServerVersion = [Microsoft.SqlServer.Management.Smo.SqlServerVersion]::Version90
$options.NoFileGroup = $true
$options.DriAll = $true

echo "Scripting database..."
$scripter.EnumScript($db)

#don't script collation for DB objects since it has been done for the database as a whole
$options.NoCollation = $true

#switch to newly created DB
"
USE [$databaseName]
GO
" | out-file -append $scriptFile -ErrorAction stop

$objects = GetObjectsFromDB $db "Schemas"
$objects += GetObjectsFromDB $db "Roles"
$objects += GetObjectsFromDB $db "Tables"
$objects += GetObjectsFromDB $db "UserDefinedFunctions"
$objects += GetObjectsFromDB $db "StoredProcedures"
$objects += GetObjectsFromDB $db "Views"

$scripter.EnumScript($objects)

echo "Done."

#set permissions (not sure how to script the ones I want without including test permissions)
"
GRANT EXECUTE ON SCHEMA::[app] TO [application] AS [dbo]
GO
GRANT SELECT ON SCHEMA::[cfg] TO [application] AS [dbo]
GO
GRANT SELECT ON SCHEMA::[cfg] TO [support] AS [dbo]
GO
GRANT SELECT ON SCHEMA::[dbo] TO [support] AS [dbo]
GO
GRANT UPDATE ON SCHEMA::[cfg] TO [support] AS [dbo]
GO
GRANT SELECT ON SCHEMA::[sup] TO [support] AS [dbo]
GO
GRANT VIEW DEFINITION ON SCHEMA::[sup] TO [support] AS [dbo]
GO
GRANT VIEW DEFINITION ON SCHEMA::[cfg] TO [support] AS [dbo]
GO
GRANT VIEW DEFINITION ON SCHEMA::[dbo] TO [support] AS [dbo]
GO
GRANT EXECUTE ON SCHEMA::[sup] TO [support] AS [dbo]
GO" | out-file -append $scriptFile -ErrorAction stop

Windows Software I can't live without

• Secunia PSI
• Sysinternals Suite (and the live version)
• ILSpy
• 7-Zip
• Fiddler
• Wireshark
• KeePass
• Subversion
• TortoiseSVN
• mdbg (.NET 3.5 and earlier), and mdbg (.NET 4.0)
• Virtual CloneDrive

Honourable mention:

• Microsoft Security Essentials
• Microsoft Baseline Security Analyzer
• Putty
• Speccy
• Truecrypt
• µTorrent
• XVI32
• Dependency Walker
• Managed Stack Explorer
• PerfView
• Remote Desktop Connection Manager
• Foxit PDF Reader
• PowerShell Community Extensions
• File Checksum Integrity Verifier (fciv)
• Windows System State Analyser x64 or x86

Visual Studio 2012:

• Visual Studio 2012 Update 1 (should be downloaded automatically from within VS 2012)
• ASP.NET and Web Tools 2012.2
• ReSharper 7.1, if installed using these steps:
  
  1. Set VS keyboard shortcuts to C# and reset to defaults.
  2. Set R# keyboard shortcuts to Visual Studio style.
  3. Set The following R# options:
     • Code Editing -> C# -> Formatting Style -> Line Breaks and Wrapping -> Line Wrapping -> Wrap long lines: untick
     • Code Editing -> C# -> Formatting Style -> Other -> Modifiers -> Use explicit private modifier: untick
     • Code Editing -> C# -> Formatting Style -> Other -> Modifiers -> Use explicit internal modifier: untick
     • Code Editing -> C# -> Formatting Style -> Other -> Align Multiline Constructs -> Array, object and collection initializer: untick
     • Tools -> External Sources -> Navigation to Sources: tick all boxes
  4. Run the following code in the Package Manager Console:

     ($DTE.Commands | ? { $_.Name -eq 'ReSharper.ReSharper_DuplicateText' }).Bindings = @()
     ($DTE.Commands | ? { $_.Name -eq 'Edit.PasteParameterTip' }).Bindings = @()
     ($DTE.Commands | ? { $_.Name -eq 'ReSharper.ReSharper_IntroduceField' }).Bindings = @()
     

SSH client for .NET

I have been looking for a good, free, .NET SSH client. This SSH client seems to fit the bill.

I did, however, run into a problem where it appears that the state of the session is not preserved between calls. I will follow this up on the Codeplex page.

The following test fails:

[TestMethod]
public void ShouldMaintainSessionStateAcrossCommands()
{
    using (SshClient client = new SshClient("sssssss", "uuuuuuu", "ppppppp"))
    {
        client.Connect();

        client.RunCommand("cd /tmp");
        string result = client.RunCommand("pwd").Result;

        Assert.AreEqual("/tmp", result);
    }
}

EDIT: This is the issue I logged and the developer's response. It seems that this behaviour is "by design", which unfortunately means that there will not be a 'fix'.

The workaround would be to run all dependent commands separated by semicolons, although this is quite limiting in some situations.

Uploading a file using FTP and the WebClient

Note: more detailed documentation for the .NET 4.0 explains the reason for requiring the additional slash (/). See the remarks section for the FtpWebRequest class. If you are using .NET 4.0, this class is probably a better bet.

I ran into some trouble uploading a file using the System.Net.WebClient. Looking at examples in the web, it seemed quite straightforward, but I found that I needed to include an additional forward slash in order to get the transfer to work correctly.

At first, I struggled to upload a file with this call to my WebClient instance:

client.UploadFile("ftp://sssssss/folder/folder/folder/t.txt", @"c:\temp\t.txt");

I was able to download a file in that location with Internet Explorer using the URI above, but not upload to it using the WebClient.

This dump from Wireshark illustrates the problem (note line 14), which seems to be a bug in the WebClient to me:

220 sssssss FTP server (Version 1.1.214.4(PHNE_38458) Tue Jul 29 07:36:52 GMT 2008) ready.
USER uuuuuuu
331 Password required for uuuuuuu.
PASS ppppppp
230 User uuuuuuulogged in.
OPTS utf8 on
500 'OPTS utf8 on': command not understood.
PWD
257 "/home/fld/uuuuuuu" is current directory.
TYPE I
200 Type set to I.
PASV
227 Entering Passive Mode (xx,xx,xx,xx,xx,xx)
STOR folder/folder/folder/t.txt
553 Could not determine cwdir: No such file or directory.
221 You could at least say goodbye.

Noticing that the STOR command was providing a relative path (not starting with /), I assume that the server was trying to save the file into the current folder i.e. /home/fld/uuuuuuu/folder/folder/folder/t.txt. To test this theory, I added a slash at the beginning ot the path:

client.UploadFile("ftp://sssssss//folder/folder/folder/t.txt", @"c:\temp\t.txt");

I am not sure if this is a valid URI, but it seems to work with the WebClient, although I am not sure why the RETR command (line 19) is executed as in the following Wireshark dump:

220 sssssss FTP server (Version 1.1.214.4(PHNE_38458) Tue Jul 29 07:36:52 GMT 2008) ready.
USER uuuuuuu
331 Password required for uuuuuuu.
PASS ppppppp
230 User uuuuuuu logged in.
OPTS utf8 on
500 'OPTS utf8 on': command not understood.
PWD
257 "/home/fld/uuuuuuu" is current directory.
TYPE I
200 Type set to I.
PASV
227 Entering Passive Mode (xx,xx,xx,xx,xx,xx)
STOR /folder/folder/folder/t.txt
150 Opening BINARY mode data connection for /folder/folder/folder/t.txt.
226 Transfer complete.
PASV
227 Entering Passive Mode (xx,xx,xx,xx,xx,xx)
RETR folder/folder/folder/t.txt
550 folder/folder/folder/t.txt: No such file or directory.
221 You could at least say goodbye.

This is the final code that seems to work:

        public static void SendFile(string sourcePath, string server, string userName, string password, string remoteFolder, string remoteFileName)
        {
            using (WebClient client = new WebClient())
            {
                string dest = string.Format(CultureInfo.InvariantCulture, "ftp://{0}/{1}/{2}", server, remoteFolder, remoteFileName);
                client.Credentials = new NetworkCredential(userName, password);
                client.Proxy = null;
                client.UploadFile(dest, sourcePath);
            }
        }

And the corresponding integration test:

        [TestMethod]
        public void ShouldSubmitFileToFtpServer()
        {
            Guid uniqueId = Guid.NewGuid();

            string tempFile = Path.Combine(Path.GetTempPath(), "FtpTestFile.txt");
            File.WriteAllText(tempFile, "Test file - safe to delete. " + uniqueId.ToString());

            string server = "sssssss";
            string userName = "uuuuuuu";
            string password = "ppppppp";
            string folder = "/folder/folder/folder";
            string fileName = "t.txt";
            Ftp.SendFile(tempFile, server, userName, password, folder, fileName);

            string tempFileDown = tempFile + ".down";

            if(File.Exists(tempFileDown)) File.Delete(tempFileDown);

            using (WebClient client = new WebClient())
            {
                client.Credentials = new NetworkCredential(userName, password);
                string ftpLink = String.Format(CultureInfo.InvariantCulture, "ftp://{0}/{1}/{2}", server, folder, fileName);
                client.CachePolicy = new HttpRequestCachePolicy(HttpRequestCacheLevel.NoCacheNoStore);
                client.DownloadFile(ftpLink, tempFileDown);
            }

            string content = File.ReadAllText(tempFileDown);

            Assert.IsTrue(content.Contains(uniqueId.ToString()));
        }

Example PowerShell header

This is a good template header that I use for new PowerShell scripts:

<#
.SYNOPSIS
    This is what the script does.
.NOTES
    Author: Me
.PARAMETER AParameter
    A parameter.
.PARAMETER ASwitch
    A switch.
#>

$ErrorActionPreference = 'Stop'
Set-StrictMode -Version 3

param(
    [parameter(mandatory)][string]$AParameter, 
    [switch]$ASwitch)

$scriptFolder = (split-path -path $MyInvocation.MyCommand.Definition -Parent)

Transforming an XML file with an XSLT in PowerShell

This code will transform an XML file with an XSLT file and save the resulting file to disk:

$xslt = new-object system.xml.xsl.xslcompiledtransform
$xslt.load('transform.xslt')
$xslt.Transform('input.xml', 'output.xml')

Complex work item queries in TFS

I recently wanted to create a work item query that showed all my work items that are either not closed or I had modified recently. This query provides a useful list of work items for associating with a check-in.

I wanted the query to show:
AssignedTo = @Me AND (State <> Closed OR (State = Closed AND ChangedDate > @Today - 7))

To do this, I needed to use multiple levels of the Group Clauses feature, which is available by selecting more than one clause and right clicking on one of the selected clauses.

The result is below: