Cannot Attach Database

Lin · Post by **Lin** » Tue Apr 17, 2007 12:41 pm

I can not get my databases to attach. I am trying to install on a Virtual PC.
In the Application Runtimes, I have Microsoft SQL Server Express 2005 with Service Pack 2 selected. I am setting the service name to MSSQL$TEST and my Setup Command Line is: SECURITYMODE=SQL DISABLENETWORKPROTOCOLS=0 SAPWD="password" INSTANCENAME="TEST" ADDLOCAL=ALL.

I add my mdf and ldf files to the $PROGRAMFILES$\\Microsoft SQL Server\\MSSQL.1\\MSSQL\\Data folder

In MS SQL Server, I have server: localhost, Instance: TEST, Database: NV, User: sa, Password: password, Create database if absent is not checkec, and my SQL Script is:
EXEC sp_attach_db @dbname = N'NV',
@filename1 = N'$PROGRAMFILES$\\Microsoft SQL Server\\MSSQL.1\\MSSQL\\Data\\NV.MDF',
@filename2 = N'$PROGRAMFILES$\\Microsoft SQL Server\\MSSQL.1\\MSSQL\\Data\\NV_LOG.LDF'

I have also tried:

USE [master]
GO
CREATE DATABASE [NV] ON
( FILENAME = N'$PROGRAMFILES$\\Microsoft SQL Server\\MSSQL.1\\MSSQL\\Data\\NV.MDF' ),
( FILENAME = N'$PROGRAMFILES$\\Microsoft SQL Server\\MSSQL.1\\MSSQL\\Data\\NV_LOG.LDF' )
FOR ATTACH ;
GO

and

EXEC sp_attach_db 'NV' , '$PROGRAMFILES$\\Microsoft SQL Server\\MSSQL.1\\MSSQL\\Data\\NV.MDF' , '$PROGRAMFILES$\\Microsoft SQL Server\\MSSQL.1\\MSSQL\\Data\\NV_LOG.LDF'

I have also used the Set Access Control to set $PROGRAMFILES$\\Microsoft SQL Server to Read/Write for Everyone

In my Configuration Manager I have also set the SQL Server (TEST) built-in account to Local System.

I have been working on this for many many hours and would really appreciate any help!!

Thanks

neillans · Post by **neillans** » Wed Apr 18, 2007 9:48 am

Never tried that approach myself, I tend to just use backups and restore them... or create the database from SQL if its to be a blank one.

Lin · Post by **Lin** » Thu Apr 19, 2007 3:43 pm

Anyone else have any ideas why I am unable to attach a database.

Lin · Post by **Lin** » Thu Apr 19, 2007 3:51 pm

I should add that I can attach the databases with the script above using the sqlcmd and also I have created an .exe that connects to the server and attaches them that I run using the Run Program.

jimo · Post by **jimo** » Thu Apr 19, 2007 7:08 pm

Try writing the SQL Script out to a file using "Write To Text File" then run it using "Run Program As" calling either osql.exe or sqlcmd.exe.

Be sure to include a command line switch to have sqlcmd or osql to write and output file (Enter either on at the command prompt and add /? to get a complete list of command line switches)

An example of my osql.exe command line follows;

Code: Select all

Run Program $OSQL_FULL_PATH$ -S$CONFIG_SQL_SERVER_NAME$ -dmaster -E -i$TARGETDIR$\\Data\\ProdInst.sql -o$TARGETDIR$\\Data\\ProdInst.log (WAIT)

Here is an example using Write to text file;

Code: Select all

Write into Text File $TARGETDIR$\\Data\\ProdInst.sql from Value EXEC sp_attach_db @dbname = N'$PRODDATABASENAME$',@filename1 = N'$PRODTARGET$\\mdpmproduction.mdf' (at end of file)
Write into Text File $TARGETDIR$\\Data\\ProdInst.sql from Value GO (at end of file)

Please note in the above I do NOT try to attach the log file, instead I let SQL Server complain silently (written to the output file) about having to create a new lof file.

Hope this helps.

Lin · Post by **Lin** » Tue Apr 24, 2007 4:03 pm

I am curious, how come attaching this way works (jimo's suggestion) but the attach doesn't work when you use the MS SQL Server dialog.

Thanks

Lin · Post by **Lin** » Wed May 09, 2007 1:40 pm

Resolution: You can't have anything in the database field on the MS SQL Server form.

JerryFleck · Post by **JerryFleck** » Fri Jan 29, 2010 12:52 am

Thanks Lin, your post helped me solve a similar situation.