SQL Server MDF and LDF files (are they hosed?)

[BruceG]

OK - This may be a question for a guru. Let me start by saying that I am new to SQL Server and that I have been working with some tutorials with the Desktop edition of SQL Server 7. I have created two test databases thus far (one of these had some users and permissions assigned).

Now I'm going to tell you what I did next, and it may seem foolish, but bear in mind that I was fully prepared to lose access to these databases if necessary. I wanted to uninstall then re-install the SQL Server 7 Desktop edition, which I did. Before I did this, I moved the 2 MDFs and 2 LDFs to a separate folder, then after reinstalling, I moved them back to the MSSQL7\Data folder.

After opening the Enterprise Manager, under the SQL Server Group for my "server" (which is actually the name of the local machine), under "Databases", although I was hoping to see these two test databases there, I was prepared to NOT see them, and I didn't.

My question is, are these two databases "hosed", or is there a way to "reinstate" them so that they show up in the Enterprise Manager tree? (If not, no BFD, I can re-create them, but if there is a way I'd like to know).

Thanks in advance for your help.

[jkirk]

I think at one point we've all thought this would work...

The quick answer is NO, unless you also backed up the MDF from the system database (and all other databases) from your previous installation. If you only have the MDF for the one database, it's not going to work. If you do have all of your MDF files, you can rebuild it - there's a six or seven page how-to buried in MSDN somewhere on how to do this... and it will take a day or 2 to get the database functional and in multi-user mode.

Next time, go into the ent mgr, right click on the database and pick 'all tasks/backup...'. This creates a file that will update your master database when it is restored.

Good luck,
-jason

[frank ashley]

I had exactly your problem, this is the solution.


Examples
This example attaches two files from pubs to the current server.

EXEC sp_attach_db @dbname = N'pubs',
@filename1 = N'c:\mssql7\data\pubs.mdf',
@filename2 = N'c:\mssql7\data\pubs_log.ldf'



See Also
CREATE DATABASE sp_helpfile
sp_attach_single_file_db sp_removedbreplication
sp_detach_db System Stored Procedures

[BruceG]

I'll be damned! It worked! You ARE the man! Thanks a lot!

[frank ashley]

No problem.

Go out and buy

Professional SQL Server 7.0 Programming by Wrox, a must have!

or

wait for the SQL 2000 book (again by Wrox, out soon)

rgds
Frank

[BruceG]

Yes - the Wrox books are quite good. I am currently working thru the Beginning SQL Server book, then I will move on to the Professional book. Thanks again.

[Maartin]

There is another, which I discovered by accident.
I also copied the databases out, I had to re-install NT and SQL, after that I tried to get the databases working because the backup refused to restore. I ended up by creating the databases in the EXACT Dir structure before the re-install. When that was done I copied the original databases over the new databases.

And it worked, now that I have read this post I know it was not the right way but it saved my but at that time.

Cheers.