Sometimes there are instances when I need to drop 6 or 7 databases on my local instance. Currently, I run 3 instances locally, two are for OLTP duties and one is for reporting. One method for dropping the databases that I like is based on just a couple of SQL files and a .bat file. First, I make sure that my replication is turned off on my instances. If replication is not turned off then the below process will error out when trying to drop the database.

SQL FILE CREATION FOR DROPPING AND CREATING THE DATABASES

In my case I need to drop multiple databases so my SQL file resembles:

DROP DATABASE db1

DROP DATABASE db2

DROP DATABASE db3

DROP DATABASE db4

DROP DATABASE db5

DROP DATABASE db6

DROP DATABASE db7

DROP DATABASE db8

Once complete I create a generic directory on my C:\ and saved this file as DropDatabases.sql. The create database script is the same script just replace the drop with the create. Keep in mind on the create database statement you can add more setting defaults to the script but I will not go into those here. You can also tinker around with this script and add some standards such as checking to see if the database exists before dropping and creating etc. This is just an idea up to you on how you use it.

CREATING THE .BAT FILE(s)

I use the sqlcmd utility in my .bat file and make calls to the server instance and specific file. Simply open up a notepad editor and type the following command(s)

sqlcmd -S (local) -i”C:\CI\DropDatabase.sql”

sqlcmd -S (local) -i”C:\CI\CreateDatabase.sql”

Then save the file as DropDatabase.bat and CreateDatabase.bat

The two statements will call either the DropDatabase.sql file or the CreateDatabase.sql file

—————————————————-

I believe the SQLCMD utility is a pretty useful tool that I will eventually incorporate into my continuous integration initiatives. For those of you who know me or follow my blog for a long period of time know that I am Red-Gate fan. Because of this I have many useful tools at my disposal and currently am going to set up a form of CI following some of their practices which can be found here

MSDN provides a thorough listing of SQLCMD syntax and variables you can view here

I enjoy trying different ways of doing things that help daily performance initiatives. Hope this helps someone else in the future.