data recovery problem: need help with sp_attach_db

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/database 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 <--personalIf you only have the data file, you might get lucky with
sp_attach_single_file_db. For example:
EXEC sp_attach_single_file_db
'MyDatabase',
'C:\MyDataFiles\MyDatabase.mdf'
Depending on the state database at the time of the crash, the attach may
fail because the data file is inconsistent and the log file is required.
In this case, I suggest you restore from backup if possible. If you
have no backup, contact Microsoft PSS.
If the database attaches successfully, you'll need to recreate logins
and remap database. You can use sp_change_users_login to facilitate
this. See the Books Online for details on using these stored procs.
--
Hope this helps.
Dan Guzman
SQL Server MVP
--
SQL FAQ links (courtesy Neil Pike):
http://www.ntfaq.com/Articles/Index.cfm?DepartmentID=800
http://www.sqlserverfaq.com
http://www.mssqlserver.com/faq
--
"Eben Yong" <yonglove@.yahoo.com> wrote in message
news:b9393494.0309131949.86e62f5@.posting.google.com...
> 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/database 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|||I tried it, but it didn't work... help!! Am I 'stuck'?
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.
"Dan Guzman" <danguzman@.nospam-earthlink.net> wrote in message news:<#UnLjVneDHA.3896@.tk2msftngp13.phx.gbl>...
> If you only have the data file, you might get lucky with
> sp_attach_single_file_db. For example:
> EXEC sp_attach_single_file_db
> 'MyDatabase',
> 'C:\MyDataFiles\MyDatabase.mdf'
>|||> I tried it, but it didn't work... help!! Am I 'stuck'?
Yes. I suggest you restore from backup if possible. If you have no
backup, contact Microsoft PSS.
--
Hope this helps.
Dan Guzman
SQL Server MVP
--
SQL FAQ links (courtesy Neil Pike):
http://www.ntfaq.com/Articles/Index.cfm?DepartmentID=800
http://www.sqlserverfaq.com
http://www.mssqlserver.com/faq
--
"Eben Yong" <yonglove@.yahoo.com> wrote in message
news:b9393494.0309150616.48445f1e@.posting.google.com...
> I tried it, but it didn't work... help!! Am I 'stuck'?
> 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.
> "Dan Guzman" <danguzman@.nospam-earthlink.net> wrote in message
news:<#UnLjVneDHA.3896@.tk2msftngp13.phx.gbl>...
> > If you only have the data file, you might get lucky with
> > sp_attach_single_file_db. For example:
> >
> > EXEC sp_attach_single_file_db
> > 'MyDatabase',
> > 'C:\MyDataFiles\MyDatabase.mdf'
> >sql

Data Provider List Missing...very odd SQL 2000

Hey All!

I have an interseting situation with one of my dev machines. (W2k3EE, SQL2k SP4)

All my linked servers stopped working posting this error:

Server: Msg 7302, Level 16, State 1, Line 1
Could not create an instance of OLE DB provider [enter provider name here, e.g. 'SQLOLEDB'.

So...when attempting to drop and recreate the linked server, the Provider pull down in the GUI is completely empty.

YIKES!

I'm thinking of reinstalling MDAC, but I've never seen this in 10 years of DBA'ing.

Have any of you? Any thoughts out there? Registry issues?

Cheers,
OddNever seen anything like it either. I'm running the same combination in my environment, so if you have any solutions, please post.

Some thoughts and/or suggestions:

1. Was the server recently patched?

2. Was any new software (eg. driver package) recently installed?

3. Any entries in the Application or System error log(s)?

4. Do you back up the registry on this machine? Can it be restored?

Regards,

hmscott|||Thanks for the reply...will definitely post what I find...

1 - yeah, SP4
2 - prolly so...the network guys love to drop agents on my dev servers without mentioning it...Alteris monitor, i think this time
3 - nada
4 - heck no, that would be too easy

:)