SQL Server can generate a script for almost anything, but “a script was generated” does not mean the database—or even the object—is portable.

A table can depend on types, schemas, keys, functions, permissions and data. A database can depend on server logins, SQL Agent jobs, credentials, linked servers, certificates, external services and application configuration. Choose the artefact for the outcome, inventory what it cannot carry, and review the proposed target change before execution.

First decide what you are moving

Use one of four scopes:

One or a few database objects

You need a readable definition for a table, view, procedure, function, role or similar object. SSMS Script as or the Generate Scripts wizard may be sufficient, provided dependencies and options are captured.

A controlled schema change

You need the target schema to converge on a reviewed model. A SQL database project and DACPAC can make schema source-controlled and produce a deploy report or incremental script.

Schema plus table data

You need portability into a new database. A BACPAC contains schema and table data by default, but is not the same as a native backup and does not carry every instance feature.

A complete workload transition

You need database state, recoverability, logins, jobs, keys, integration, application cutover and operational acceptance. This is a migration programme. A generated script, DACPAC or BACPAC may be one artefact within it—not the programme itself.

Build a source and target inventory

Record:

  • source and target engine, edition, version and compatibility level;
  • database size, growth, availability and acceptable outage/data loss;
  • collation and database options;
  • schemas, tables, views, procedures, functions, triggers, sequences and types;
  • keys, constraints, indexes, partitioning, full-text and statistics needs;
  • users, roles, permissions and row-level security;
  • encryption keys/certificates and protected columns;
  • SQL Agent jobs, alerts, operators and maintenance tasks;
  • logins and server-role membership;
  • linked servers, credentials, proxies, endpoints and external data sources;
  • replication, CDC, Service Broker, CLR and vendor components;
  • application connection, transaction and deployment expectations; and
  • current backup, restore evidence and rollback target.

Do not put passwords or production connection strings in the inventory or generated scripts.

Script a single object deliberately

SSMS can generate CREATE, ALTER or other script forms for selected objects. Before using the output, ask:

  • Is the object’s schema owner present?
  • Are referenced user-defined types, functions, sequences or tables present first?
  • Are keys, defaults, checks, triggers and indexes included?
  • Does the target support the syntax and feature?
  • Are permissions and role memberships included or deliberately separate?
  • Is data required, and if so, how will it be moved consistently?

For a table, scripting only the visible column definition may omit the relationships and operational behaviour that make it safe. For a procedure, a successful CREATE does not prove its dependencies, permissions or application contract.

Save generated output as Unicode text, review it in version control or a controlled change record, and avoid executing directly from an unreviewed clipboard.

Use Generate Scripts for a defined object set

The SSMS Generate and Publish Scripts Wizard can script an entire database or selected objects. Its advanced options matter. Depending on the outcome, review:

  • target engine and target server version;
  • schema only, data only, or schema and data;
  • object-level permissions and role membership;
  • constraints, indexes, triggers and statistics;
  • collation and database options;
  • existence/drop behaviour; and
  • unsupported-object messages.

Target settings can exclude or comment unsupported features. That is a warning to redesign or choose a different migration path, not permission to delete the comments and hope.

Data-as-INSERT scripting can be useful for small reference datasets. It is usually a poor bulk migration mechanism for large or changing tables: scripts become huge, consistency is harder to control, identities and constraints need care, and retry behaviour may be unclear.

Know what DACPAC and BACPAC mean

DACPAC

A DACPAC is a portable database model/build artefact. Data is not included by default, although extraction can be configured to include user-table data. Publishing compares the source model with the target and calculates an incremental update.

That comparison can include drops or data movement. Keep the default data-loss protections, but do not mistake them for a guarantee. A conversion can fail on actual data, and an allowed drop can still be operationally wrong.

BACPAC

A BACPAC contains a database model and table data by default and imports into a new database. It is useful for portability and some archival/migration scenarios. It is not a transaction-log-aware native backup, an instance configuration export or proof that every workload feature is supported on the target.

Microsoft states that DACPAC and BACPAC files are compressed but not encrypted. Treat them as sensitive: a BACPAC normally contains production data, and a DACPAC reveals schema and may contain data if extracted that way. Protect storage, transfer, access and disposal.

Generate reports and scripts before publish

SqlPackage can produce:

  • a DeployReport describing changes a publish would make;
  • a DriftReport describing changes since a registered schema state; and
  • a Script containing the incremental Transact-SQL needed to make a target match a source model.

Review the output for:

  • object drops or recreation;
  • narrowing or conversion of data types;
  • table rebuilds and data movement;
  • changes to permissions, users or role membership;
  • collation/database-option changes;
  • disabled/re-enabled triggers;
  • SQLCMD variables and environment values;
  • long locks, transaction-log growth and outage impact; and
  • objects present in the target but absent from the source model.

Options such as DropObjectsNotInSource, BlockOnPossibleDataLoss, AllowIncompatiblePlatform and AllowUnsafeRowLevelSecurityDataMovement have serious consequences. Do not weaken a guardrail simply to make generation or deployment complete.

Capture dependencies outside the database

A moved or restored database can contain a user whose corresponding server login is missing or has a different security identifier. The result is an orphaned user or failed connection even though the database itself is online.

Inventory and migrate through supported, reviewed procedures:

  • Windows, Microsoft Entra and SQL logins;
  • server roles and instance-level permissions;
  • SQL Agent jobs, schedules, proxies, alerts and operators;
  • credentials, certificates, keys and secrets;
  • linked servers and remote credentials;
  • endpoints and network/TLS configuration;
  • server configuration and trace/event sessions; and
  • application-side connection strings, DNS and secrets.

Do not script plaintext passwords into a file. Some login scripting paths do not carry passwords; design credential handover separately.

Choose a consistency and cutover model

For a changing database, define:

  • full-copy start and duration;
  • how deltas are captured or writes are frozen;
  • acceptable recovery point and downtime;
  • target validation before final sync;
  • application connection switch;
  • source read-only/retirement timing; and
  • rollback before and after new target writes.

Running two writable copies without controlled replication creates divergent truth. A DNS or connection-string switch is not the migration; it is one cutover step.

Rehearse against a disposable target

Use a non-production target that matches the destination engine and compatibility constraints. Protect copied data or use representative sanitised data where production content is unnecessary.

Rehearse:

  1. package/script creation from a controlled source state;
  2. target creation and instance-dependency setup;
  3. report and generated-script review;
  4. deployment/import execution and timing;
  5. row/count/control-total reconciliation appropriate to the workload;
  6. object, permission and dependency comparison;
  7. application functional and performance checks; and
  8. failure recovery and cleanup.

Record every manual correction. If the rehearsal depends on undocumented clicks or ad-hoc SQL, the production plan is not repeatable yet.

Verify the workload after migration

Confirm more than “database online”:

  • expected schemas and objects exist;
  • data reconciliation matches defined controls;
  • constraints, identities/sequences, indexes and triggers behave correctly;
  • users, roles and application identities have only intended access;
  • jobs, integrations and notifications run under correct owners;
  • encryption, backup, monitoring and maintenance work;
  • application reads, writes and transactions complete; and
  • rejected or unauthorised operations remain rejected.

Then establish a fresh target backup, verify operational monitoring and retain the migration artefacts under controlled access for the approved period. Retire the source only when the workload owner accepts the target and the rollback boundary is understood.