We help IT Professionals succeed at work.

sp_attach_single_file_db problem       SQL7

exec sp_attach_single_file_db @dbname='MailArchiver_200609_IN',
   @physname='X:\MailArc\MailArchiver_200609_in_Data.MDF'

generates------------
Server: Msg 945, Level 14, State 2, Line 1
Database 'MailArchiver_200609_IN' cannot be opened because some of the files could not be activated.
Server: Msg 1813, Level 16, State 2, Line 1
Could not open new database 'MailArchiver_200609_IN'. CREATE DATABASE is aborted.
-----------------------------

http://support.microsoft.com/kb/271223 says this is by design when multiple logs files were used.
This was not the case. LDF's have been deleted.
database was dismounted using    exec sp_detach_db 'mailArchiver_YYYYMM_IN';

the MDF file was then renamed to be the month in question.

exec sp_attach_db @dbname='MailArchiver_200609_IN',
   @filename1='X:\MailArc\MailArchiver_200609_in_Data.MDF'

generates same error.  X drive has 130G spare. MDF is 1.5G.

I can reattach a DB detached yesterday using the singlefile sp.
Comment
Watch Question

Commented:
Your command is correct. And you detached it correctly.

Are you moving the database file from SQL 7 to SQL 7?



CERTIFIED EXPERT

Author

Commented:
actually just offline and online again to the same SQL7 server.
file has moved location.

David ToddSenior Database Administrator
CERTIFIED EXPERT

Commented:
Hi,

Suggest that you detach the database instead of taking it off-line. The attach the database with so_attach_db.

Specify the log file as well.

Cheers
  David
CERTIFIED EXPERT

Author

Commented:
sorry about miscommunication.

I do detach database, change its filename , then move it to an offline storage drive.
Tried moving it back to main storage & re-attach.

This is when the error throws. Not quite the ms-kb one as the Msg 5105 doesnt occur.
CERTIFIED EXPERT
Commented:
i'm in the process of getting around the problem by opening DB in emergency mode  and copying data out.

http://www.spaceprogram.com/knowledge/2002/06/recovering-from-deleted-log-file-on_12.html

I will request question be closed.

Explore More ContentExplore courses, solutions, and other research materials related to this topic.