Sql-server – Can’t use SQL Server database on a networkpath

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.

This isn't an access issue. The problem is that you are attaching the database to a higher version of SQL Server than the instance the .mdf was originally attached to and it's read only. SQL Server is trying to upgrade the database as part of the attach and can not because the db is in read-only mode.

If your database is in READ_ONLY (which sounds like it is the case based on your error messages), then you need all files (both log and data) to attach it per the Technet documentation. If you look under the FOR ATTACH clause, it explicitly describes that:

...for a read-only database, the log cannot be rebuilt because the primary file cannot be updated. Therefore, when you attach a read-only database whose log is unavailable, you must provide the log files or files in the FOR ATTACH clause.

You need the accompanying .ldf for the database. I would contact whoever provided the database and ask them to provide both the .mdf and .ldf files. Then, you can attach it with the following syntax:

CREATE DATABASE [foo]
ON (FILENAME='<<path to mdf>>')
LOG ON (FILENAME='<<path to ldf>>')
FOR ATTACH;

The READ_ONLY status cannot be removed from the database without attaching it because it is stored in the metadata of the database. To alter it, the database needs to be online and attached.

Another alternative is to ask for a backup of the database. You can work around some of these issues more easily if you're doing a database restore instead of an attach.