MDF Files for SQL Server - Backup

Question

Here's the script:

We have a corporate website with three databases. It starts SQL Server 2005. Three databases are associated with MDF files. Someone tried to directly copy / paste one of these MDF files that made some kind of lock in the file, which caused a read-only error. We fixed this by disconnecting / reconnecting.

To prevent this from happening again, we want to get copies of these databases in order to lower the quality of our QA database.

What is the best way to make copies without a corporate website? I tried to find google and did not come up with a decent way. The databases are pretty small, and the worst case that I can think of is to just shut down the SQL Server service, and then just copy the MDF and then restart the service. I guess 10 seconds in the easiest.

[Note that anyone reading this, do not copy the MDF files directly, make sure that you at least use the DB offline or you can lock the DB]

Answer 1

Instead of trying to copy and lock the MDF files, just back up the database on the file system and then copy them.

You ask if users will be affected during the backup ...

, "" , , , - . . , , .

, : https://serverfault.com/questions/100490/what-happens-during-a-live-sql-server-backup

Answer 2

@dave, , , DataBase > Tasks > Backup . . , SQL Server; , .

sqlcmd -S "MyHostName\sqlexpress" -Q "BACKUP DATABASE MyDatabaseName TO DISK = N'C:\path\to\backup.bak ' NOFORMAT, NOINIT, NAME = N'MyDatabaseName-Full Database Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10"