RSS
Feed
Thursday, June 11, 2009 | 
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
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
3 comments:
Ritesh, This is great stuff. I never knew that this is possible while the database server is online.
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
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
Post a Comment