September 13, 2003 at 9:51 pm
Hello, The short story is that we were trying to update to windows 2000 sp4 and the server was corrupted. It would not boot. So, we reinstalled the OS and SQL Server 7. Now, I have one MDF file which I need to reattach. How do I do this? I've been reading about sp_attach_db and sp_attach_single_file_db so I know that it's possible but I need a quick step-by-step as to how to access the command line and what to do, caveats, etc. I'd like to have things back up and running by Monday AM so if there is a gentle reader who sees this cry for help and can send me an email concerning the fix I would greatly appreciate it. THANKS!
Eben Yong
eben_yong@hpsm.org <--work (I prefer responses, here)
yonglove@yahoo.com <--personal
Eben Yong
eben_yong@hpsm.org
September 13, 2003 at 10:33 pm
Here's a start:
1) Copy the mdf to your data folder.
2) Open Query Analyzer, connect to your server, set the database to master.
3) Type EXEC sp_attach_single_file_db @dbname = 'yourdbname here',
@physname = 'complete file path\yourmdfgoeshere.mdf', then run it.
4) That should create a new log file and attach the mdf.
5) If you've had to recreate any sql logins used by that database you will have to fix them. Run sp_change_users_login 'report' to get a list, then run sp_change_users_login 'autofix', 'login' for each one that is report.
I think that will do it. Be sure to run a backup once you get it all going.
Andy
September 15, 2003 at 8:11 am
Ok, I tried it... but it didn't work!! Help!
exec sp_attach_single_file_db @dbname = 'eVision_Data', @physname = 'E:\MSSQL7\DATA\eVision_Data.MDF'
Server: Msg 945, Level 14, State 2, Line 1
Database 'eVision_Data' 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 'eVision_Data'. CREATE DATABASE is aborted.
Eben Yong
Eben Yong
eben_yong@hpsm.org
September 15, 2003 at 8:25 am
quote:
exec sp_attach_single_file_db @dbname = 'eVision_Data', @physname = 'E:\MSSQL7\DATA\eVision_Data.MDF'
Are you sure that's the complete path? It's usually something like:
E:\Programs\Microsoft SQL Server\......
-SQLBill
September 15, 2003 at 8:28 am
Yes, it's the complete path. I used 'cut & paste'. I've been reading about this problem over the Internet. Apparently there may be some serious problems that I might need Microsoft PSS to assist with. But I hope that can be avoided through this forum. Thanks.
Eben Yong
Eben Yong
eben_yong@hpsm.org
September 15, 2003 at 10:30 am
Did you DETACH the database first? This is required for an ATTACH to work.
What do you mean you did 'cut and paste'. Do you mean you MOVED the data files to a new location? The problem might be with the header of the files.
You might give this a try:
Use Enterprise Manager. Drill down to Databases. Right click on Databases, go to All Tasks, Attach Database. Browse to the datbase file, make sure you assign the proper DBO and select OK. Does that attach your database?
If not, the problem most likely is that your .mdf file is corrupt.
-SQLBill
September 15, 2003 at 10:34 am
No the database was NOT detached. We were attempting to update the server with Windows critical updates and the server somehow became corrupted. We reinstalled everything and are trying to reattach a solitary MDF file under SQL Server 7.
When I say cut & paste, I just mean that I know that the physical path is correct.
Under SQL Server 7, the only options I get are: Backup & Restore DB, Import & Export DB.
Eben Yong
Eben Yong
eben_yong@hpsm.org
September 15, 2003 at 12:46 pm
At this point I believe your only option is a call to Microsoft.
It's kinda late for this, but the experience you are going through is why I always stop all my SQL Services and also detach databases before I make changes/reboot my server.
-SQLBill
September 15, 2003 at 5:02 pm
You might try to use CREATE DATABASE ... FOR ATTACH. But basically what you really want to do is to simply restore a backup of the database. sp_Attach... only works on databases that have been detached with sp_detach_db.
Gary Johnson
Microsoft Natural Language Group
DBA, Sr. DB Engineer
Gary Johnson
Microsoft Natural Language Group
DBA, Sr. DB Engineer
This posting is provided "AS IS" with no warranties, and confers no rights. The opinions expressed in this post are my own and may not reflect that of my employer.
September 15, 2003 at 5:22 pm
Not quite the same, but this might help (use caution!):
http://www.sqlservercentral.com/columnists/awarren/attachanddetachagain.asp
Andy
September 15, 2003 at 5:49 pm
Very nice article Andy! I think I would rather restore the backup that I would have created just prior to installing SP4 though as I think that would simply be easier and less nerve wracking! 🙂
Gary Johnson
Microsoft Natural Language Group
DBA, Sr. DB Engineer
Gary Johnson
Microsoft Natural Language Group
DBA, Sr. DB Engineer
This posting is provided "AS IS" with no warranties, and confers no rights. The opinions expressed in this post are my own and may not reflect that of my employer.
September 16, 2003 at 7:23 am
Here goes a 'stab in the dark'....
I think I missed an important part of your thread that might have part of the solution.
You say you were updating to Win2K SP4. So your database was only at Win2K SP3 when it failed. What did you rebuild the server to prior to trying to attach the database?
If you built it to Win2K SP4, the attach probably won't work because the header shows it should be Win2K SP3.
Try building to Win2K SP3, attaching the database and then applying SP4.
-SQLBill
September 16, 2003 at 8:50 am
I took your advice and called Microsoft PSS. They helped me to reattach the database and even waived the support fee when the heard that the initial cause for the problem was an attempt to perform a windows critical update. The resolution details are fuzzy but the concept is simple:
create a test database
copy the source MDF on top of the test MDF
this puts the test DB in suspect mode
then rebuild the log
back up the DB
restore it as your original DB in the physical locations you desire (MDF & LDF)
You need to perform things like override configuration and manually update sysdatabases (the status field) of the target DB, do 'dbcc checkdb' and things like that.
All in all--pretty easy and straightforward--if you are a good SQL Server Admin--which I suppose I'm on the road to becoming, now!!
Eben Yong
Eben Yong
eben_yong@hpsm.org
September 16, 2003 at 11:30 am
Yes,
I just found this work around myself. Here are the steps.
create a new database by same name and same physical filenames as the old ones ( save out the old mdf file somewhere else for now)
Stop SQL Server and replace the newly created mdf file with the old one that has your data
start SQL Server , put the database in emergency mode, restart SQL Server and then run
DBCC rebuild_LOG
Then run checkdb for consistency as deleting the log can introduce the risk of corrupting the database
Gary Johnson
Microsoft Natural Language Group
DBA, Sr. DB Engineer
Gary Johnson
Microsoft Natural Language Group
DBA, Sr. DB Engineer
This posting is provided "AS IS" with no warranties, and confers no rights. The opinions expressed in this post are my own and may not reflect that of my employer.
September 16, 2003 at 11:44 am
Gary,
I heard this solution before and someone said this could cause database inconsistency.
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply