The fastest, simplest solution is to restore your last good backup. If that isn't an option you can try a workaround to make MSSQL think the database already exists:
http://sqlskills.com/BLOGS/PAUL/post/Disaster-recovery-101-hack-attach-a-damaged-database.aspx
Then you can run DBCC and see if it can repair the database, but even in that case you may still have data loss. You could also call Microsoft (maybe you already have a support contract), but the bottom line is that if you have a damaged database and no backup then there's not much that anyone can do.
Lets take your questions one by one :
Is it possible to do the following:Have a copy of the database on the same SQL Server Instance
Yes it is possible. You just have to restore the database main-db.bak with move.
e.g. Provided you dont have secondary data files, below will work
-- find logical names
restore filelistonly from disk = 'path\main-db.bak'
go
restore database main_db_copy
from disk = 'path\main-db.bak'
with recovery, stats = 10,
-- below logical data and log file names will be found from the filelistonly mentioned above.
move 'logicalDataFileName' to 'PhysicalPath\main-db_data.mdf',
move 'logicalLogFileName' to 'PhysicalPath\main-db_log.ldf'
Create a new empty database
No need to first create an empty database, as the restore with move will create it.
Restore the database from main-db.bak file and not overrite anything else that may be using the same data MDF and LDF files?
Same as above. As restore with move will not over write unless you use restore with REPLACE explicitly to overwrite.
Or would it be easier to reproduce this database from a live running database copy so that it is not needed to restore if from backup?
Cannot understand what you mean by this ! If you want to create a new database or copy of the current database , you have to take a backup and then use restore database to restore it.
Is it mandatory to rename the LDF and MDF files when the database is restored, can I script to automate the majority of the work?
If you want to overwrite the existing one, then no. If not and you want to create a copy of existing one, then Yes - you have to rename the mdf and ldf files.
You can fully automate the restore process. Some examples can be found here and here.
Best Answer
Yes, the easiest way is to attach the MDF (if you can). Not all abandoned MDF files are attachable, it depends on how they were detached and what state the server was in when that happened. The proper way to take a backup is
BACKUP DATABASE.There isn't a way that I know of to extract only a single table from a detached MDF file, unless you're really, really comfortable with a hex editor.
So I think you are on the right track - attach your MDF as a different database name, and extract the data from the one table you need. Shark provided the syntax you'll need. However, again, a disk image does not necessarily leave an MDF in a usable state, so without a proper backup, all I can do is cross my fingers for you.