Monday, February 20, 2012

NEWB: Moving DBs to new install of SQL Serv. w/o sp_detach_db

Hi,
After a power outage (and UPS failure), my boss's SQL Server 2000 won't
start (or crashes quickly or something). We don't have any backups, as it's
a test server. Is there a way to import the database files into a new SQL
installation? By "installation," I mean either a re-installed SS, a second
installation on the same PC, or maybe even on another PC. In another words,
given only the files sitting on the harddrive, is there a way to "import"
the DBs in those files into SS?

Sp_attach_db would probably be the answer, except it seems to require
running sp_detach_db...which I cannot do, as SS is not working.

Thanks in advance,

Jay[posted and mailed, please reply in news]

Jay (twentycavities@.hotmail.com) writes:
> After a power outage (and UPS failure), my boss's SQL Server 2000 won't
> start (or crashes quickly or something). We don't have any backups, as
> it's a test server. Is there a way to import the database files into a
> new SQL installation? By "installation," I mean either a re-installed
> SS, a second installation on the same PC, or maybe even on another PC.
> In another words, given only the files sitting on the harddrive, is
> there a way to "import" the DBs in those files into SS?
> Sp_attach_db would probably be the answer, except it seems to require
> running sp_detach_db...which I cannot do, as SS is not working.

sp_attach_db would indeed be the first thing to try. Since you did
not detach the database cleanly, it may not work. But it's worth to
give it a try. You most likely need both the data files and the log
files.

The other option to try is sp_attach_single_file_db. but if
sp_attach_db does not work, I would not place my bets on this one.

If that files, there is yet a way, but I wiill not post this method
here as it is too dangerous. Suffice to say that while you will be
able to access the database file this, you will get it exactly in
the state it was in at the time of the crash. So if this was a state
of inconsistency in the midst of a transaction, you have a mess of a
database.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||In article <mHLCb.17818$Vg3.10491@.fe3.columbus.rr.com>,
twentycavities@.hotmail.com says...
> Sp_attach_db would probably be the answer, except it seems to require
> running sp_detach_db...which I cannot do, as SS is not working.

I got hung up on the same thing, but as Erland points out, it will
probably work. It did for me.

-- Rick

No comments:

Post a Comment