SQL db export script

• SQL db export script
  • Description
  • Special Notes
  • Prerequisites
  • Output
  • Usage
  • Parameters
    • -server
    • -instance
    • -database
    • -outputPath
    • -username
    • -password
    • -schemas
    • -dmlTables
    • -objectTypes
    • -objectNameFilter
    • -offsetDate
    • -logFile
  • Features
  • SQL Compatibility
  • Examples
    • • Export all schemas and pass credentials
      •  
      • Export specific schemas
      • Use a connection string to Azure SQL
      • Export only stored procedures and functions [-objectTypes 'sp','functions']
      • Export only objects that contain the word test [-objectNameFilter 'test']
      • Export only objects that end the word debug [ -objectNameFilter 'debug$' ]
      • Export a delta of all changed/created objects since a specific date time [ -offsetDate '2022-11-01 06:00:00' ]
      • Export a delta of all changed/created objects last 7 days (dynamic date) [ -offsetDate "$((Get-Date).AddDays(-7))" ]
  • Future

Description

This script exports all the DDL related items of a database and some selected items of the instance into individual files which enables a better way to compare to databases with each other and also to import into a source control repository. Objects file time stamps are aligned with database timestamps with the exception for indexes.

git link: sql-script-db.ps1

Special Notes

• For Azure you have to use the connection string approach as Azure needs the Initial Catalog set and there is no way currently to do this programmatically. There is a work-around where we mock the connection string but this will be a future fix.
   
• When possible, try and run on a remote machine on the same network as the database this will avoid network overhead and speed up the process. After this you can zip and transfer locally as there will be many little files that can take a while to copy when not packed into one archive.
   
• Depending on the users level of access instance level options will only script if the user has the correct server roles, i.e. sysadmin. For the DB level the user has to have DB owner (if not a sysadmin).

Prerequisites

• Powershell 5+, works on 7+ (Core) [link]
• SqlServer module (automatically installs if missing) [link]

Output

The output goes into a folder specified by the outputPath folder, the folder structure is as follows

• \databases
  • \triggers
    • [trigger_name].sql
  • \[database_name]
    • listing.txt
    • \triggers
      • [trigger_name].sql
    • \[schema_name]
      • [schema_name].sql
      • \stored-procedures
        • [stored_procedure_name].sql
      • \tables
        • \[table_name]
          • [table_name].sql
          • \constraints
            • [constaint_name].sql
          • \triggers
            • [trigger_name].sql
          • \indexes
            • [index_name].sql
      • \functions
        • [function_name].sql
      • \types
        • [type_name].sql
      • \synonyms
        • [synonym_name].sql
      • \sequences
        • [sequence_name].sql
      • \views
        • [view_name].sql

Notes

• dbo would not have a ddl script as this is the default schema
• listing.txt contains a list of the objects to script based on filter criteria
• names are escaped where necessary to be filesystem "friendly" see [link]
• if we are only scripting a single database the databases\[database_name] is not enforced and the path that was passed in is used as the root

Usage

Parameters

-server

The host name or ip of the db server

-instance

Pass the value default if there is no specific instance

-database

The database name that we want to export. When a * is passed all databases on that instance will be scripted.

-outputPath

This specifies the location locally where all the objects will be written out

-username

The sql username to authenticate with, if this is not passed you will be prompted for credentials

-password

See username

-schemas

The is an list of schemas we would like to export, this defaults to * if no value is passed which indicates all schemas

-dmlTables

For future, list of tables we would like to generate DML scripts for this would be for config related tables and not transactional tables

-objectTypes

This will give you the options to script specific object types list of avaible options below

-objectNameFilter

This allows you to filter the objects with specific names uses regular expression to match

-offsetDate

This specifies from what modify date you would like objects to be scripted. For example if you want only the object scripted that was modified/created in the last seven days you would pass in the date 7 days ago. This allows you to script only the deltas if you have previously done a full dump. Currently indexes are excluded from this entirely as there is no way to accurately identify the modified date on these. This means that the table needs to have been modified in the offsetDate window for it to trigger index scripting.

-logFile

This specifies the location locally where we would like to output any internal logs that the script or modules generated while exciting, if omitted no extended output will be generated. This is useful for troubleshooting if something is not working as expected.

Features

• exports DDL for all non-system triggers, stored procedures, tables, indexes, constraints
• exports into individual ddl files which makes migrating into a source control repository simpler and enables objects level tracking
• set the file timestamp equal to the database timestamp of the objects (exceptions: indexes)
• allows for delta dumps based on an offset date time
• allows for specific objects names using regexp matches
• allows for specific object types
• standardizes stored procedure and function headers based on template
• creates a listing.txt file that contains the objects to be scripted, handy in delta scenarios

SQL Compatibility

Examples

Export all schemas and pass credentials

Notice the single quotes for password parameter this allows passing in reserved characters for the password ie a $

Export specific schemas

Use a connection string to Azure SQL

Export only stored procedures and functions [-objectTypes 'sp','functions']

Export only objects that contain the word test [-objectNameFilter 'test']

Export only objects that end the word debug [ -objectNameFilter 'debug$' ]

Export a delta of all changed/created objects since a specific date time [ -offsetDate '2022-11-01 06:00:00' ]

Export a delta of all changed/created objects last 7 days (dynamic date) [ -offsetDate "$((Get-Date).AddDays(-7))" ]

Future

• add parallel processing to speed up processing time
• script database and instance level options
• script DML for specified tables i.e. config tables
• use an approach to identify index modified dates
• adding hashes for cross database compares
• standardize formatting based on internal standards