I want Powershell’s invoke-sqlcmd to ignore SQL execution errors

powershellscripting

I've written a Powershell script that scripts out the appropriate SQL scripts for all databases on a server as well as creating BCP files for the contents of each. This is to allow the restoration of a database from a higher level of SQL to a lower level where backup and restore will be impossible (but there are no features or functions used that are incompatible otherwise). This is great, to automatically run them against the destination server is problematic.

Each script created contains small problems. For example the table scripts all start with dropping the table by name if it already exists. This is fine in SSMS because you can just remove that line if the table doesn't already exist to avoid the "doesn't exist or you don't have permission" errors. There's other things you'd remove too like creating indexes that already exist from having run other scripts that make other tables from the same database.

Invoke-sqlcmd -inputfile dbo.SomeTable.sql stops and fails on these errors. How can I make either invoke-sqlcmd ignore them and just do what it can (in the knowledge that's fine), or temporarily make SQL tolerant of those and not raise stopping errors, or what else can I use to programatically apply the scripts SQL server generates to an SQL server without manual intervention?

Best Answer

How can I [...] temporarily make SQL tolerant of those and not raise stopping errors

Test to see if the object exists before trying to drop it or create it. That way you'll still throw errors in the event that the DROP or CREATE itself fails rather than for collisions.

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[YourTableName]') AND type in (N'U'))
DROP TABLE [dbo].[YourTableName]
GO

IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[YourTableName]') AND type in (N'U'))
CREATE TABLE [dbo].[YourTableName] ( ... )

[Note: This syntax comes from the scripts that Microsoft.SqlServer.Management.Smo.Scripter generates.]

I'm assuming that your PowerShell scripting script is using Microsoft.SqlServer.Management.SMO.Scripter? Why not specify Scripter.Options.IncludeIfNotExists = $true? You get all the same options that you get with SSMS's Generate Scripts tool. Do something like this:

Add-Type -AssemblyName 'Microsoft.SqlServer.Smo, Version=14.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91'

$ServerName = 'servername'
$DatabaseName = 'databasename'
$ScriptFileName = 'C:\scriptfilename.sql'
if (Test-Path $ScriptFileName) {
    Remove-Item $ScriptFileName
}

$SMOServer = New-Object -TypeName Microsoft.SqlServer.Management.Smo.Server -ArgumentList $ServerName
$SMODatabase = $SMOServer.Databases.Item($DatabaseName)

$Scripter = New-Object -TypeName Microsoft.SqlServer.Management.Smo.Scripter -ArgumentList $SMOServer
$Scripter.Options.AllowSystemObjects = $false
$Scripter.Options.IncludeHeaders = $true
$Scripter.Options.IncludeIfNotExists = $true
$Scripter.Options.Indexes = $true
$Scripter.Options.Permissions = $true
$Scripter.Options.ScriptBatchTerminator = $true
$Scripter.Options.ScriptSchema = $true
$Scripter.Options.TargetServerVersion = [Microsoft.SqlServer.Management.Smo.SqlServerVersion]::Version100
$Scripter.Options.WithDependencies = $true
$Scripter.Options.FileName = $ScriptFileName
$Scripter.Options.AppendToFile = $true
$Scripter.Options.ToFileOnly = $true

# Drop all tables
$Scripter.Options.ScriptData = $false # Don't script data
$Scripter.Options.ScriptDrops = $true # Script drop statements
foreach ($SMOTable in $SMODatabase.Tables) {
    $Scripter.EnumScript($SMOTable.Urn)
}

# Create all tables with data, indexes, and permissions
$Scripter.Options.ScriptData = $true # Script table data
$Scripter.Options.ScriptDrops = $false # Script create statements
foreach ($SMOTable in $SMODatabase.Tables) {
    $CreateScripter.EnumScript($SMOTable.Urn)
}

This scripts the tables using SQL Server 2008 syntax (Version100). I'm using a version of the libraries that ships with Sql Server Management Studio 17.

Related Question