How can I copy a production SQL Server database to a local Docker instance without Windows authentication?

Below is one approach that uses Microsoft’s cross‑platform SQLPackage tool to extract a lightweight DACPAC of just your schema objects and then deploy it against your local docker SQL Server instance. This method avoids copying over users (and related security objects) while preserving tables, schemas, functions, stored procedures, and views.

---

Overview

1. Extracting the Schema from Prod
   Use SQLPackage’s Extract action to create a DACPAC from your production database. When doing so, set parameters to exclude users (and any other security objects you don’t need) so that any schema tied to Windows authentication isn’t included.

2. Deploying on the Local Machine
   Transfer the resulting DACPAC to your Mac. Then use SQLPackage’s Publish action to deploy the schema to a docker-hosted SQL Server running with the SA account.

---

Step 1: Extracting the DACPAC from Production

Since SSMS isn’t available on Mac, SQLPackage is ideal because it’s cross‑platform. You can run a command like the following from a Windows machine (or any environment where SQLPackage is installed):

BASHCopied
sqlpackage /Action:Extract \
  /SourceConnectionString:"Data Source=prodserver;Initial Catalog=prodDb;User ID=yourUser;Password=yourPassword;" \
  /TargetFile:"proddb.dacpac" \
  /p:ExcludeObjectTypes=Logins,Users,Roles

Notes:

• The /p:ExcludeObjectTypes property excludes security objects (logins and users) that cause issues in a Windows‑authenticated production environment.
• You can adjust or add further exclusion parameters if needed.

---

Step 2: Deploying the DACPAC Locally

Once you have the DACPAC on your local machine, you can deploy it onto your docker‑hosted SQL Server instance. On your Mac, with SQLPackage installed (via Homebrew or direct download), run:

BASHCopied
sqlpackage /Action:Publish \
  /TargetConnectionString:"Data Source=localhost,1433;Initial Catalog=devDb;User ID=SA;Password=yourSAPassword;" \
  /SourceFile:"proddb.dacpac" \
  /p:DropObjectsNotInSource=True \
  /p:BlockOnPossibleDataLoss=False

Notes:

• The /p:DropObjectsNotInSource=True parameter ensures that any objects present in the target but not in the DACPAC are removed, keeping your local environment in sync.
• The /p:BlockOnPossibleDataLoss=False parameter bypasses interactive confirmation for changes that may lead to data loss (since you’re dealing with a dev database).

---

Benefits

• Local Control: Each developer works on a local copy, reducing migration conflicts.
• Cross‑Platform: SQLPackage is fully supported on Mac, eliminating reliance on SSMS.
• Clean Schema: With excluded Windows‑authenticated users, all local objects will be created under the SA account.

---

This solution provides a consistent way to extract only the objects you care about from production and re‑apply them locally, enhancing developer experience and reducing headaches around migration conflicts.

Feel free to adjust any parameters and review SQLPackage documentation for additional options as needed.