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,
JayYou could try sp_attach_single_file_db, though I believe that may require a
successful detach as well.
--
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"Jay" <twentycavities@.hotmail.com> wrote in message
news:g2MCb.17882$Vg3.15076@.fe3.columbus.rr.com...
> 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
>
>|||Jay,
I dont see why you could not simply copy the MDF and LDF
files to another machine (or put in a safe place). Then
fix the issue with SQL2K not starting.
You should then be able to attache the database and log
file again via Enterprise Manager (right-click on
Databases/All tasks/Attach Database...).
Ron
>--Original Message--
>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
>
>
>.
>|||I believe all he has to do is attach the databases. Find the old =databases and attach them. I have done this before and in fact will be =doing it again shortly. Attach through the GUI in your other SQL =Manager.
-- George Hester
__________________________________
"Jay" <twentycavities@.hotmail.com> wrote in message =news:g2MCb.17882$Vg3.15076@.fe3.columbus.rr.com...
> 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
> > > >|||>> I believe all he has to do is attach the databases.
This works fine when the databases have been detached successfully.
However, it's not always so easy when the "detach" wasn't clean (as in Jay's
case).
--
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/|||Well I have done it with no detaching. In fact I have to do it again =shortly. The Windows Server crashed and there is no reliable way I know =of to get SQL Server out of there functional and I need the databases. =So as I do it I'll probably remember the corners I had to cut to get it =to work. If he is interested I'll describe the steps here.
-- George Hester
__________________________________
"Aaron Bertrand [MVP]" <aaron@.TRASHaspfaq.com> wrote in message =news:uGW9ANwwDHA.3428@.TK2MSFTNGP11.phx.gbl...
> >> I believe all he has to do is attach the databases.
> > This works fine when the databases have been detached successfully.
> However, it's not always so easy when the "detach" wasn't clean (as in =Jay's
> case).
> > -- > Aaron Bertrand
> SQL Server MVP
> http://www.aspfaq.com/
> >|||> Well I have done it with no detaching.
I think you have been lucky, and this is certainly not guaranteed to work.
--
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/

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