Can I recover from the database files?

[thomas.cameron]

Howdy all -

I am *not* a DBA, so please bear with me. A client's Windows 2000
server went belly up yesterday afternoon after getting updates from
Windows Update. Wouldn't come up at all any more. I broke the mirror
for the OS, rebuilt the Windows 2000 installation, and installed MS-SQL
2000 and SP3 so that now the server is configured identically to what
it was before the crash.

Now I have the databases in D:\Program Files\Microsoft SQL
Server\MSSQL\Data. D: is a mirror of the (now broken) old
installation. How can I get the databases into the active installation
of MS-SQL on the C: drive?

I was thinking of creating databases with the same name, shutting down
SQL and copying the files from D: to C: but that sounds too easy.

Any pointers greatly appreciated. Please remember that I am not an
experienced DBA, so use short words and speak slowly... :-)

[Erland Sommarskog]

(th************@gmail.com) writes:

I am *not* a DBA, so please bear with me. A client's Windows 2000
server went belly up yesterday afternoon after getting updates from
Windows Update. Wouldn't come up at all any more. I broke the mirror
for the OS, rebuilt the Windows 2000 installation, and installed MS-SQL
2000 and SP3 so that now the server is configured identically to what
it was before the crash.

Now I have the databases in D:\Program Files\Microsoft SQL
Server\MSSQL\Data. D: is a mirror of the (now broken) old
installation. How can I get the databases into the active installation
of MS-SQL on the C: drive?

I was thinking of creating databases with the same name, shutting down
SQL and copying the files from D: to C: but that sounds too easy.

That would not be the way to do. Rather you should first copy the files
into the place where you want them, and then use sp_attach_db to attach
them to the new server. Keep in mind that you need to copy both MDF and LDF
files. Rather than using sp_attach_db, you can probably find some screen
for this in Enterprise Manager if you prefer.

Now, this may or may not work. If the files were shut down cleanly, it
will work. And since this appeared to have happened after a reboot, there
is a chance that this is the case.

If you get errors on a database, it all gets more complicated. Restoring
from a clean backup could be safest way, and if you also have transaction
log dumps you should be able to come fairly close to the state before
the crash.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp