Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I'm working on setting up a development copy of a production database on SQL Server 2008 R2 SP1. The live database is getting lightly used by two developers for read only queries currently, but the new database will have updates done on it as well.

Since the database is 2.1TB and took a total of 3 days to restore and update to the latest build we need for testing, my original plan was to create a new set of backup files and then restore from those files. This would allow me to create the development copy of the database on the same SQL instance and machine, without having to take the current database offline.

However, in order to save a couple of those days, I was thinking that it might be a good idea to just copy over the physical database files and attach the new copy of the database. Unfortunately when I try to copy I get an error referring to the lock that SQL Server puts on those files.

Since I can't take the database offline for anything but transferring the log files (I can finish this before people get in in the morning), is there any way I can copy the live database files without putting the database into an offline state? Or should I wait until people go home to do that?

share|improve this question
    
2.1tb and 3 days? Time to retire the 100mbit network, you know. 2.1tb on a decent backup system with a 10g backbone - is not something that takes 3 days. I pull full backups of a aroudn 1tb databae regularly and it takes a LOT less than 1 day.... a LOT less. –  TomTom Jul 8 '13 at 16:16

3 Answers 3

up vote 6 down vote accepted

Why does it take 3 days to backup / restore a 2.1 TB database? If it is all about moving the backup, I'd suspect copying the MDF/LDF files would actually be slower since at least the backup could be compressed. And if it's just about restoring, then writing the copies of the file there shouldn't be any faster than the restore process itself - make sure you have instant file initialization enabled. Or get faster I/O on the secondary system.

Anyway, no, you can't copy the MDF/LDF files while the database is online, you'd have to take it offline to do so. And this is the absolute LEAST safe way to copy a database. If anything happens to the files while they're detached/offline, you now have zero copies of your database.

I would suggest looking into ways to make the backup/restore faster - whether it be ensuring you're compressing your backups and transferring the files in the best way possible, using the best I/O subsystem available, getting better I/O, making sure that IFI is enabled, etc.

Another option to consider is round robin log shipping - assuming production is in full recovery and you are taking regular log backups, you can perform log shipping to one database while developers work on another, and when you get to a stable point, restore with recovery and have the developers switch, taking their recent changes with them. Now re-initialize log shipping on the copy they just stopped working on, and it will be ready for them at the next "stable" cut-over, with no waiting.

share|improve this answer
    
The backup would take an unknown amount of hours (I didn't create the original backup). The restore takes 10 hours, then running updates to get it up to our development build takes another 8 hours. So really, if we're talking about a 24 hour day it only takes 18 hours. Talking about 8 hour days, that's basically 3 days (adding in some time to copy files over). There is ABSOLUTELY ways I can tune the backup/restore process itself, and that is what I'm going to do now that I know that's the way I want to go. –  Sean Long Jul 8 '13 at 14:35

I have seen the other answers and they make perfect sense. However, just to answer your question, you can certainly copy database files without taking the DB offline. You will need to use SMO programming for this purpose. Check this out : http://technet.microsoft.com/en-us/library/ms162175(v=sql.100).aspx

Please note that the link provided is to general SMO programming objects and not a specific answer to this question. I haven't done so yet but I am soon going to try this out and will let everyone know (if I remember), what I find out.

share|improve this answer

No, you'll have to take it offline to copy the files. If you have your database divided into smaller files/filegroups it could help in making the backup restore faster after you've done it once (depending on what data is changing and what data is needed for the developement copy).

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.