My company has a number of SQL installation scripts that build a fresh copy of our database. Each script relates to a specific SQL task. One script builds the database, another builds the tables, etc. I call SQLCMD repeatedly to execute each script. Is it possible to concatenate these scripts together and execute them with a single call to SQLCMD?
Yes it is. SQLCMD offers the :r command. When :r is encountered in a SQL script, it essentially tells the SQLCMD utility to include the file referenced into the calling script. This can afford you the opportunity to break up a single script into multiple scripts that represent functional areas of the database or to replace multiple SQLCMD calls with a single call to a main SQL file. Furthermore, using the :r command to execute multiple scripts in a single batch allows you define a single set of variables which can be seen by all the included scripts (provided there is no intervening GO terminator). SQLCMD was introduced in SQL Server 2005 and is the replacement for osql which Microsoft will be deprecating in a future release. If you're not familiar with SQLCMD, it is a command line utility that can be used to execute T-SQL commands and scripts from the operating system.
NOTE: You can download the sample files via the attached .zip file which you can unzip and use to execute the sample code that will be presented in this tip.
In the forthcoming example, I'll create 5 .sql files representing functional areas of a sample database called MSSQLTIPS. The first script is called CREATE_DB.sql and creates a database on your SQL 2005 Server called MSSQLTIPS. This script then includes 4 scripts (using the :r command) to perform table creation, table inserts, index creation, and stored procedure creation. A .bat file is created to execute SQLCMD from the Windows operating system in order to create a fresh database.
I first create a sub-folder under my C: drive called C:\Scripts. I store the following SQL scripts in this folder.
Script code to create tables
/* SCRIPT: CREATE_TABLES.sql */ PRINT 'CREATING TABLES ' GO
IF OBJECT_ID('EMPLOYEE') IS NOT NULL DROP TABLE DBO.EMPLOYEE GO CREATE TABLE DBO.EMPLOYEE ( EMPLOYEEID INT IDENTITY(1,1) NOT NULL PRIMARY KEY, FIRSTNAME VARCHAR(50), LASTNAME VARCHAR(50) ) GO
IF OBJECT_ID('TIMECARD') IS NOT NULL DROP TABLE DBO.TIMECARD GO CREATE TABLE DBO.TIMECARD ( TIMECARDID INT IDENTITY(1,1) NOT NULL PRIMARY KEY, EMPLOYEEID INT NOT NULL, HOURSWORKED TINYINT NOT NULL, HOURLYRATE MONEY NOT NULL, DATEWORKED DATETIME NOT NULL ) GO
DECLARE @TOTAL_TABLES INT SET @TOTAL_TABLES = 2
Script code to insert data
/* SCRIPT: TABLE_INSERTS.sql */
PRINT 'TOTAL TABLES CREATED = ' + CAST(@TOTAL_TABLES AS VARCHAR) GO PRINT 'INSERTING DATA INTO EMPLOYEE' GO
INSERT INTO DBO.EMPLOYEE (FIRSTNAME, LASTNAME) SELECT 'JOHN', 'DOE' GO INSERT INTO DBO.EMPLOYEE (FIRSTNAME, LASTNAME) SELECT 'JANE', 'DOE' GO INSERT INTO DBO.EMPLOYEE (FIRSTNAME, LASTNAME) SELECT 'JEFF', 'DOE' GO
Script code to create indexes
/* SCRIPT: CREATE_INDEXES.sql */ PRINT 'CREATING INDEXES' GO
IF NOT EXISTS (SELECT 1 FROM SYS.INDEXES WHERE NAME = 'IX_EMPLOYEE_LASTNAME') CREATE INDEX IX_EMPLOYEE_LASTNAME ON DBO.EMPLOYEE(LASTNAME, FIRSTNAME) GO IF NOT EXISTS (SELECT 1 FROM SYS.INDEXES WHERE NAME = 'IX_TIMECARD_EMPLOYEEID') CREATE INDEX IX_TIMECARD_EMPLOYEEID ON DBO.TIMECARD(EMPLOYEEID) GO
Script code to create procedures
/* SCRIPT: CREATE_PROCEDURES.sql */ PRINT 'CREATING PROCEDUREs' GO
IF OBJECT_ID('GET_EMPLOYEE_TIMECARDS') IS NOT NULL DROP PROCEDURE DBO.GET_EMPLOYEE_TIMECARDS GO CREATE PROCEDURE DBO.GET_EMPLOYEE_TIMECARDS @EMPLOYEEID INT AS
SET NOCOUNT ON
SELECT * FROM DBO.EMPLOYEE E JOIN DBO.TIMECARD T ON E.EMPLOYEEID = T.EMPLOYEEID WHERE E.EMPLOYEEID = @EMPLOYEEID ORDER BY DATEWORKED
Script code to create the new database and objects
/* SCRIPT: CREATE_DB.sql */ /* BUILD A DATABASE */
-- This is the main caller for each script SET NOCOUNT ON GO
PRINT 'CREATING DATABASE' IF EXISTS (SELECT 1 FROM SYS.DATABASES WHERE NAME = 'MSSQLTIPS') DROP DATABASE MSSQLTIPS GO CREATE DATABASE MSSQLTIPS GO
Need help. Looking for a script that will do a full backup of all databases. for sql server 2014express. I have to it everyday. So i need instructions to make a batch file that can run the script and do the backup. I am very new to this concept.I can schedule the batch file through windows task schedualar to run automatically everyday on certaintime. any help would be greatly appreciated .. need quick help. I saw some scripts that seems like doing it but file path and srver path not clear to me need explained what i need to change according to my setting in the script
Friday, July 18, 2014 - 2:35:05 PM - Sreenivas Reddy
I am in a situation of extracting data from a database, however, from a single database. I wrote a simple batch script using a for loop to execute all the .sql files in the folder and write the output to a .txt file. The script is doing the job for me. The only problem i observe here is the time it is taking do the job. I have almost 120 queries that needs to be queried for and write the output to external files. The batch file is taking almost 90-100 minutes. I am looking for an alternative to do my job in much faster way. Can someone help me how can i achieve this in much faster way...
Wednesday, January 09, 2013 - 12:18:43 PM - Eric in Sacramento
I followed the example above and was able to create the database, schema, logins and users but when I call the command to create the tables I got an error, its seems it is point to the database master instead of the new database that I created.
Kindly give me an advise on how to deal with this problem.
Tuesday, July 17, 2012 - 2:17:32 PM - Mayur Sharma
sqlcmd -E -S%ServerName% -Q "Create Database HUM" ,,,but problem is its creating database on default path c:\MSSQL\MSSQLinstance\Data..... i want that to be in N:\SQLLog and N:\SQLData ...how it will be done pl. help me///
Try these tipsEnable the TCP/IP poocrtol using the Surface Area Configuration Utility Make sure the TCP/IP poocrtol is enabled in the SQL Server Configuration UtilityMake sure the SQL Server browser is started. Note this stepis optional. It is possible to set the SQL Server instance to use a fixed IP address but this isnon-standard for named instancesMake sure SQL Server and SQL Server Browser are exempted by the firewall on the server machine.This is done by putting sqlservr.exe and sqlbrowser.exe as an exception in thewindows firewall.Note: In order to get things to work. You might need to completely reboot theserver machine after making the changes. There have been reports (thanks P.C.) that starting and stopping theSQL Server and Browser software is not enough.
My same concern. Our scripts will sometimes have errors due to dependencies, etc. The errors are not always critical and require rollback; however, we would like to make sure the error condition is reported and the output is preserved, just in case...
Maybe this could be the next part of this article. :)
Wednesday, May 27, 2009 - 11:35:19 AM - Roberto12cr