Attach a SQL Server database from a windows batch file.

I had an issue where each day a database would detach itself so I slimmed down another authors post (mentioned at the end of this post) for my needs.

I have only tried this on a Windows 2008 R2 server / SQL Server 2008R2 but i’m sure it would be fine on Windows 2003/SQL Server 2005..

  1. Create a batch file .e.g Attach_db.bat
  2. Copy the contents below into the file and replace the following to your own settings:- SERVERNAME, DATABASE, YOUR DATABASE NAME NO EXTENSION.MDF & DATABASE PATH.
  3. Detach the file from SQL Server and run the file to test it.
@echo off
set host=%computername%

@echo ---- Connecting to Server ----

REM -------------********* ATTACH DATABASE*********-------------

sqlcmd -E -S SERVERNAME\DATABASE -d master -Q "EXEC sp_attach_db @dbname=N'YOUR DATABASE NAME NO EXTENSION.MDF', @filename1 = N'DATABASE PATH\YOUR DATABASE.mdf',@filename2 = N'DATABASE PATH\YOUR DATABASE_log.LDF'"

@echo ---- Attached new db ----

timeout /t 2 /nobreak>nul

This is a slimmed down version from Parth Dave’s excellent post.
Author: http://notesbyparth.wordpress.com/2012/06/29/run-sql-commands-from-windows-batch-file-or-attachdetach-database-automatically-using-batch-script/

 

Leave a Comment

Your email address will not be published.

Follow

Get every new post on this blog delivered to your Inbox.

Join other followers: