SQLCMD Alternative in PowerShell

[A versão em português deste artigo pode ser encontrada aqui]

I needed to run a T-SQL script of about 7GB. It was a giant INSERT script generated through the Management Studio “generate scripts” option and, for some reason, the SQLCMD utility was throwing the following error in the middle of the script:

Msg 102, Level 15, State 1, Server SRV, Line 1
Incorrect syntax near ...

After looking for the very statement that was throwing the error, I concluded that the statement was OK and that SQLCMD had a bug that prevented it from executing that enormous script. As I couldn’t find any other tool to execute such a script, I went to create my own SQLCMD in PowerShell, which I now share with you:

$filePath = "C:\backup.sql";
$stream = [System.IO.File]::OpenText($filePath);
$connection = New-Object System.Data.SqlClient.SqlConnection;
$connection.ConnectionString = "Persist Security Info=True;User ID=user;Password=passwd;Initial Catalog=Restored_Database;Data Source=LPC:.";
$connection.Open();
$command = New-Object System.Data.SqlClient.SqlCommand;
$command.Connection = $connection;
$statement = "";
$statementsProcessed = 0;
$totalBytes = (New-Object System.IO.FileInfo($filePath)).Length;
$bytesProcessed = 0;

while (-not $stream.EndOfStream) {
    # Read the next line
    $line = $stream.ReadLine();
    $bytesProcessed += [System.Text.Encoding]::Unicode.GetByteCount($line + [Environment]::NewLine);
    # If this line is a GO statement, execute the statement buffer read so far.
    if (($line.Trim() -eq "GO") -or ($line.Trim() -eq "GO;")) {
        try {
            #$statement;
            $command.CommandText = $statement;
            [void]$command.ExecuteNonQuery();
            $statement = "";
            "{0} statements processed ({1:f} MB) - {2:f}%" -f (++$statementsProcessed), ($bytesProcessed / 1024 / 1024), ($bytesProcessed / $totalBytes * 100);
        }
        catch {
            "!!! ERROR !!!";
            $Error;
        }
    }
    else {
        # Add the line to the statement buffer
        $statement += $line + [Environment]::NewLine;
    }
}    

$stream.Close();

Output example:

137238 statements processed (544.54 MB) - 7.57%
137239 statements processed (544.55 MB) - 7.57%
137240 statements processed (544.56 MB) - 7.57%
137241 statements processed (544.57 MB) - 7.57%
137242 statements processed (544.58 MB) - 7.57%
137243 statements processed (544.59 MB) - 7.57%
137244 statements processed (544.60 MB) - 7.57%
137245 statements processed (544.61 MB) - 7.57%
137246 statements processed (544.63 MB) - 7.58%
137247 statements processed (544.64 MB) - 7.58%
137248 statements processed (544.65 MB) - 7.58%
137249 statements processed (544.66 MB) - 7.58%
137250 statements processed (544.67 MB) - 7.58%
137251 statements processed (544.68 MB) - 7.58%
137252 statements processed (544.69 MB) - 7.58%
137253 statements processed (544.70 MB) - 7.58%
...
5716545 statements processed (7189.70 MB) - 100.00%
Advertisements

1 Comment

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s