How to create a SQL-SERVER database from several script files?

Question

I've been struggling with this for several days. I have an application that uses a SQL SERVER database for storing its data. This database has several tables, stored procedures, functions and views.

I used to generate a large database creation script for deployments. Now, I'd like to have one script/file per object, and a main script to run all those scripts.

Using sqlcmd mode, I've made a script that lists all files in a path, and creates another file with the execution of all files from that path.

    --DECLARE PATH
    :SETVAR WorkDir     "C:\scripts\"

    --DECLARE TMP FILES
    :SETVAR WorkFile "listing.tmp"
    :SETVAR RunFile "final_script.txt"

    --DELETE PREVIOUS FILE IF EXISTS
    !!IF EXIST $(WorkDir)$(RunFile) DEL $(WorkDir)$(RunFile)
    !!IF EXIST $(WorkDir)$(WorkFile) DEL $(WorkDir)$(WorkFile)

    --REDIRECT OUT TO WORK FILE
    :OUT $(WorkDir)$(WorkFile)

    --LIST ALL SQL FILES
    !!DIR $(WorkDir)"*.sql" /b/o

    --APPEND EXECUTE COMMAND
    !!FOR /F "usebackq tokens=*" %a in (`dir /b $(WorkDir)*.sql /on`) DO ( @ECHO :r "$(WorkDir)%a">> $(WorkDir)$(RunFile) )

    --EXECUTE "final_script"
    :r $(WorkDir)$(RunFile)

This script will generate another script, that will contain something like this:

    -r: c:\scripts\script_01.sql
    -r: c:\scripts\script_02.sql
    -r: c:\scripts\script_03.sql
    -r: c:\scripts\script_04.sql

The problem that I have is this: I can't find how to automatize the database creation. I'd like to set the database name in this script, and remove any reference to a database name from the path scripts.

I can change this script in order to append the CREATE DATABASE sentence into the final script, in order to have something like this

    CREATE DATABASE [MYDB]
    GO
    USE [MYDB]
    GO
    -r: c:\scripts\script_01.sql
    -r: c:\scripts\script_02.sql
    -r: c:\scripts\script_03.sql
    -r: c:\scripts\script_04.sql

But, the -r sentences will be executed before the database creation, and the scripts will be executed using [master] database.

If I include or create another script with the database creation sentences, I have no way to tell other scripts which database should use.

Any idea?

Best regards.

score 2 · Answer 1 · answered Nov 29 '17 at 21:56

I would highly recommend that you just use SSDT (SQL Server Data Tools) in VS to convert your db into a database project. This will let VS do all of the heavy lifting, not to mention other cool features such as schema & data comparisons.

Take a look here for how to convert your database over. This will create individual scripts for each of your database objects for you. It will only take a very short time to give it a try. Creating a SQL Server Database Project in VS2012

Thank you @Frobozz! I'm taking a look to database projects, and i could automatize a lot of things that i ussed to do manually! — ggconde, Nov 30 '17 at 20:13

How to create a SQL-SERVER database from several script files?

1 Answers1