9

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?

Improve this question
0

4 Answers 4

Reset to default
11

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.

Improve this answer
1
  • 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
    Commented Jul 8, 2013 at 14:35
0

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).

Improve this answer
0

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.

Improve this answer
-1

Can a database's files be copied while a database is online?

Yes.

All you have to do is set the database to read-only:

  1. Run:

    USE [master]
GO
ALTER DATABASE [Database] SET READ_ONLY WITH NO_WAIT
GO

  2. Copy the database files - MDF,LDF, etc. to their new destination

  3. Once copy is done, you can detach and re-attach on the new location. Just make sure to reset the read-only option and that you are using the same user account when manipulating these files. Also, you might want to delete the old files after the move.

Another method

After the files are successfully copied (step 2 above):

You can set the database to: Single_User mode then:

  1. Run:

    USE [master]
GO
ALTER DATABASE [Database] SET  Single_USER WITH NO_WAIT
GO

  2. D:\ being the new location

    ALTER DATABASE Database MODIFY FILE
(NAME = DATABASE, FILENAME = 'D:\DATABASE.mdf')
GO
ALTER DATABASE OHDSI MODIFY FILE
(NAME = DATABASE_LOG, FILENAME = 'D:\DATABASE_LOG.ldf')
GO

  3. Make sure the data files in the new location (D:\) has full access to the service account that is running SQL Server.

  4. Restart the SQL services

  5. Run:

    USE [master]
GO
ALTER DATABASE [Database] SET  Multi_USER WITH NO_WAIT
GO

Done!

Improve this answer
4
  • First note that putting the DB in read-only mode causes an effective outage if normal usage requires writing to the database. Second, I would expect SQL Server to still have the files locked for use if they're being read; if your personal experience or MS documentation says otherwise, please edit that into your answer. Third, the advice I've generally seen for altering a file location is to take the DB offline, not just into single user mode.. Again, if you've got personal experience or MS docs suggesting otherwise, please edit into answer. Thanks!
    – RDFozz
    Commented Jul 31, 2017 at 18:00
  • That's correct! Read-Only will prevent users from writing but they will be able to read. In your second point - yes you can take the database offline - but the question is while being online. The process i described above works with the minimal downtime. Thanks.
    – RAVicioso
    Commented Jul 31, 2017 at 18:54
  • @RAVicioso the database being read only is an outage too. If you can't write new data (i.e. take orders), your system is effectively down. This seems like a bad idea, non?
    – Erik Reasonable Rates Darling
    Commented Jul 31, 2017 at 20:08
  • @sp_BlitzErik - The solution works, but you won't be able to write during this type of maintenance. In your example: "take orders", it won't.
    – RAVicioso
    Commented Aug 15, 2017 at 18:35

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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