This module provides the following capabilities:
To have access to the following features, you have to import the module:
PS> Install-Module -Name Arcus.Scripting.SQL
With this script, you can perform database upgrades by providing/adding specific SQL scripts with the right version number. Once a new version number is detected it will incrementally execute this.
While doing so it will create a table "DatabaseVersion". If the DatabaseVersion table doesn't exist it will automatically create it.
This function allows you to trigger a database migration, which will only execute the newly provided SQL scripts, based on the provided version number in each of the scripts. The current version is stored in a table "DatabaseVersion", which will be created if it doesn't exist yet.
|yes||The SQL Server that hosts the SQL Database. (Do not include 'database.windows.net')|
|yes||The name of the SQL Database|
|yes||The UserName of the SQL Database|
|yes||The Password of the SQL Database|
|no (default: ||The directory folder where the SQL migration scripts are located on the file system|
|no (default: ||The file filter to limit the SQL script files to use during the migrations|
|no (default: ||The database schema to use when running SQL commands on the target database|
Make sure that the credentials that you provide can write tables to the database + any action that you specify in the SQL scripts.
Example with defaults
PS> RunDatabaseScript -ServerName "my-server-name" -DatabaseName "my-database-name" -Username "my-sql-username" -Password "my-sql-password"# Looking for SQL scripts in folder: ./sqlScripts
Example with custom values
PS> RunDatabaseScript -ServerName "my-server-name" -DatabaseName "my-database-name" -Username "my-sql-username" -Password "my-sql-password" -ScriptsFolder "$PSScriptRoot/sql-scripts" -ScriptsFileFilter "*.MyScript.sql" -DatabaseSchema "custom"# Looking for SQL scripts in folder: ./sql-scripts
In the location where you want to run the script add the folder "sqlScripts".
Within this folder there should be by default the
CreateDatabaseVersionTable.sql-file, containing the script to create the initial version table:
CREATE TABLE [dbo].[DatabaseVersion]( [CurrentVersionNumber] INT NOT NULL, [MigrationDescription] [nvarchar](256) NOT NULL, CONSTRAINT [PKDatabaseVersion] PRIMARY KEY CLUSTERED ( [CurrentVersionNumber] ASC ) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON))
- Next to that you can add your scripts which, to be recognized by the module, need to match the following naming convention:
In practice this can look like this: Arcus_001_AddIndexes.sql
When a new migration comes along, just create the new SQL script with a version number one number higher than the previous one.