sp_attach_db problem

Posted on 2007-10-09
Last Modified: 2010-05-18
I successfully detached a database from the server so that I could dispose of the large log file that had filled up the disk.
I then proceeded to re-attach the database, using the following query in MSSQL Server 7
exec sp_attach_db @dbname = 'Stamps', @filename1 = 'D:\MSSQL7\Data\Stamps_Data.MDF'

this gives the following error, how can I re-attach the database ? ( I have double checked the file name and path)
I have also tried the following but get the same error
exec sp_attach_db @dbname = N'Stamps', @filename1 = N'D:\MSSQL7\Data\Stamps_Data.MDF'

Server: Msg 945, Level 14, State 2, Line 1
Database 'Stamps' 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 'Stamps'. CREATE DATABASE is aborted.

I have also posted in SQL 2005 because SQL server 7 is not listed in ee ?
Question by:alcindor
    LVL 5

    Expert Comment

    Aside from the fact that this is not the safest way to shrink your log file have you tried using the Enterprise Manager to attach the database?
    LVL 75

    Accepted Solution

    whenever you use the sp_Attach_db to attach the database, you need to provide the entire files used by that db (mdf, ldf and or ndf), in your case you are missing at least the ldf file

    exec sp_attach_db @dbname = N'Stamps', @filename1 = N'D:\MSSQL7\Data\Stamps_Data.MDF',  

    @filename2 = N'D:\MSSQL7\Data\Stamps_log.LDF' --- check whether the path is correct

    Now if you just have the MDF file, use sp_Attach_Single_File_Db instead of Sp_Attach_DB

    LVL 2

    Author Comment

    aneeshattingal, I get the same error message when I use sp_attach_single_file_db ???
    I have re-booted the server but still no luck. Is there any other way that I can restorre the database from the MDF file ?


    Crag, There is no way of attaching a databse with Entrprise manager that ships with SQL server 7
    LVL 75

    Expert Comment

    by:Aneesh Retnakaran
    LVL 38

    Expert Comment

    by:Jim P.
    If the ldf file is gone, at this point I think it is a lost cause.

    I was never able to get a DB to attach with the MDF alone in SQL 7.

    At this point your only real hope is to restore from backup.

    For future reference change your database's recovery mode to 'Simple' and it will automatically truncate the transaction log.
    LVL 2

    Author Comment

    aneeshattingal, I re-attached the database with sp_attach_db and the original log file that I had moved rather than deleted.
    I then detached and then did sp_attach_single_file_db and it worked the second time.

    Thanks for your prompt help, I can go home now !

    Best tegards,


    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How your wiki can always stay up-to-date

    Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
    - Increase transparency
    - Onboard new hires faster
    - Access from mobile/offline

    by Mark Wills Attending one of Rob Farley's seminars the other day, I heard the phrase "The Accidental DBA" and fell in love with it. It got me thinking about the plight of the newcomer to SQL Server...  So if you are the accidental DBA, or, simp…
    Shadow IT is coming out of the shadows as more businesses are choosing cloud-based applications. It is now a multi-cloud world for most organizations. Simultaneously, most businesses have yet to consolidate with one cloud provider or define an offic…
    Video by: Steve
    Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
    Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

    759 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    11 Experts available now in Live!

    Get 1:1 Help Now