SQL Server MDF Files - How to back up

Question

Here's the scenario:

We have a corporate website with three databases. It's running SQL Server 2005. The three databases are linked to MDF files. Someone tried to directly copy / paste one of these MDF files that did some kind of lock on the file that caused a read-only error. We fixed this by detaching / reattaching.

To prevent this again, we're looking to get copies of these databases to demote to our QA database.

What's the best way we can make copies without taking the corporate site down? I tried to search google and didn't come up with a decent way. The databases are fairly small, and the worst case I can think of is just to shut down the SQL Server Service, then just copy the MDF's, then restart the service. I'm assuming that's 10 seconds at the most downtime.

[Please note anyone reading this, do not directly copy MDF files, make sure you at least take the DB offline or the DB can be locked]

Answer 1

Rather than trying to copy and locking the MDF files, just take backups of the database to the filesystem and then copy those off.

You ask whether users will be affected during a backup...

We can't say that there won't be "any" affect on the users because of course we are doing things with the database. However, backing up live databases is the norm. Generally, you would schedule the backup overnight or during a period of low use so that there is minimal disruption.

Check out this questions which has some good information provided: https://serverfault.com/questions/100490/what-happens-during-a-live-sql-server-backup