Scripting SQL Server databases sans SMO Scripter

I can script out a database schema (and optionally data) from SQL Server databases. Any version from 2005 and beyond, as well as SQL Azure Database. And most all instance level objects.  And I'm not the SMO scripter object. Or a DBA. Who am I?

I would be DACFx 3.0. And I can do some things (unlike SMO scripter, or a busy DBA) like script out deltas (schema only), given any two databases or DACPACs. Or any combination of SSDT project, database, and DACPAC. 

When DAC (that's data-tier applications, not dedicated administrator connection) was introduced back around SQL Server 2008 R2, there were a subset of "most likely candidate" database objects it could script. This increased over time to include all SQL Azure database objects. But in DACFx 3.0, which appears in SSMS 2012, SSDT (along with its command-line brother sqlpackage.exe), and soon the Azure portal, there is almost full-fidelity up to SQL Server 2012. There are still some rough edges being worked on (along with enhancements), both in the functionality and in the integration with SSMS, as you might expect.

SSMS and SQL Server's PowerShell provider are both built with SMO, and SMO's functionality roughly follows what SSMS supports (e.g. before SMO 2012 there was no SSMS/SMO support for extended events; now there is, in both places). SMO is much, much more granular in its API… think "almost every SSMS dialog is built over SMO". DACFx providers a discrete set of (less than 10) utility functions. But in SSMS 2008 R2, 2008 R2 SP1, and 2012, DACFx makes its presence felt. For example in SSMS 2012, we have, in SSMS Object Explorer:
Databases Tab:
    Deploy (from DACPAC)
    Import (from BACPAC)
Individual Databases/Tasks:
    Extract (to DACPAC)
    Deploy Database to Azure (Migrate via BACPAC)
    Export (to BACPAC)
    Register (to MSDB, to master on SQL Azure)
    Upgrade (existing registration and schema)

In SSMS, these functions are fairly rigid, i.e. there aren't many options, failure to comply with the "default options" can cause failure. But with SQLPackage.exe and SSDT and the DACFx API, a plethora of options exist (albeit not as many as SMO scripter, but some of scripter's options are for code formatting). These options live in the DacDeployOptions and DacExtractOptions classes in the API (that is Microsoft.SqlServer.Dac.dll) and are exposed as command-line switches in SQLPackage.exe or properties in the SSDT project properties page. And documented in the SQL Server documentation. 

If you don't want to install SSDT on your machine, you can download DACFx 3.0 as part of the SQL Server 2012 Feature Pack. It's called "Microsoft® SQL Server® 2012 Data-Tier Application Framework".

More to come…


Other articles

Imagine feeling confident enough to handle whatever your database throws at you.

With training and consulting from SQLskills, you’ll be able to solve big problems, elevate your team’s capacity, and take control of your data career.