• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1146
  • Last Modified:

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.
0
Robberbaron (robr)
Asked:
Robberbaron (robr)
  • 3
1 Solution
 
ptjcbCommented:
Your command is correct. And you detached it correctly.

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



0
 
Robberbaron (robr)Author Commented:
actually just offline and online again to the same SQL7 server.
file has moved location.

0
 
David ToddSenior DBACommented:
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
0
 
Robberbaron (robr)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.
0
 
Robberbaron (robr)Author 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.
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now