Using sqlcmd to Run a T-SQL Script

In managing a development environment it become necessary to create many builds of a database as well as creating new builds for various prototyping and development activities. I prefer the flexibility that the command line offers.

Using sqlcmd to build a database

This should actually be more appropriately titled how to run T-SQL code at the command line prompt. The following is the build script for creating a new database. Building in code gives us more flexibility in my opinion. For one, it is easier to clone a script. Second, since you are working directly with the source and not creating it though a GUI it is easier to move it into a source control system.

The Database Build Script

If all you have ever done is use the SQL GUI you may not even realize that source code gets generated for many of the functions you perform. Here is the source code for a database I am going to build on my local drive. You can build to any network SQL Server that you have file system access to. Of course you will also need to have access to build a database on a given SQL Server.

I assume that you have the ability to build databases. If you are working on a shared SQL Server that is administered by someone else this will likely not be the case. If you are working on a local version of SQL Server and have installed the software correctly you should have administrative access.

The code below contained in a file named db_TNGMGR.sql.

USE MASTER 
GO 

CREATE DATABASE tngmgr ON  
  PRIMARY  
  (  
   NAME       = tngmgr_Data , 
   FILENAME   = 'c:\ss2008\MSSQL.SQLEXPRESS\MSSQL\DATA\tngmgr_Data.MDF' , 
   SIZE       = 10MB      , 
   MAXSIZE    = UNLIMITED , 
   FILEGROWTH = 10MB 
  ) 
  LOG ON  
  ( 
   NAME       = tngmgr_Log , 
   FILENAME   = 'c:\ss2008\MSSQL.SQLEXPRESS\MSSQL\LOG\tngmgr_Log.LDF'  ,
   SIZE       = 10MB      , 
   MAXSIZE    = UNLIMITED , 
   FILEGROWTH = 10MB   
  ) 
GO

I also want to note that I do not have any drop database conditional in my code. For building databases and tables I prefer NOT to use such logic. The main reason is that it can be quite dangerous to do so. It is not uncommon to have connections to multiple SQL Server environments. Unknowingly one can run a script like this while in the wrong environment. By not adding the conditional if I ran this script in an environment where the database already existed it would fail. This is a good thing indeed. I have heard too many horror stories from collegues that have inadvertantly blown away a build. If I need to delete a database or a table(s) this is a seperate activity all together!

You can use this code as a template to build new databases.

Creating the Database Using sqlcmd

>sqlcmd -i c:\ss2008s\tngmgr\db_TNGMGR.sql

The -i argument is the input switch. What follows the -i switch is the reference to the file I wish to run.

 

 

 


 Collapse All    Expand All
Custom Search

SQL Server Information and Resources