In this section I will try to show how can we create a database using batch file. This batch file will internally execute sql script placed in the directory.

Introduction

Once we are familier with creating batch files, now it is time to perform action using it. In this example I will try to explain about how can we generate a database using batch file. Here I am taking SQL SERVER database and will perform database generation using SQLCMD command. I will use few of features of this command in this example. For more information on SQLCMD command please visit msdn.

Using the Code

Let's go step by step. If you are not aware about how to create a batch file, please click on this link.

Step 1. Create batch file

I am creating a batch file and naming it as runsqlscript.bat

Step 2. Create sql script

Create a sql script file and put your sql command in it to generate database. I am creating script file and naming it as script.sql. Put both batch file and sql script file in the same folder for your convenience. Below is the code for sql script which I have created for this example.

Here I have simply written script to create a database with a test table.

USE [master]
GO

IF  EXISTS (SELECT name FROM sys.databases WHERE name = N'MyTestDatabase')
DROP DATABASE [MyTestDatabase]
GO

USE [master]
GO

/****** Creating Database [MyTestDatabase]  ******/
PRINT 'Creating Database...'
CREATE DATABASE [MyTestDatabase]
GO
PRINT 'Database created successfully...'
GO
PRINT 'Selecting database instance...'
GO
USE [MyTestDatabase]
GO
PRINT 'Creating database tables...'
CREATE TABLE [TESTTABLE] (ID INT, NAME VARCHAR(50), VALUE VARCHAR(50))
GO
PRINT 'Tables created successfully...'
GO
PRINT 'Task successful'

Step 3. Call Script in batch file

Once we are done till this step, now it's time to call sql script file in batch file. To execute sql script use SQLCMD command.

sqlcmd -S <ComputerName>\<InstanceName> -i <ScriptPath>

Below are the command attributes I used in my example:
-E use trusted connection
-S Server detail
-i Input file

For more detail please click on this link

Below is the code I used in my batch file.

@ECHO =========================Dot Net Sample Batch File Example====================
@ECHO This example will create a database in Sql Server. It will simply execute the sql script created in directory...
@ECHO Executing sql script.......

sqlcmd -E -S localhost -i F:\DotNetConceptGenerateDatabaseWithBatchFile\script.sql

pause

Hope this can help you.