SQL SERVER – Generate Script with Data from Database – Database Publishing Wizard

The Database Publishing Wizard scripted schema and data for SQL Server 2005-era transfers. I keep that workflow separate from current SSMS instructions.

Readers of my schema-copy article wanted the rows as well. The historical wizard could script schema, data, or both. Its .NET 2.0 and SQL Server 2005 SMO prerequisites and version-90 installation folder belong to that period.

The historical command-line workflow

"C:\Program Files\Microsoft SQL Server\90\Tools\Publishing\sqlpubwiz.exe" script -d AdventureWorks "C:\AdventureWorks.sql"
"C:\Program Files\Microsoft SQL Server\90\Tools\Publishing\sqlpubwiz.exe" script -d AdventureWorks "C:\AdventureWorks.sql" -schemaonly
"C:\Program Files\Microsoft SQL Server\90\Tools\Publishing\sqlpubwiz.exe" script -d AdventureWorks "C:\AdventureWorks.sql" -dataonly

The current SSMS route

1. Right-click the intended database and select Tasks > Generate Scripts.
2. Select the required objects and choose a file destination.
3. In Advanced scripting options, set Types of data to script to schema only, data only, or schema and data.
4. Review the target version and other options, then inspect the generated script before executing it.

Permissions and encrypted definitions limit what can be scripted. Instance-level dependencies, sensitive row values, and file locations need separate review. Generating a script does not prove it recreates every aspect of the database.

Data scripts can become very large. A tested backup/restore or another supported transfer method may fit a large database better. The original 100 MB suggestion was a small-test recommendation, not a guaranteed safe file size.

Reference: SSMS Generate Scripts Wizard.

Related reading

• SQL SERVER – 2005 – Create Script to Copy Database Schema and All The Objects – Stored Procedure, Functions, Triggers, Tables, Views, Constraints and All Other Database Objects

Original video

A generated script is not a validated migration, it is an artifact that still needs a recreation test.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.