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

6 Responses to “Copy LDF and MDF file without stopping SQL Server services:”

  1. Bihag Says:

    Ritesh, This is great stuff. I never knew that this is possible while the database server is online.

  2. Bihag Says:

    Ritesh, This is great stuff. I never knew that this is possible while the database server is online.

  3. Anonymous Says:

    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

  4. Anonymous Says:

    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

  5. Ritesh Shah Says:

    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

  6. Ritesh Shah Says:

    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


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: