SQL-Server Blog of Ritesh Shah --Fight the fear of SQL with SQLHub.com

Today I have been asked by someone that I am not able to copy MDF and LDF file from our production server.  Whenever I want to copy it, I have to stop SQL Service. This is very common problem and not everybody aware with why this happens? So I tempted to write something small about this.

If you are using your database, you are not able to copy the data or log files; you can do it without even stopping services of SQL with following small script.

 

ALTER DATABASE YourDatabase

SET OFFLINE WITH ROLLBACK IMMEDIATE;

--Now Manually copy your file(s) to anywhere you want

--and set your database online

--during this process your database will be offline

--It's not good but better than stoping SQL Server services

ALTER DATABASE YourDatabase

SET ONLINE;

Actually take a backup of database and move backup file anywhere could be good but even if you wish to copy MDF or/and LDF file, you can have above approach.

Reference: Ritesh Shah
http://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles but examples and explanations prepared by Ritesh Shah, founder of http://www.SQLHub.com