Scripting all MS SQL Databases using SMOscript

SMOscript user Eric reported that the tool fails to script all remaining objects of a database once an error occurs trying to generate a CREATE PROCEDURE statement for an encrypted stored procedure.

This problem has been fixed in the latest version 0.14 which is now available for download.

Based on his feedback I want to demonstrate more capabilities of SMOscript:

Logging

While SMOscript does not implement log file functionality, you can still redirect its output to files from the command line using the standard shell redirectors >, >> and 2>.

smoscript ...parameters... >filename.log 2>filename.err

Scripting all databases on a server

To script all databases of a server into separate files in distinct directories, you can use SMOscript to first list all databases, then invoke SMOscript to script each database in the original list:

set basepath=c:\path\to\output\
set first=

for /f in "usebackq" %%i in (`smoscript -s localhost`) do ↵
                                         (set db=%%i& call :smo)
goto :end

:smo

if "%first%"=="." (
    echo database %db%
    mkdir %basepath%%db%
    smoscript -s localhost -d %db% -f %basepath%%db%\create.sql ↵
                                         > %basepath%%db%.log
)
set first=.

goto :eof
:end

The first line of “smoscript -s” is not a database name, so we include it from the list by using the variable named “first”.

The simple combination of SMOscript with a bit of shell magic can be quite efficient.

Schema-Qualified Foreign Keys in SMOscript

The MSSQL SMO library has some funny functionality and options (depending on your sense of humor).

If you tell an Smo.Table to script itself and its dependent objects (indexes, constraints, etc.) using its Script(ScriptingOptions) method, it will correctly generate the table name in Schema.Table notation (schema qualified), but the foreign key constraints to other tables will not contain the schema of the referenced table.

That is, unless you set the ScriptingOptions.SchemaQualifyForeignKeyReferences member to true. Thanks to user Oliver for pointing that out.

SMOscript has now been fixed and is available for download here.

automssqlbackup Update 0.30

A user of automssqlbackup notified me of two problems with the program:

• If you try to backup a database running on a named SQL Server instance (server\instance), creation of the log file caused an error as the “\” is handled like a directory separator, and the server directory could not be found.
• If there is an exception during backup (executed by SMO’s SqlBackup method), the exception message is not displayed.

Both problems are fixed in the latest version 0.30 of automssqlbackup.

Note: By default, automssqlbackup performs a full backup on Sundays, and incremental backups on all other days. So if you run automssqlbackup during the week, and you never (fully) backed up your databases before, you will get an exception for the databases without full backups.

automssqlbackup is available for download here.

Standalone SMO 2008 download

The Microsoft SQL Server 2008 Feature Pack (link to April 2009 version) contains tools and libraries for SQL Server 2008, among them the latest version of SQL Server Management Objects (SMO) which support 2000, 2005, and 2008.

SMO is the technology used by my applications SMOscript and automssqlbackup to query and control SQL Server databases.

Listing database properties using SMO

Recently I came across a couple of questions asking how to retrieve the physical file names of databases via SMO on Stackoverflow.

These questions prompted me to add a “List Database Properties” function to SMOscript.

This new function lists all database properties accessible via SMO, the DatabaseOptions object, and the database’s FileGroups and Files.

Sample output:

C:\Projects\smoscript>smoscript -s localhost -d dbscript2dev db
Database dbscript2dev on server localhost:

Current user dbo is DBO DbOwner

Owner DOMAIN\User Created 26.03.2008 21:23
Accessible True Updatable True SystemObject False
Status Normal CompatibilityLevel Version80
Size 386 MB SpaceAvailable 148.694 kB
DataSpaceUsage 133.016 kB IndexSpaceUsage 15.984 kB
PrimaryFilePath C:\Programme\Microsoft SQL Server\MSSQL\data
Default FileGroup PRIMARY Schema dbo
Collation Latin1_General_CI_AS CaseSensitive False
FullTextEnabled False
LastBackup 20.06.2009 03:01
LastLogBackup 11.09.2008 23:50
ActiveConnections 0

