Run SQL commands from Windows Batch file OR Attach/Detach database automatically using batch script.
June 29, 2012 16 Comments
Sometimes there is a need to automate certain SQL commands so that you don’t have to physically login and do your same stuff. For ex, you have made an application which requires SQL database. To install the application/software, you simply copy the setup file and install it. But to install database, you have to go through number of steps like copying .mdf and log file to default SQL location, login to SQL Server, detach previous database (if exists) and attach new database. Now it would be OK if you want to install it in one or two PC’s. But what if you want to install in more than 100 PC’s? This is definitely not a good solution.
Now think of having a Windows Batch file where you have command to automatically login to SQL Server, detach database and attach a new one. Quite simple, isn’t it? Please have a look at the solution and Windows Batch file code below.
Steps to follow:
1> Make db_setup folder in c:\
2> Copy your blank databases
3> Copy your batch file that you will create
4> Run that batch file…
See my db_setup folder below!
Database name : ExDB
Setup.bat file
@echo off set host=%computername% @echo ---- Connecting to Server ---- REM -------------********* DETACH DATABASE*********------------- sqlcmd -E -S %host%\SQLEXPRESS -d master -Q "EXEC sp_detach_db [ExDB], [true]" @echo ---- Successfully Connected ---- If exist "C:\Program Files" Set ePath=C:\Program Files If exist "C:\Program Files (x86)" set ePath=C:\Program Files (x86) cd C:\ cd %ePath%\Microsoft SQL Server\MSSQL.1\MSSQL\Data If exist del ExDB.mdf If exist del ExDB_log.ldf @echo ---- Deleted old db ---- cd C:\ cd C:\db_setup Rem -> Navigate to your db_setup folder and copy blank database files to sql data folder! copy "ExDB.mdf" "%ePath%\Microsoft SQL Server\MSSQL.1\MSSQL\Data\ExDB.mdf" copy "ExDB_log.ldf" "%ePath%\Microsoft SQL Server\MSSQL.1\MSSQL\Data\ExDB_log.ldf" @echo ---- Copied new db ---- REM -------------********* ATTACH DATABASE*********------------- sqlcmd -E -S %host%\SQLEXPRESS -d master -Q "EXEC sp_attach_db @dbname=N'ExDB', @filename1 = N'%ePath%\Microsoft SQL Server\MSSQL.1\MSSQL\Data\ExDB.mdf',@filename2 = N'%ePath%\Microsoft SQL Server\MSSQL.1\MSSQL\Data\ExDB_log.ldf'" @echo ---- Attached new db ---- timeout /t 2 /nobreak>nul @echo ---- FINISHED ----
Notes by Parth Dave 
Pingback: SQL: How to Attach database automatically using batch script « raykichi's blog
How can I write a bat file to locate files in a specified folder that are .csv, then create a function that loops through each file, then copies its contents and finally inserts the contents into a dedicated and already configured table within SQL?
I want this to run every hour. All the other error handling will be handled by me, but I need something of this functionality to be able to even think of my next step. I liked your example and it worked first time on my side…..so thank you for proving it could be done
Hello Mate,
If you need to do all this from command prompt, you need to first of all understand what is the capabilities of SQLCMD command. So go to command prompt and type sqlcmd -?. This will tell you what is possible through command. The command below will export all table data to test.csv
Same way, you need to use BCP command (http://msdn.microsoft.com/en-us/library/ms162802.aspx) that will import .csv to your table. Below Command will import c:\test.csv data in YOUR_TABLE
Now final bit is looping though all .CSV files, you would need to create FOR loop that will retrieve files one by one and will pass it to BCP for importing. So go to command prompt and type FOR /?. Command is:
set dir1="C:\Test\AUTOCMD" FOR %%X in ("%dir1%\*.csv") DO echo %%~dpnXHello,
I have the same requirement as above. I need to attach db created from my project to sql database.I am running sql server 2008.
database log path is C:\Program Files\Microsoft SQL Server\MSSQL10_50.WINCC\MSSQL\DATA
what are changes I need to do in above program?
Hi Sudhindra,
Sorry for late reply as I was not in town. Please find your answer below:
Line 8 : See SQLCMD command your self, pretty easy. Change SQL Server,Instance,Database Name
Line 16: Change it to cd %ePath%\Microsoft SQL Server\MSSQL10_50.WINCC\MSSQL\DATA
Lin 18, 19 : Change name to your production database
Line 27,28 : Same as Line 16 change path and database name
Line 34 : Change path, database name
You can even declare another variable and put SQL path in it. See how you go mate.
Regards,
Parth
how can i detach database sql server using batch file…can i know the code
Hi,
You can use the following command to detach SQL database.
-E -> Trusted Connection
-S -> is your Server Name\Instance Name.
-d -> is your database name
-Q -> is your query that you would write in your sql editor to detach database
Just type sqlcmd -? in command prompt for other available options. It’s pretty cool.
Regards,
Parth
i need to use a batch file to detach a database .But i don’t know how to use a batch file
thank you 🙂
Regards,
May-Ann
Hi Dear,
Ok. Right click on windows explorer -> New -> Text Document. Rename it to “TestDetach.bat”. (Batch file has extension .bat. Make sure to remove .txt)
Right click on created batch file and open with Notepad or Notepad++.
Now copy the following code to your TestDetach.bat file:
Just remember to replace Sql Server Name/Instance Name, Database to yours one.
Then Open Command Prompt. ( Click Windows icon -> Run -> type “cmd”.
Now Navigate to your folder where you created batch file. Copy the path from Address bar to Command Prompt. Just right click when you pasting path to command prompt followed by “cd” command. So cd C:\DBSetup\TestDetach.bat.
It will then execute the file and if it succeeded you will see Detached Successfully on command prompt.
Let me know how you go.
Cheers.
Parth
thanks for the code :-), but we need to execute it in batch file not in command prompt
Regards
May-Ann
hi:
how can we call the files in sql using batchfile?
Hi good evening:
HOW CAN WE DETACH SQL SERVER DATABASE USING .BATFILE?
respectfully
Jeddah
Hi May-Ann,
I don’t know what your requirement is but double click on .Bat file and it will execute if you don’t want to run in Cmd Prompt.
Regards,
Parth
Hi Jeddah,
Please elaborate your question. For other question, you can see my second last comment where I created .Bat file and then double click on .Bat file to execute.
Regards,
Parth
hi good evening:
How to detach sql server database using batchfile?
respectfully,
jeddah
good evening:
after we put our files in database, how can we call those files using batchfile and command prompt but my problem is how to use batchfile?