--- title: Microsoft SQL Server - Deployment description: Deploy the generated artifacts of a Microsoft SQL Server project with deploy.ps1. --- [Skip to content](https://knowledge.bigenius-x.com/microsoft-sql-server-deployment-2.0#main-content) [Contact us](https://knowledge.bigenius-x.com/kb-tickets/new?hsLang=en) [Go to Customer Portal](https://knowledge.bigenius-x.com/customer-portal?hsLang=en) [![TRI\_Logo\_biGENIUS\_2020\_RGB\_s](https://knowledge.bigenius-x.com/hs-fs/hubfs/Marketing/TRI_Logo_biGENIUS_2020_RGB_s.png?width=150&height=42&name=TRI_Logo_biGENIUS_2020_RGB_s.png)](https://www.bigenius-x.com/) Open main navigation Close main navigation - [Contact us](https://knowledge.bigenius-x.com/kb-tickets/new) - [Go to Customer Portal](https://knowledge.bigenius-x.com/customer-portal) How can we help you? - There are no suggestions because the search field is empty. 1. [biGENIUS-X Knowledge Base](https://knowledge.bigenius-x.com/?hsLang=en) 2. [Generators](https://knowledge.bigenius-x.com/generators?hsLang=en) 3. [Deployment](https://knowledge.bigenius-x.com/generators?hsLang=en#deployment) # Microsoft SQL Server - Deployment **Applies to** Platform: Microsoft SQL Server Deploy the generated artifacts of a Microsoft SQL Server project with `deploy.ps1`. The script runs sqlcmd, creates the databases, schemas and objects of every layer and the load control, and then validates every view and stored procedure. Afterwards the solution is ready to load. ### Prerequisites - The [Microsoft SQL Server target environment](https://knowledge.bigenius-x.com/microsoft-sql-server-target-environment?hsLang=en) is prepared: PowerShell 7, sqlcmd and a login that can create the databases and objects. - The artifacts are [generated](https://knowledge.bigenius-x.com/generate-artifacts?hsLang=en) and [downloaded](https://knowledge.bigenius-x.com/download-generated-artifacts?hsLang=en). - The placeholders are [replaced](https://knowledge.bigenius-x.com/replace-the-placeholders-in-the-artifacts?hsLang=en) with the [Microsoft SQL Server placeholder values](https://knowledge.bigenius-x.com/microsoft-sql-server-placeholder-values-2.0?hsLang=en). ### Deploy the artifacts 1. Open PowerShell 7 in the artifacts folder, the folder that contains `deploy.ps1`. For example, type `pwsh` in the address bar of File Explorer. With the default Deployment Options and Deploy LoadControl set to true, each deployment drops and recreates every generated table and the load control tables. Their data and the load history are lost. Before you deploy to a database that holds data, clear Generate Drop Table Statements in Project Settings, set Deploy LoadControl to false, and generate the artifacts again. 2. Run `.\deploy.ps1`. ![PowerShell after .\\deploy.ps1 with the prompt Please specify the server or instance name](https://knowledge.bigenius-x.com/hs-fs/hubfs/image-png-Feb-19-2024-01-53-25-7456-PM.png?width=392&height=88&name=image-png-Feb-19-2024-01-53-25-7456-PM.png) 3. At `Please specify the server or instance name`, enter the server, for example `\` or `,`. Leave it empty for `localhost`. ![Server prompt left empty; the script answers -\> using server name localhost](https://knowledge.bigenius-x.com/hs-fs/hubfs/image-png-Oct-04-2023-02-32-35-8829-PM.png?width=413&height=61&name=image-png-Oct-04-2023-02-32-35-8829-PM.png) 4. At `Please specify the user name`, enter a SQL Server login, or leave it empty for Windows authentication. ![User name prompt with a login entered](https://knowledge.bigenius-x.com/hs-fs/hubfs/image-png-Oct-04-2023-02-29-58-5268-PM.png?width=367&height=71&name=image-png-Oct-04-2023-02-29-58-5268-PM.png) 5. For a login, enter its password at `Please specify the password`. The script shows the messages of sqlcmd and ends with `Finished`. 6. Read the result as described in [Check the result](https://knowledge.bigenius-x.com/microsoft-sql-server-deployment-2.0#check-the-result). To skip the prompts, pass the values as parameters: `.\deploy.ps1 -ServerName -UserName -Password `. The order of the scripts that `deploy.sql` runs is explained in [Microsoft SQL Server - Artifacts](https://knowledge.bigenius-x.com/microsoft-sql-server-artifacts-2.0?hsLang=en). ### Check the result The deployment succeeded when no message starts with `Msg` and the last message before `Finished` is `Deployment validation succeeded.` When views or stored procedures do not compile, the deployment ends with: ``` Deployment validation found errors. Msg 50001, Level 16, State 1, Server , Line Deployment validation failed. ``` The `Msg` lines above it name each failed object. SQL Server Management Studio then lists the tables, views and stored procedures, for example of a Data Vault project in one database: ![Tables folder of the database with the load control tables in LC, the hub, link and satellite tables in RDV and the Stage tables in SA](https://knowledge.bigenius-x.com/hs-fs/hubfs/image-png-May-25-2023-01-12-23-0703-PM.png?width=378&height=773&name=image-png-May-25-2023-01-12-23-0703-PM.png) ![Views folder with the load control views in LC and the views of each hub with the suffixes \_Deduplication, \_Delta, \_Hashing, \_Result and \_Source](https://knowledge.bigenius-x.com/hs-fs/hubfs/image-png-May-25-2023-01-15-31-8264-PM.png?width=402&height=749&name=image-png-May-25-2023-01-15-31-8264-PM.png) ![Stored Procedures folder with the load control procedures in LC, from AddOrUpdate\_ExecutionPlan to Remove\_ExecutionPlan](https://knowledge.bigenius-x.com/hs-fs/hubfs/image-png-May-25-2023-01-14-25-7835-PM.png?width=469&height=752&name=image-png-May-25-2023-01-14-25-7835-PM.png) ### Example The Data Vault project `Datavault.Mssql` with 106 tables deploys in under 30 seconds to a local SQL Server with Windows authentication. The databases did not exist and were created. The output reads: ``` Running deploy.ps1 ... -> using server name localhost -> using no user name and password ... Processing deployment script in ... ... Creating Table [LC].[LoadConfig_LoadObject]... ... Creating Procedure [LC].[Start_Execution]... ... Deployment validation succeeded. ... Finished ``` ### Redeploy and keep data To deploy a changed project into databases that already hold data: 1. In [Project Settings](https://knowledge.bigenius-x.com/manage-project-settings-and-generator-configuration?hsLang=en), open the Additional Settings. In the Deployment group, clear `Generate Drop Table Statements` in [Deployment Options](https://knowledge.bigenius-x.com/deployment-options-project?hsLang=en). 2. In the Load Control group, set [Deploy LoadControl](https://knowledge.bigenius-x.com/deploy-loadcontrol-project?hsLang=en) to `false`. Keep [Configure LoadControl](https://knowledge.bigenius-x.com/configure-loadcontrol-project?hsLang=en) `true`, so that new load objects are registered. 3. Generate, download and replace the placeholders, then run `.\deploy.ps1`. The deployment then creates only the tables that do not exist yet. Existing tables keep their rows and their columns. The load control keeps its configuration, execution plans and execution history, and new load objects are added. Views are dropped and created again, and stored procedures are altered. Each existing default index reports error 1913, see [Failure symptoms and recovery](https://knowledge.bigenius-x.com/microsoft-sql-server-deployment-2.0#failure-symptoms-and-recovery). Column changes are not applied to existing tables. When a view uses a new or renamed column of an existing table, the view fails. Every view built on it fails as well and is missing after the deployment. The next load stops at the first loader that reads a missing view. To change existing tables, use a delta deployment such as [Microsoft SQL Server - Delta Deployment with Liquibase](https://knowledge.bigenius-x.com/microsoft-sql-server-delta-deployment-with-liquibase-2.0?hsLang=en). A changed [Implementation Type](https://knowledge.bigenius-x.com/implementation-type-model-object?hsLang=en) is deployed as follows. From `Cached` to `Virtual`, the result view reads the source view, and the load object is deactivated. The old table, with its rows, and its loader stay in the database. From `Virtual` to `Cached`, the table and the loader are created, and the load object is added. ### Failure symptoms and recovery #### Each existing default index reports error 1913 Message: `Msg 1913` with `The operation failed because an index or statistics with name '' already exists on table ''.` Cause: With `Generate Drop Table Statements` cleared, the existing tables keep their indexes, and the deployment creates each default index again. Fix: None needed when these are the only errors and the deployment ends with `Deployment validation succeeded.` #### Views fail with invalid column or object names after a redeploy Message: `Msg 207` with `Invalid column name ''.`, then `Msg 208` with `Invalid object name ''.` for the views built on it Cause: The model added or renamed a column of a table that the deployment kept. Fix: Add or change the column in the table, for example with the delta deployment of [Microsoft SQL Server - Delta Deployment with Liquibase](https://knowledge.bigenius-x.com/microsoft-sql-server-delta-deployment-with-liquibase-2.0?hsLang=en), which also creates the views again. #### The deployment script does not find sqlcmd Message: `SqlCmd is not installed, not recognized or the PATH environment variable is not properly configured` Cause: The sqlcmd folder is not in `PATH`. Fix: Add it as described in [Microsoft SQL Server - Target environment](https://knowledge.bigenius-x.com/microsoft-sql-server-target-environment?hsLang=en) and open a new PowerShell 7 window. For failures in other steps, see [Troubleshoot generation, deployment and load failures](https://knowledge.bigenius-x.com/troubleshoot-generation-deployment-and-load-failures?hsLang=en). ### Release differences - Generator 2.2: the deployment ends with `SQL\validate_deployment.sql`, which recompiles every view and stored procedure, see [Generators Release - 2.2.0](https://knowledge.bigenius-x.com/generators-release-2.2.0?hsLang=en). ### Related articles - Previous: [Microsoft SQL Server - Target environment](https://knowledge.bigenius-x.com/microsoft-sql-server-target-environment?hsLang=en), [Generate artifacts](https://knowledge.bigenius-x.com/generate-artifacts?hsLang=en), [Download generated artifacts](https://knowledge.bigenius-x.com/download-generated-artifacts?hsLang=en), [Replace the placeholders in the artifacts](https://knowledge.bigenius-x.com/replace-the-placeholders-in-the-artifacts?hsLang=en) - Next: [Microsoft SQL Server - Load data - Native Load Control](https://knowledge.bigenius-x.com/microsoft-sql-server-load-data-native-load-control-2.0?hsLang=en), [Microsoft SQL Server - Load data - Apache Airflow Load Control](https://knowledge.bigenius-x.com/microsoft-sql-server-load-data-apache-airflow-load-control-2.0?hsLang=en) - Reference: [Microsoft SQL Server - Placeholder values](https://knowledge.bigenius-x.com/microsoft-sql-server-placeholder-values-2.0?hsLang=en), [Deployment Options (Project)](https://knowledge.bigenius-x.com/deployment-options-project?hsLang=en), [Deploy LoadControl (Project)](https://knowledge.bigenius-x.com/deploy-loadcontrol-project?hsLang=en), [Configure LoadControl (Project)](https://knowledge.bigenius-x.com/configure-loadcontrol-project?hsLang=en), [Implementation Type (Model Object)](https://knowledge.bigenius-x.com/implementation-type-model-object?hsLang=en) - See also: [Microsoft SQL Server - Artifacts](https://knowledge.bigenius-x.com/microsoft-sql-server-artifacts-2.0?hsLang=en), [Microsoft SQL Server - Delta Deployment with Liquibase](https://knowledge.bigenius-x.com/microsoft-sql-server-delta-deployment-with-liquibase-2.0?hsLang=en), [Troubleshoot generation, deployment and load failures](https://knowledge.bigenius-x.com/troubleshoot-generation-deployment-and-load-failures?hsLang=en), [Deploy a Project in production](https://knowledge.bigenius-x.com/deploy-a-project-in-production?hsLang=en) - [Getting started](https://knowledge.bigenius-x.com/getting-started?hsLang=en#main-content) - [Guidelines](https://knowledge.bigenius-x.com/getting-started?hsLang=en#guidelines) - [Start working](https://knowledge.bigenius-x.com/getting-started?hsLang=en#start-working) - [Account](https://knowledge.bigenius-x.com/getting-started?hsLang=en#account) - [General overview](https://knowledge.bigenius-x.com/general-overview?hsLang=en) - [Artificial Intelligence](https://knowledge.bigenius-x.com/artificial-intelligence?hsLang=en) - [Application Modules](https://knowledge.bigenius-x.com/application-modules?hsLang=en#main-content) - [Administration](https://knowledge.bigenius-x.com/application-modules?hsLang=en#administration) - [Solutions](https://knowledge.bigenius-x.com/application-modules?hsLang=en#solutions) - [Global Features](https://knowledge.bigenius-x.com/application-modules?hsLang=en#global-features) - [Projects](https://knowledge.bigenius-x.com/application-modules?hsLang=en#projects) - [Branches](https://knowledge.bigenius-x.com/application-modules?hsLang=en#branches) - [Data Connections](https://knowledge.bigenius-x.com/application-modules?hsLang=en#data-connections) - [Dataflow Modeling - Overview](https://knowledge.bigenius-x.com/application-modules?hsLang=en#dataflow-modeling-overview) - [Dataflow Modeling - Wizard Steps](https://knowledge.bigenius-x.com/application-modules?hsLang=en#dataflow-modeling-wizard-steps) - [Dataflow Modeling - Terms](https://knowledge.bigenius-x.com/application-modules?hsLang=en#dataflow-modeling-terms) - [Dataflow Modeling - Term Mapping](https://knowledge.bigenius-x.com/application-modules?hsLang=en#dataflow-modeling-term-mapping) - [Dataflow Modeling - Relationships](https://knowledge.bigenius-x.com/application-modules?hsLang=en#dataflow-modeling-relationships) - [Dataflow Modeling - Data Quality](https://knowledge.bigenius-x.com/application-modules?hsLang=en#dataflow-modeling-data-quality) - [Dataflow Modeling - Indexes](https://knowledge.bigenius-x.com/application-modules?hsLang=en#dataflow-modeling-indexes) - [Relationship Modeling](https://knowledge.bigenius-x.com/application-modules?hsLang=en#relationship-modeling) - [Generate Artifacts](https://knowledge.bigenius-x.com/application-modules?hsLang=en#generate-artifacts) - [Project Settings](https://knowledge.bigenius-x.com/application-modules?hsLang=en#project-settings) - [Data Marketplace](https://knowledge.bigenius-x.com/application-modules?hsLang=en#data-marketplace) - [Generators](https://knowledge.bigenius-x.com/generators?hsLang=en#main-content) - [Fabric Warehouse](https://knowledge.bigenius-x.com/generators?hsLang=en#fabric-warehouse) - [Fabric Lakehouse](https://knowledge.bigenius-x.com/generators?hsLang=en#fabric-lakehouse) - [Databricks](https://knowledge.bigenius-x.com/generators?hsLang=en#databricks) - [Snowflake](https://knowledge.bigenius-x.com/generators?hsLang=en#snowflake) - [Microsoft SQL Server](https://knowledge.bigenius-x.com/generators?hsLang=en#microsoft-sql-server) - [Oracle](https://knowledge.bigenius-x.com/generators?hsLang=en#oracle) - [Artifacts](https://knowledge.bigenius-x.com/generators?hsLang=en#artifacts) - [Replace Placeholders](https://knowledge.bigenius-x.com/generators?hsLang=en#replace-placeholders) - [Target solution environment](https://knowledge.bigenius-x.com/generators?hsLang=en#target-solution-environment) - [Deployment](https://knowledge.bigenius-x.com/generators?hsLang=en#deployment) - [Deployment with an Azuze DevOps pipeline](https://knowledge.bigenius-x.com/generators?hsLang=en#deployment-with-an-azuze-devops-pipeline) - [Delta Deployment](https://knowledge.bigenius-x.com/generators?hsLang=en#delta-deployment) - [Load control environment](https://knowledge.bigenius-x.com/generators?hsLang=en#load-control-environment) - [Load data with a native load control](https://knowledge.bigenius-x.com/generators?hsLang=en#load-data-with-a-native-load-control) - [Load data with Apache Airflow](https://knowledge.bigenius-x.com/generators?hsLang=en#load-data-with-apache-airflow) - [Model Object Type](https://knowledge.bigenius-x.com/generators?hsLang=en#model-object-type) - [Properties](https://knowledge.bigenius-x.com/generators?hsLang=en#properties) - [Default Terms](https://knowledge.bigenius-x.com/generators?hsLang=en#default-terms) - [Discovery application](https://knowledge.bigenius-x.com/discovery-application?hsLang=en#main-content) - [Discovery configurations](https://knowledge.bigenius-x.com/discovery-application?hsLang=en#discovery-configurations) - [Best practices](https://knowledge.bigenius-x.com/best-practices?hsLang=en#main-content) - [Modeling Approaches](https://knowledge.bigenius-x.com/best-practices?hsLang=en#modeling-approaches) - [Use Cases](https://knowledge.bigenius-x.com/best-practices?hsLang=en#use-cases) - [Business Rules](https://knowledge.bigenius-x.com/best-practices?hsLang=en#business-rules) - [Data Quality Rules](https://knowledge.bigenius-x.com/best-practices?hsLang=en#data-quality-rules) - [FAQs](https://knowledge.bigenius-x.com/faqs?hsLang=en) - [Product Release Notes](https://knowledge.bigenius-x.com/product-release-notes?hsLang=en#main-content) - [SaaS Application](https://knowledge.bigenius-x.com/product-release-notes?hsLang=en#saas-application) - [Discovery application](https://knowledge.bigenius-x.com/product-release-notes?hsLang=en#discovery-application) - [Generators](https://knowledge.bigenius-x.com/product-release-notes?hsLang=en#generators) - [Legal Documents](https://knowledge.bigenius-x.com/legal-documents?hsLang=en#main-content) - [Current legal docs](https://knowledge.bigenius-x.com/legal-documents?hsLang=en#current-legal-docs) - [Software Product and Limits](https://knowledge.bigenius-x.com/legal-documents?hsLang=en#software-product-and-limits) - [Contact us](https://knowledge.bigenius-x.com/kb-tickets/new?hsLang=en) - [Customer Portal](https://knowledge.bigenius-x.com/customer-portal) - [Sign in](https://knowledge.bigenius-x.com/_hcms/mem/login) [![biGENIUS logo](https://knowledge.bigenius-x.com/hubfs/TRI_Logo_biGENIUS_2020_RGB-1.svg "biGENIUS logo")](https://www.bigenius-x.com) All rights reserved © 2025, biGENIUS AG