Copy LDF and MDF file without stopping SQL Server services:

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
Advertisements