/SOS_ExtractCompareUpdate

Exploring Extract, Compare and Update in SQLOpsStudio

Primary LanguagePLpgSQL

SOS_ExtractCompareUpdate

SQL Ops Studio is a neat dev environment for SQL Server based on VSCode shell.

I think of it like SQLOpsStudio is to SSMS as VSCode is to Visual Studio.

But what about the SSDT tooling in Visual Studio? Things like Import Database, Extract, Compare, Update Schema are wanting in SOS.

This simple project is to explore the command line capabilites for Extract, Compare, Update, Publish in the SOS terminal window using SSDT tools such as SQLPackage.exe and SQLCmd.exe.

Here is a suggestion to add such features to SOS: SSDT-style Import from database and sqlpackage.exe integration (extract, compare, update, publish, etc.)

microsoft/azuredatastudio#389

Prerequisites:

Setup:

  • Clone this repo to a local directory
  • Open the directory in SOS
  • change paths and variables in environment.bat

Usage:

Generate Create Scripts:

  • execute the following in cmd terminal
    • Scripter.cmd

Now you have one Create script per object in the source database organized in your project directory like so:

  • DATABASES
    • DatabaseName
      • Functions
        • dbo.FunctionName.UserDefinedFunction.sql
      • Procedures
        • dbo.ProcedureName.StoredProcedure.sql
      • Tables
        • dbo.TableName.Table.sql
      • View
        • dbo.ViewName.View.sql

Swell!

Extract, Compare, Update:

  • execute the following in cmd terminal
    • Createdbs.cmd

You now have two empty databases.

  • open Entity.sql and execute on source database

You now have a table called Entity in the source db.

  • execute the following in cmd terminal
    • extract.cmd
    • compare.cmd

This will create dacpacs and a delta script called CompareUpdate_%UpdateVersion%.sqlcmd.sql showing the diffenece in schema between the two dbs.
Neat!

  • execute the following in cmd terminal
    • update.cmd

This will apply the delta script to the target db. Now your databases are the same.
Woot!

  • Open AlterTable.sql and execute on source database
  • Change UpdateVersion variable in environment.bat
  • execute the following in cmd terminal
    • extract.cmd
    • compare.cmd

Now you should have a new delta update script.

  • execute the following in cmd terminal
    • update.cmd

Now your databases are the same again.

If you extract and compare again you'll get a delta script that doesn't make any changes.