How to run SQL Scripts using Command Prompt or in a batch file


How to run sql script using command prompt
or
How to deploy database in production environment
or 
How to create database using batch file
or 
How to run sql scripts stored in a folder
or 
Automating Database Creation
or
How to call various SQL Scripts from Master SQL Script (Master - Child Scripts)

I have came across various clients, where they would not be have SQL Server Management Studio installed on their production box and if you want to run SQL Scripts using command prompt then you can follow below example

Step1 - Create a batch file and when you execute this batch file then it will run create_db.sql file against Master database which is stored in C:\Mihir\Scripts folder and creates an empty database

SQLCMD -E -dmaster -i C:\Scripts\create_db.sql

PAUSE

Step2 - Here I have created a Master SQL Script (Create_DB.sql), which will call various child Scripts

/* BUILD A DATABASE */

-- This is the Master Script
SET NOCOUNT ON
GO

PRINT 'CREATING DATABASE'
IF EXISTS (SELECT 1 FROM SYS.DATABASES WHERE NAME = 'TestDB')
   DROP DATABASE TestDB
GO
CREATE DATABASE TestDB
GO

:On Error exit

:r c:\Scripts\CREATE_TABLES.sql
:r c:\Scripts\TABLE_INSERTS.sql
:r c:\Scripts\CREATE_INDEXES.sql
:r c:\Scripts\CREATE_PROCEDURES.sql

PRINT 'DATABASE CREATE IS COMPLETE'
GO




Step3 - Now various child Scripts will be executed which will create required database objects such as creating tables, inserting records in to required tables, creating Indexes and stored procedures


And once all the scripts are run successfully, you will get Database Create is Complete and you can verify the database creation.

Source File as attached
https://drive.google.com/file/d/0B40RqOcLP1O4VTRtQTdCNmtmZ1U/edit?usp=sharing


Comments

Post a Comment

Popular posts from this blog

Calculating Age of the person in T-SQL

One of common calculations, we have to do in ETL is calculating age of the person from the date attribute And this is how I have calculated Age of the person. Select BirthDate , CASE             WHEN Month ( GetDate ()) < Month ( c . [BirthDate] )                 THEN DateDiff ( yy , c . [BirthDate] , GetDate ()) - 1             WHEN Month ( GetDate ()) = Month ( c . [BirthDate] )             AND Day ( GetDate ()) < Day ( c . [BirthDate] )                 THEN DateDiff ( yy , c . [BirthDate] , GetDate ()) - 1             ELSE DateDiff ( yy , c . [BirthDate] , GetDate ())         END AS [Age]               from [dbo] . [DimCustomer] c               order by BirthDate I am sure, there would be other better ways to do this and would love to hear, of how you have calculated age of the person
Read more

How to Troubleshoot Subscription issue in Reporting Services

Tips on troubleshooting Subscription issue in Reporting Services 1 – Look at the Reporting Services Log File Error Message from Log File: Email Provider has no server or pickup directory specified, Microsoft.ReportingServices.Diagnostics.Utilities.ServerConfigurationErrorException: The report server has encountered a configuration error. ;   ERROR: Throwing Microsoft.ReportingServices.Diagnostics.Utilities.ServerConfigurationErrorException: Email Provider has no server or pickup directory specified, Microsoft.ReportingServices.Diagnostics.Utilities.ServerConfigurationErrorException: The report server has encountered a configuration error. ;   INFO: Notification 20f7b482-eb74-4b51-9e53-9f0609034e72 completed.  Success: False, Status: Failure writing file Startup :  The report server has encountered a configuration error. , DeliveryExtension: Report Server FileShare, Report: Startup, .......      2 - Run below query against ReportServer Database and this 
Read more

MDX Queries - Current Day - Month and Year

Problem: Query to get Current Day - Month and Year in MDX /* MDX for retrieving current Year */ WITH MEMBER [Measures].[Current Year] AS     VBAMDX. Format (VBAMDX.Now(), "yyyy" ) SELECT   {[Measures].[Current Year]} ON COLUMNS FROM [Analysis Services Tutorial]; /* Current Month */ WITH MEMBER [Measures].[Current Month] AS     VBAMDX. Format (VBAMDX.Now(), "MM" ) SELECT   {[Measures].[Current Month]} ON COLUMNS FROM [Analysis Services Tutorial]; /* MDX for retrieving current Date */ WITH MEMBER [Measures].[Current Date] AS     VBAMDX. Format (VBAMDX.Now(), "dd" ) SELECT   {[Measures].[Current Date]} ON COLUMNS FROM [Analysis Services Tutorial];
Read more