Do I need to detach a database to attach it?

Question

I am trying to move data from one SQL Server server to another (located on another computer). I know of the detach/attach method, but I don't want to disturb the prod environment (detaching a prod database is out of the question). Can't I just copy/paste the MDF and LDF files IF nothing is being actively written to/read from the prod?

What if there are processes that are reading or writing to the database, what are my options to create an "attach" file without performing detach?

Thank you very much. I read on two questions related to this but neither one applies here. One suggests using backup/restore, but that seems a bit difficult.

Answer 1

Unfortunately you can't just copy the "hot" mdf & ldf files, even while no data is actually being written to the DB. As long as the files are attached to a SQL Server you cannot copy or move them. In fact I don't think Windows will let you even copy them as they should be locked by the server process.

The trivial solution is just using backup and restore, it's painless and secure. In fact there should be already be a regular backup process since you mention that this is production system, maybe you don't even need to actually take a backup, just wait for the next one to take place and use it to restore. On SQL Server the backup/restore process is really straightforward, probably one of the easiest on all the RDBMS ecosystem.

Any other method will be more difficult and likely yield worse results. I wouldn't recommend them at all. You could try to copy the files using VSS (Volume Shadow Copy) and see if you get consistent enough files to attach. Alternatively you could create an empty database and just export all the tables, views, procedures, etc.