I am stucking on one place in sql server. We have more than 100 of databases(.mdf). I want to copy the updated data in mdf file to another location. Suppose First time have I have detach all databases from location C:\Program Files (x86)\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA and copy them to another location say C:\ and reattach the all original files(detach because of used by sql server). Now I have copy in C drive. I want a query or steps to copy the updated data from all databases from my original location to copied location (which is in c drive) regularly. I have googled about this and find the solution that stop your services first. But I can't do this because in my company 24 hours shift and regularly data updated. So there is no chance to do this. and also I have tested it with Batch file.
xcopy /y /d /r /s /e "C:\Program Files (x86)\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\TestAxle.mdf" "c:\TestAxle.mdf"
But it showing the error message Sharing Voilation also tested with the below command:
copy /y "C:\Program Files (x86)\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\TestAxle.mdf" "c:\TestAxle.mdf"
But its also showing the file is using by another process.
Please tell me how do I copy the updated data in mdf file regularly.
Thanks in Advance
This sounds to me like you are trying to create a backup. If that is the case, use the backup command instead. While it does not create an "mdf" file, the backup will contain everything the original mdf file contains, including the contents of unused pages.
A backup using the
CREATE BACKUPcommand (http://msdn.microsoft.com/en-us/library/ms187510(v=sql.105).aspx) you can take without shutting the service down or otherwise blocking access to the database. (In most cases there is not even a noticeable performance impact.)Also keep in mind, that a SQL Server backup is guaranteed to be transactionally consistent. A copy of the mdf file (at least if kept without its ldf file) is not guaranteed to be transactionally consistent.
Getting an actual copy of the mdf file is not possible as the files are locked by the service. The only way that I can think of would be SAN replication.
If these two options don't work in your case report back what you are trying to achieve by having those copies.