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.
Express Edition limits the size of the database to 10 GB.
The backup is small because it only contains pages that are used -- regardless of whether or not the backup is compressed -- but the original file sizes will be restored to the size they were when they were backed up. The file sizes are what count against the limit.
Assuming this is the problem, the only solutions are to shrink the files before taking the backup, or restore the backup on a different edition (probably Developer edition).
Best Answer
Well I don't wonder about the restoring time. I wonder about your file amount. 15k files for 112MB? What the h...?
Your symptom is nearly the same as during file copying. If you have two disc (disc1, disc2) and you try to copy 100.000 files each 4KB in size from disc1 to disc2 it may take hours. If you copy one zip file without compression (containing those files) with 400.000KB file size and then extract it afterwards on disc2, it would take a few seconds.
You'll stressing your I/O subsystem with many I/O operations. Consider to merge those files back to one or just a few. I'm not sure what has driven you to those design?