Options AnsiNullDefault False AnsiNullsEnabled False AnsiPaddingEnabled False An
siWarningsEnabled False ArithmeticAbortEnabled False AutoClose False AutoCreateS
tatistics True AutoShrink False AutoUpdateStatistics True CloseCursorsOnCommitEn
abled False ConcatenateNullYieldsNull False DatabaseOwnershipChaining True Local
CursorsDefault False NumericRoundAbortEnabled False PageVerify TornPageDetection
 QuotedIdentifiersEnabled False RecoveryModel Full RecursiveTriggersEnabled True
 UserAccess Multiple

Filegroup PRIMARY
 Default True ReadOnly False Size 318.208 MB
 File dbscriptdev_Data
 Path C:\Programme\Microsoft SQL Server\MSSQL\data\dbscript2dev.mdf
 Primary True
 Size 318.208 MB MaxSize unlimited
 Used 176.768 kB Available 141.440 kB
 Growth 10 %

Logfile dbscriptdev_Log
 Path C:\Programme\Microsoft SQL Server\MSSQL\data\dbscript2dev_log.ldf
 Size 76.736 MB MaxSize unlimited
 Used 70.090 kB
 Growth 10 %

The latest version 0.12 of SMOscript is available for download here.

SMOscript: Scripting database objects using SMO

I created SMOscript a couple of months ago with the intention to make it a replacement for the SQL Server 2000 tool called scptxfr.

After installing SQL Server 2008 and AdventureWorks, I found that, besides some other shortcomings, SMOscript would not connect to an SQL Server 2008. Clearly time for an update!

The new version of SMOscript provides these improvements and new features:

The database dictionary is iterated faster by calling SetDefaultInitFields.

CREATE scripts now include child objects such as constraints and triggers as a result of SetDefaultInitFields.

SMOscript scripts all scriptable database objects as defined by SMO API. (The previous version only scripted tables, views, synonyms, stored procedures, and functions)

SMOscript now supports SQL Server 2008.

The latest version of SMOscript is available for download here.

Note: This is a command-line tool. You won’t see an icon under Start/Programs!

Upgrading SMO 2005 to SP3

The original SMO 2005 library does not support connections to MS SQL Server 2008. Trying to connect results in a exception with the message

This SQL Server version (10.0) not supported.

I had downloaded and installed the MS SQL Server 2005 Management Objects Collection only on my PC and wanted to upgrade to SP3, only to find that there is no separate download of SMO 2005 SP3.

So I downloaded SQL Server 2005 Express SP3 and started the installer. However there was no simple option to upgrade SMO (or XMO) only, and I did not want to install the database engine on this machine.

The solution I found was to leave the installer open, go to the installer’s temporary directory (in c:\ root, name consisting of 20 hex digits), and from the Setup subdirectory started SqlRun_Tools.msi.

This upgraded the SMO objects to SP3 level, and resolved the cause of the exception.

automssqlbackup Update

The latest version 0.29 of automssqlbackup manages to backup large databases which timed out in previous versions.

File sizes are handled as [long] values now, and exceptions during SMO backups are handled correctly, fixing two bugs in the notification email.

automssqlbackup is available for download here.

Handling SMO SqlBackup Timeout

I hope I have finally tracked down a bug in automssqlbackup which caused an exception when creating backups of huge databases (DB ~40GB, backup file ~4GB): The SqlBackup operation simply timed out as the default command timeout is set to 10 minutes.

I found that there is a property called StatementTimeout in the ServerConnection class, so that you can set the timeout to infinity like so:

$conn = New-Object Microsoft.SqlServer.Management.Common.ServerConnection($dbhost)
$conn.StatementTimeout = 0
$srv = New-Object Microsoft.SqlServer.Management.Smo.Server($conn)

$bk = New-Object Microsoft.SqlServer.Management.Smo.Backup
$bk.Action = [Microsoft.SqlServer.Management.Smo.BackupActionType]::Database
$bk.Database = $dbname
$bk.Initialize = $False
$bk.Incremental = -not $full
$bk.Devices.AddDevice($backupfile, Microsoft.SqlServer.Management.Smo.DeviceType]::File)
$bk.SqlBackup($srv)