Examples
Copy-ready scripts for common SQL Server changes, plus configuration and CI pipelines.
- Create a table
- Add a NOT NULL column to a table with data
- Rename a column
- Data migration with a reversible split
- An irreversible change
- Statements that cannot run in a transaction
- A system-versioned (temporal) table
- A partitioned table
- Views, procedures and functions (repeatables)
- Seed data for one environment
- Environment-specific names with variables
- Logins, users and grants that differ per environment
- An Always Encrypted column
- A complete bsm.yaml
- A plan after two developers merged
- GitHub Actions
- Azure Pipelines
Create a table
The basic shape of every change: deploy, the exact opposite in revert, and a verify script that raises an error if the deploy did not take effect. Verify runs inside the same transaction, so a failing check rolls the change back.
changes/create-customer/deploy.sqlCREATE TABLE dbo.Customer (
Id int IDENTITY NOT NULL CONSTRAINT PK_Customer PRIMARY KEY,
Name nvarchar(200) NOT NULL,
Email nvarchar(320) NULL,
CreatedAt datetime2(3) NOT NULL CONSTRAINT DF_Customer_CreatedAt DEFAULT (sysutcdatetime())
);
CREATE UNIQUE INDEX UX_Customer_Email ON dbo.Customer (Email) WHERE Email IS NOT NULL;changes/create-customer/revert.sqlDROP TABLE dbo.Customer;changes/create-customer/verify.sqlIF OBJECT_ID(N'dbo.Customer', N'U') IS NULL
THROW 50000, N'dbo.Customer was not created', 1;
IF INDEXPROPERTY(OBJECT_ID(N'dbo.Customer'), N'UX_Customer_Email', 'IsUnique') <> 1
THROW 50000, N'UX_Customer_Email is missing or not unique', 1;bsm add create-customer --note "customer table"Add a NOT NULL column to a table with data
Add it nullable, backfill, then tighten. Batches separated by GO run in order inside the change's transaction. Name constraints explicitly: system-generated names differ between servers.
ALTER TABLE dbo.Customer ADD Status tinyint NULL;
GO
UPDATE dbo.Customer SET Status = 1 WHERE Status IS NULL;
GO
ALTER TABLE dbo.Customer ALTER COLUMN Status tinyint NOT NULL;
ALTER TABLE dbo.Customer ADD CONSTRAINT DF_Customer_Status DEFAULT (1) FOR Status;
ALTER TABLE dbo.Customer ADD CONSTRAINT CK_Customer_Status CHECK (Status IN (1, 2, 3));changes/add-customer-status/revert.sqlALTER TABLE dbo.Customer DROP CONSTRAINT CK_Customer_Status;
ALTER TABLE dbo.Customer DROP CONSTRAINT DF_Customer_Status;
ALTER TABLE dbo.Customer DROP COLUMN Status;bsm.plan (line added by bsm add -r create-customer)add-customer-status [create-customer] @2026-11-04T09:02Z aliceRename a column
Renames are reversible when you write both directions.
changes/rename-customer-email/deploy.sqlEXEC sp_rename N'dbo.Customer.Email', N'EmailAddress', N'COLUMN';changes/rename-customer-email/revert.sqlEXEC sp_rename N'dbo.Customer.EmailAddress', N'Email', N'COLUMN';changes/rename-customer-email/verify.sqlIF COL_LENGTH(N'dbo.Customer', N'EmailAddress') IS NULL
THROW 50000, N'EmailAddress column missing', 1;Data migration with a reversible split
Move data into a new shape and keep the revert honest. For very large tables, batch the update.
changes/split-customer-name/deploy.sqlALTER TABLE dbo.Customer ADD FirstName nvarchar(100) NULL, LastName nvarchar(100) NULL;
GO
DECLARE @batch int = 5000;
WHILE 1 = 1
BEGIN
UPDATE TOP (@batch) dbo.Customer
SET FirstName = LEFT(Name, CHARINDEX(N' ', Name + N' ') - 1),
LastName = LTRIM(SUBSTRING(Name, CHARINDEX(N' ', Name + N' ') + 1, 200))
WHERE FirstName IS NULL;
IF @@ROWCOUNT < @batch BREAK;
ENDchanges/split-customer-name/revert.sqlUPDATE dbo.Customer SET Name = CONCAT_WS(N' ', FirstName, NULLIF(LastName, N''));
ALTER TABLE dbo.Customer DROP COLUMN FirstName, LastName;An irreversible change
Some changes cannot be undone, such as dropping data you no longer keep. Mark them !irreversible: no revert.sql is needed and bsm revert stops with a clear message instead of guessing. Archiving first is good manners.
SELECT * INTO archive.LegacyOrder_20261103 FROM dbo.LegacyOrder;
DROP TABLE dbo.LegacyOrder;bsm add drop-legacy-orders --irreversibleStatements that cannot run in a transaction
SQL Server refuses some statements inside a transaction (ALTER DATABASE, full-text DDL, RECONFIGURE, resumable index operations). The linter catches them; mark the change !notx and make the script safe to re-run, because a failure halfway leaves partial work.
IF NOT EXISTS (SELECT 1 FROM sys.fulltext_catalogs WHERE name = N'ftCatalog')
CREATE FULLTEXT CATALOG ftCatalog;
GO
IF NOT EXISTS (SELECT 1 FROM sys.fulltext_indexes WHERE object_id = OBJECT_ID(N'dbo.Product'))
CREATE FULLTEXT INDEX ON dbo.Product (Name, Description) KEY INDEX PK_Product ON ftCatalog;changes/product-fulltext/revert.sqlIF EXISTS (SELECT 1 FROM sys.fulltext_indexes WHERE object_id = OBJECT_ID(N'dbo.Product'))
DROP FULLTEXT INDEX ON dbo.Product;
GO
IF EXISTS (SELECT 1 FROM sys.fulltext_catalogs WHERE name = N'ftCatalog')
DROP FULLTEXT CATALOG ftCatalog;bsm.planproduct-fulltext [create-product] !notxA system-versioned (temporal) table
Name the history table so it is the same on every server and the revert can drop it. To change the table later, switch versioning off, change both tables and switch it on again in one change (lint rule BSM004 catches a missing ON).
changes/create-price/deploy.sqlCREATE TABLE dbo.Price (
Id int NOT NULL CONSTRAINT PK_Price PRIMARY KEY,
ProductId int NOT NULL,
Amount money NOT NULL,
ValidFrom datetime2 GENERATED ALWAYS AS ROW START HIDDEN NOT NULL,
ValidTo datetime2 GENERATED ALWAYS AS ROW END HIDDEN NOT NULL,
PERIOD FOR SYSTEM_TIME (ValidFrom, ValidTo))
WITH (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.PriceHistory, HISTORY_RETENTION_PERIOD = 2 YEARS));changes/create-price/revert.sqlALTER TABLE dbo.Price SET (SYSTEM_VERSIONING = OFF);
DROP TABLE dbo.Price, dbo.PriceHistory;changes/create-price/verify.sqlIF OBJECTPROPERTY(OBJECT_ID(N'dbo.Price'), 'TableTemporalType') <> 2
THROW 50000, N'dbo.Price is not system-versioned', 1;A partitioned table
The function, scheme and table go in one change. Filegroups come from a variable because they differ per environment. If a job adds partitions in production, set schemaFingerprint.partitionBoundaries: ignore for that environment so its splits are not reported as drift.
CREATE PARTITION FUNCTION pfOrderDate (date) AS RANGE RIGHT FOR VALUES ('2026-01-01', '2026-04-01', '2026-07-01', '2026-10-01');
CREATE PARTITION SCHEME psOrderDate AS PARTITION pfOrderDate ALL TO ({OrderFilegroup});
CREATE TABLE dbo.[Order] (
OrderDate date NOT NULL,
Id bigint NOT NULL,
CustomerId int NOT NULL,
CONSTRAINT PK_Order PRIMARY KEY (OrderDate, Id)) ON psOrderDate (OrderDate);
CREATE INDEX IX_Order_Customer ON dbo.[Order] (CustomerId) ON psOrderDate (OrderDate);changes/create-order/revert.sqlDROP TABLE dbo.[Order];
DROP PARTITION SCHEME psOrderDate;
DROP PARTITION FUNCTION pfOrderDate;bsm.yamlvariables:
OrderFilegroup: "[PRIMARY]" # Azure SQL Database has only PRIMARY
environments:
onprem-prod:
variables:
OrderFilegroup: "[FG_Orders]"
schemaFingerprint:
partitionBoundaries: ignore # a job splits new partitions every monthViews, procedures and functions (repeatables)
Programmable objects live in repeatable/ and use CREATE OR ALTER. Badger SQL Migrate re-applies one only when its hash changes, after all changes, in dependency order. Declare dependencies with a header.
CREATE OR ALTER VIEW dbo.vActiveCustomer
AS
SELECT Id, Name, EmailAddress
FROM dbo.Customer
WHERE Status = 1;repeatable/procedures/usp_FindCustomer.sql-- bsm:requires views/vActiveCustomer.sql
CREATE OR ALTER PROCEDURE dbo.usp_FindCustomer
@search nvarchar(200)
AS
BEGIN
SET NOCOUNT ON;
SELECT TOP (50) Id, Name, EmailAddress
FROM dbo.vActiveCustomer
WHERE Name LIKE @search + N'%'
ORDER BY Name;
ENDSeed data for one environment
Scripts under environments/<name>/ run only in that environment, after the shared repeatables. Write them idempotently; they re-run when they change.
-- bsm:requires views/vActiveCustomer.sql
MERGE dbo.Customer AS t
USING (VALUES
(N'Ada Lovelace', N'ada@example.com', 1),
(N'Grace Hopper', N'grace@example.com', 1)
) AS s (Name, EmailAddress, Status)
ON t.EmailAddress = s.EmailAddress
WHEN NOT MATCHED THEN
INSERT (Name, EmailAddress, Status) VALUES (s.Name, s.EmailAddress, s.Status);Environment-specific names with variables
The same script targets calc_test in test and calc_prod in prod. Built-ins like {env} and {db} are always available; your own variables live in bsm.yaml.
variables:
schemas:
calc: calc_{env}
AppRole: app_reader
environments:
test:
database: AppTest
prod:
database: AppProd
variables:
AppRole: app_reader_prod
linkedServer: REPORTING01changes/create-calc-schema/deploy.sqlCREATE SCHEMA [{schemas.calc}];
GO
CREATE TABLE [{schemas.calc}].[table1] (
Id int NOT NULL PRIMARY KEY,
Note nvarchar(100) NOT NULL DEFAULT (N'{{env}} stays literal')
);
GO
CREATE ROLE [{AppRole}];
GRANT SELECT ON SCHEMA::[{schemas.calc}] TO [{AppRole}];
PRINT 'deployed {change} to {env} ({db}) with bsm {bsm.version}';bsm render create-calc-schema -e prod # prints the exact SQL prod will receive
bsm vars -e prod # every variable, its value and where it came fromoutput of bsm render -e prodCREATE SCHEMA [calc_prod];
GO
CREATE TABLE [calc_prod].[table1] (
Id int NOT NULL PRIMARY KEY,
Note nvarchar(100) NOT NULL DEFAULT (N'{env} stays literal')
);
GO
CREATE ROLE [app_reader_prod];
GRANT SELECT ON SCHEMA::[calc_prod] TO [app_reader_prod];
PRINT 'deployed create-calc-schema to prod (AppProd) with bsm 0.1.0';Logins, users and grants that differ per environment
Principals usually differ per environment. Keep the shape in the change and the names in variables. Secrets come from the environment, never from the file.
bsm.yamlenvironments:
prod:
variables:
apiIdentity: myapp-api-prod # Entra managed identity name
test:
variables:
apiIdentity: myapp-api-testchanges/grant-api-identity/deploy.sqlCREATE USER [{apiIdentity}] FROM EXTERNAL PROVIDER;
ALTER ROLE db_datareader ADD MEMBER [{apiIdentity}];
GRANT EXECUTE ON SCHEMA::dbo TO [{apiIdentity}];changes/grant-api-identity/revert.sqlREVOKE EXECUTE ON SCHEMA::dbo FROM [{apiIdentity}];
ALTER ROLE db_datareader DROP MEMBER [{apiIdentity}];
DROP USER [{apiIdentity}];An Always Encrypted column
The change names a column encryption key; each environment decides which column master key protects it. Badger SQL Migrate creates the keys before the change runs.
bsm.yamlenvironments:
test:
alwaysEncrypted:
columnMasterKeys:
CMK_Test: { keyStore: AZURE_KEY_VAULT, keyPath: "https://myapp-test-kv.vault.azure.net/keys/cmk/1a2b3c" }
columnEncryptionKeys:
CEK_Customer: { columnMasterKey: CMK_Test }
prod:
alwaysEncrypted:
columnMasterKeys:
CMK_Prod_2026: { keyStore: AZURE_KEY_VAULT, keyPath: "https://myapp-prod-kv.vault.azure.net/keys/cmk/9f8e7d" }
columnEncryptionKeys:
CEK_Customer: { columnMasterKey: CMK_Prod_2026 }changes/add-customer-taxid/deploy.sqlALTER TABLE dbo.Customer ADD TaxId char(11) COLLATE Latin1_General_BIN2
ENCRYPTED WITH (
COLUMN_ENCRYPTION_KEY = CEK_Customer,
ENCRYPTION_TYPE = Deterministic,
ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256') NULL;changes/add-customer-taxid/revert.sqlALTER TABLE dbo.Customer DROP COLUMN TaxId;bsm env status -e prod # keys: in sync, to create, to rotate, conflicting
bsm deploy -e prod # creates CMK_Prod_2026 and CEK_Customer, then runs the changeA complete bsm.yaml
A realistic configuration: a local container, Azure test and production, a template for shared settings, and production rules.
bsm.yamlprovider: sqlserver
defaultEnvironment: local
preprocessor:
unknown: error # unknown {name} in a script is an error
variables:
AppRole: app_reader
schemas:
reporting: reporting_{env}
environments:
_azure: # template: shared by test and prod through extends
database: App
tls: { mode: strict }
authentication: entraInteractive
entra:
tenantId: 7f3c1e2a-0000-0000-0000-000000000000
connectionProperties:
Connect Retry Count: 3
Connect Retry Interval: 10
local:
host: localhost
database: App
authentication: password
user: sa
password: ${env:BSM_LOCAL_PASSWORD}
tls: { mode: mandatory, trustServerCertificate: true }
test:
extends: _azure
description: Shared test database
host: myapp-test.database.windows.net
prod:
extends: _azure
description: Production
host: myapp-prod.database.windows.net
protected: true # type "prod" (or pass --yes) to deploy
allowRevert: false # fix forward
ordering: strict # exact plan order only
authentication: entraWorkloadIdentity
variables:
AppRole: app_reader_prod
connectionProperties:
Command Timeout: 600A plan after two developers merged
Alice and Bob each appended a line on their branches. Git's union merge driver (set up by bsm init) kept both lines without a conflict.
# Badger SQL Migrate plan: one change per line, in deploy order.
create-customer @2026-11-03T14:10Z brock # customer table
add-customer-status [create-customer] @2026-11-03T16:42Z brock
@release-1.0
rename-customer-email [create-customer] @2026-11-04T09:02Z alice
add-order-table [create-customer] @2026-11-04T09:15Z bob
product-fulltext [create-product] !notx @2026-11-05T11:30Z bob
drop-legacy-orders !irreversible @2026-11-06T08:00Z alicebsm plan check # unique names, dependencies, folders, lint, variables in every environmentGitHub Actions
Check on every pull request; deploy to test on merge; deploy to production with workload identity federation (no secrets).
.github/workflows/database.ymlname: database
on:
pull_request:
push:
branches: [main]
permissions:
id-token: write # Entra workload identity federation
contents: read
jobs:
check:
runs-on: ubuntu-latest
steps:
- uses: actions/checkout@v4
- uses: actions/setup-dotnet@v4
with: { dotnet-version: 10.0.x }
- run: dotnet tool install --global Badger.SqlMigrate
- run: bsm plan check -C db
deploy:
if: github.ref == 'refs/heads/main'
needs: check
runs-on: ubuntu-latest
environment: production
steps:
- uses: actions/checkout@v4
- uses: actions/setup-dotnet@v4
with: { dotnet-version: 10.0.x }
- run: dotnet tool install --global Badger.SqlMigrate
- uses: azure/login@v2
with:
client-id: ${{ vars.AZURE_CLIENT_ID }}
tenant-id: ${{ vars.AZURE_TENANT_ID }}
allow-no-subscriptions: true
- run: bsm deploy -C db -e test --json
- run: bsm deploy -C db -e prod --yes --json
- run: bsm verify -C db -e prodbsm.yaml (prod for this pipeline) prod:
extends: _azure
authentication: entraAzureCli # uses the azure/login session aboveAzure Pipelines
With a workload-identity service connection, use entraAzurePipelines, or run inside an AzureCLI task with entraAzureCli.
steps:
- task: UseDotNet@2
inputs: { packageType: sdk, version: 10.0.x }
- script: dotnet tool install --global Badger.SqlMigrate
- script: bsm deploy -C db -e prod --yes
env:
SYSTEM_ACCESSTOKEN: $(System.AccessToken)bsm.yaml prod:
extends: _azure
authentication: entraAzurePipelines
entra:
tenantId: 7f3c1e2a-0000-0000-0000-000000000000
clientId: 0a1b2c3d-0000-0000-0000-000000000000
serviceConnectionId: 5e6f7a8b-0000-0000-0000-000000000000
systemAccessToken: ${env:SYSTEM_ACCESSTOKEN}A runnable sample project is in the repository under samples/demo-db. For every option, see the documentation.