OSQL Script for Attaching and Detaching SQL Database

• Syed Anis

  Say Hey Kid

  Points: 685

  More actions

  October 15, 2007 at 1:27 pm

  #178537

  I have the OSQL script below:

  SET DATABASEFOLDER=%STRING%\My Documents\TIMS\Databases

  SET DATABASETEMP=%DATABASEFOLDER%

  echo Detaching Databases from Default Instance

  "C:\Program Files\Microsoft SQL Server\90\Tools\Binn\osql" -E -Q "exec sp_detach_db @dbname='EZSalesOrder'"

  echo Database Detached Successfully

  echo Re-attaching Databases into Named Instance

  "C:\Program Files\Microsoft SQL Server\90\Tools\Binn\osql" -S (local)\HES -E -Q EXEC sp_attach_db @dbname = N'EZSalesOrder',

  @filename1 = N'%DATABASETEMP%"\EZSalesOrder_Data.mdf',

  @filename2 = N'%DATABASETEMP%"\EZSalesOrder_Log.mdf'

  First part of the script i.e. detach database is working fine but the attached part (highlighted in brown) of the script is not working. Its give me an error message saying that “Incorrect syntax near ‘,’ ‘filename1’ is not recognized as an internal or external command”. Any thoughts??

  Please help

  Thanks,

  Syed

• Tommy Bollhofer

  SSChampion

  Points: 14940

  More actions

  October 15, 2007 at 3:27 pm

  #742154

  Try -

  declare @cmd varchar(1000), @DatabaseTEMP varchar(1000)

  set @DATABASETEMP = 'C:\program files\Microsoft SQL Server\MSSQL.1\MSSQL\Data'

  set @cmd = 'osql.exe -E -Q "EXEC sp_attach_db ''EZSalesOrder'', ''' + @DATABASETEMP + '\EZSalesOrder_Data.mdf'', ''' + @DATABASETEMP + '\EZSalesOrder_Log.ldf''"'

  print @cmd

  --exec xp_cmdshell @cmd

  Tommy

  Follow @sqlscribe

• sqldba-294117

  Hall of Fame

  Points: 3486

  More actions

  October 21, 2007 at 10:11 pm

  #743993

  try using sqlcmd.exe as isql & osql are deprecated in SQL Server 2005

• Rob Goddard

  SSCrazy

  Points: 2622

  More actions

  October 22, 2007 at 5:41 am

  #744083

  Syed Anis (10/15/2007)

  ---

  I have the OSQL script below:

  SET DATABASEFOLDER=%STRING%\My Documents\TIMS\Databases

  SET DATABASETEMP=%DATABASEFOLDER%

  echo Detaching Databases from Default Instance

  "C:\Program Files\Microsoft SQL Server\90\Tools\Binn\osql" -E -Q "exec sp_detach_db @dbname='EZSalesOrder'"

  echo Database Detached Successfully

  echo Re-attaching Databases into Named Instance

  "C:\Program Files\Microsoft SQL Server\90\Tools\Binn\osql" -S (local)\HES -E -Q EXEC sp_attach_db @dbname = N'EZSalesOrder',

  @filename1 = N'%DATABASETEMP%"\EZSalesOrder_Data.mdf',

  @filename2 = N'%DATABASETEMP%"\EZSalesOrder_Log.mdf'

  First part of the script i.e. detach database is working fine but the attached part (highlighted in brown) of the script is not working. Its give me an error message saying that “Incorrect syntax near ‘,’ ‘filename1’ is not recognized as an internal or external command”. Any thoughts??

  Please help

  Thanks,

  Syed

  The code passed to osql needs to be enclosed in quotes, as in the first call to osql in your code example.

  Try replacing the bold section with:

  "C:\Program Files\Microsoft SQL Server\90\Tools\Binn\osql" -S (local)\HES -E -Q "EXEC sp_attach_db @dbname = N'EZSalesOrder',

  @filename1 = N'%DATABASETEMP%"\EZSalesOrder_Data.mdf',

  @filename2 = N'%DATABASETEMP%"\EZSalesOrder_Log.mdf'"

  --------
  [font="Tahoma"]I love deadlines. I like the whooshing sound they make as they fly by. -Douglas Adams[/font]