[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now


sp_attach_db problem

Posted on 2007-10-09
Medium Priority
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

Expert Comment

ID: 20041068
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

Aneesh Retnakaran earned 2000 total points
ID: 20041082
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


Author Comment

ID: 20041814
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
Restore individual SQL databases with ease

Veeam Explorer for Microsoft SQL Server delivers an easy-to-use, wizard-driven interface for restoring your databases from a backup. No expert SQL background required. Web interface provides a complete view of all available SQL databases to simplify the recovery of lost database

LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 20041861
LVL 38

Expert Comment

by:Jim P.
ID: 20042638
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.

Author Comment

ID: 20042777
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,


Featured Post

NEW Veeam Backup for Microsoft Office 365 1.5

With Office 365, it’s your data and your responsibility to protect it. NEW Veeam Backup for Microsoft Office 365 eliminates the risk of losing access to your Office 365 data.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Backups and Disaster RecoveryIn this post, we’ll look at strategies for backups and disaster recovery.
Instead of error trapping or hard-coding for non-updateable fields when using QODBC, let VBA automatically disable them when forms open. This way, users can view but not change the data. Part 1 explained how to use schema tables to do this. Part 2 h…
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…
In this video, Percona Director of Solution Engineering Jon Tobin discusses the function and features of Percona Server for MongoDB. How Percona can help Percona can help you determine if Percona Server for MongoDB is the right solution for …

834 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