Blank database setup

Provisioning a new empty ERP database requires three phases: create the database, apply schema exported from a working SQL Server instance, then run minimal seed scripts. Schema is not stored in Git — you generate it from a known-good local database.

Introduction

DB_DAMSYERP is the default database name used across deployment scripts and documentation. A blank database has no user tables until you apply a schema-only script. The minimal seed alone cannot create hundreds of tables and stored procedures.

Script pack location in the application repository:

database-backup/scripts-blank-db/

Purpose

Stand up a fresh SQL Server database for VPS staging, disaster recovery, or developer onboarding when a full backup restore is not available — while keeping naming and connection strings consistent with the web application.

When to use

• New VPS or SQL Express instance
• Replacing a corrupted database while keeping the same app deploy
• Creating an isolated test database without production data

Do not use blank setup when you need historical transactions — restore a full backup instead.

How it works

Script	Role
00-recreate-blank-database.sql	Drop and recreate empty DB_DAMSYERP (destructive)
01-create-blank-database.sql	Create database if missing; set RECOVERY SIMPLE
02-verify-blank-database.sql	Report table/procedure/view counts
03-NOTES-apply-schema.sql	Instructions only — schema export/apply
04-seed-minimal.sql	Company, role, branch, admin user, module flags
04b-fix-admin-login.sql	Diagnose/repair admin login chain
05-update-connection-string-NOTES.sql	Point app at new database
Export-SchemaOnly-FromLocal.ps1	Optional automation for SSMS export path
Clean-SchemaScript.ps1	Remove CREATE DATABASE, bad paths, orphan logins

Step-by-step — Full setup

Phase 1 — Create the database

Option A — Fresh create (non-destructive if DB missing)

1. Connect SSMS to localhost\SQLEXPRESS (enable Trust server certificate if prompted).
2. Open and execute 01-create-blank-database.sql.
3. Run 02-verify-blank-database.sql.

Expected: UserTableCount = 0, UserProcedureCount = 0.

Option B — Clean slate (drops existing data)

1. Execute 00-recreate-blank-database.sql.
2. Run 02-verify-blank-database.sql.

danger

00-recreate-blank-database.sql drops DB_DAMSYERP if it exists. Take a backup first in production.

Phase 2 — Export schema from working database

On a PC with a full working ERP database (commonly DB_GLOBALSECURITY):

1. SSMS → right-click source database → Tasks → Generate Scripts…
2. Choose Script entire database and all database objects
3. Advanced → Types of data to script = Schema only
4. Save to file, e.g. C:\Temp\DB_DAMSYERP_SchemaOnly.sql

Edit the file (Notepad or script):

1. Replace all DB_GLOBALSECURITY → DB_DAMSYERP
2. Ensure script contains USE [DB_DAMSYERP]
3. Copy to VPS, e.g. C:\DBBackup\DB_DAMSYERP_SchemaOnly.sql

Phase 2b — Clean the schema file (required)

Generate Scripts often includes artifacts that fail on VPS:

Error	Cause
Msg 1801 / 5133	Embedded CREATE DATABASE + old .mdf paths
Msg 15007	Orphan SQL login/user (e.g. [gsa]) from source laptop

On the VPS:

cd C:\DBBackup
powershell -ExecutionPolicy Bypass -File "Clean-SchemaScript.ps1" `
  -InputFile "C:\DBBackup\DB_DAMSYERP_SchemaOnly.sql"

Output: DB_DAMSYERP_SchemaOnly_CLEAN.sql

Copy Clean-SchemaScript.ps1 from database-backup/scripts-blank-db/ first.

Phase 2c — Apply schema

1. SSMS → connect to VPS SQL instance.
2. Ensure DB_DAMSYERP exists (Phase 1).
3. Open DB_DAMSYERP_SchemaOnly_CLEAN.sql
4. Database dropdown = DB_DAMSYERP → Execute (F5).

Optional sqlcmd:

sqlcmd -S localhost\SQLEXPRESS -E -C -d DB_DAMSYERP `
  -i "C:\DBBackup\DB_DAMSYERP_SchemaOnly_CLEAN.sql"

5. Run 02-verify-blank-database.sql.

Expected: UserTableCount > 0, UserProcedureCount > 0 (on the order of hundreds).

You should not see Msg 1801, 5133, or 15007 after cleaning.

Phase 3 — Seed minimal data

1. Execute 04-seed-minimal.sql against DB_DAMSYERP.
2. If login fails, run 04b-fix-admin-login.sql.
3. Update IIS Web.config — see 05-update-connection-string-NOTES.sql and Connection strings.
4. Recycle IIS app pool; test login.

Default credentials after seed — details in Seed scripts:

Field	Value
Company	Damsy Technologies (CompID 1)
Login ID	admin
Password	Admin@123

Examples

Example — Verification query output (after schema)

DatabaseName   UserTableCount   UserProcedureCount
DB_DAMSYERP    368              887

Counts vary by source export; zero counts mean schema not applied.

Example — Replace database name in PowerShell

(Get-Content C:\Temp\DB_DAMSYERP_SchemaOnly.sql) `
  -replace 'DB_GLOBALSECURITY','DB_DAMSYERP' |
  Set-Content C:\Temp\DB_DAMSYERP_SchemaOnly.sql

Example — Login chain verify (from seed script)

The seed ends with a SELECT matching GetLoginUser joins. Zero rows → run 04b-fix-admin-login.sql.

Related pages

• Seed scripts
• Database operations
• Deploy to IIS
• Troubleshooting — login failed

Important notes

• Do not create [gsa] login on VPS for schema fixes — the site uses Windows/IIS pool auth; clean the script instead.
• MenuMaster / UserPermissions may remain empty after minimal seed — home tiles may work while sidebar menus error until rows are copied from dev.
• SoftwareVersions rows are required for login page version display — seed script inserts them.
• BranchMaster is required — GetLoginUser INNER JOIN fails without a branch linked to the user.

Common mistakes

Mistake	Result
Skipping schema apply; running seed only	Mass INSERT errors
Running unclean schema file	CREATE DATABASE / path / login errors
Wrong USE database in SSMS	Objects created in master
Forgetting CompanyUser link	Login fails despite valid user row
Export includes data + schema unintentionally	Huge script; not needed for blank setup

Next steps

1. Read Seed scripts for admin user and module flags.
2. Deploy to IIS with updated connection string.
3. Copy MenuMaster / UserPermissions from dev if navigation breaks — Common issues.
4. Change default admin password after first successful login.