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
Microsoft SQL Server Blog. Fight the fear of SQL with SQLHub.com. Founder is Ritesh Shah

Author: Riteshshah

Ritesh Shah is a database professional with having 16+ years of experience in various domain of IT field. He is a author of many technical articles on Microsoft Technology and authored a book of SQL Server 2012 Performance Tuning cookbook. He loves work with different Microsoft Products.

3 thoughts on “Copy LDF and MDF file without stopping SQL Server services:”

  1. This Very good sample. Thank you !

    Bu i want to ask this – If my Data file ( mdf and ldf) are greater than 300-400 MB this process will take some minutes. May be other user work with this sql server form other computer in the same time?? Will there any problem?

    Thanks,
    Vugar Avazov, avazov@yahoo.com

  2. Hello Vugar,

    Thanks for your comment.

    Since database is offline, user will face downtime until you execute command to make database online.

    if you don't want to face offline than take backup of your database and if needed restore it somewhere else. this would be efficient for your need I guess.

    Ritesh

Comments are closed.