Tuesday, May 12, 2015

How to take a copy of database files while they are being used by SQL Server

I am sure that you have tried this at least once and have face the below issue.

Generally, we cannot copy database files or log files when they re being used by SQL Server, simply when the SQL Server service is running. If we need to get a copy of MDFs and LDFs, what we normally do is, stopping the service and copying files. However this is not possible with production databases, specifically when databases are used by 24x7. What is the solution for this?

There are many third party applications and tools available for getting this done. Some are paid tools and some are free tools. I have used one with SQL Server 2005 and 2008, unfortunately I cannot remember it now. While I am reading blogs, I came across this post: Backup/Copy Files that are "In Use" or "Locked" in Windows (Command Line) which is written on Copying Files. It explains how to use a tool called HoboCopy which is specifically for backing up / copying files but it can be used for copying our SQL Server files while they are being used.

In order to use HoboCopy, download the relevant file for you from this: https://github.com/candera/hobocopy/downloads,

Once downloaded, open Command Prompt as an Administrator and type the command as follows;

HoboCopy "FileFolderPath" "DestinationFolderPath" FileName.

For example, if you want to get the AdventureWorks2014 database file copied, you will be typing as;

HoboCopy "C:\Program Files\Microsoft SQL Server\MSSQL12,SQL2014_86\MSSQL\DATA" "C;\Test" AdventureWorks2014_Data.mdf

Note that, in order to run this command, you need to make sure Volume Shadow Copy and Microsoft Software Shadow Copy Provider services are running.

No comments: