SQL db export script

• SQL db export script
  • Description
  • Download
  • Special Notes
  • Prerequisites
  • Output
  • SQL Compatibility
  • How to Run
    • • 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.

Download

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 user's 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]

SQL Compatibility

How to Run

1. Create a directory on your C: drive call Dev\Tools\DB Export

2. Save the file downloaded in the folder

3. Run the folloing command

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