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. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

We need to copy 500 GB database from our PRODUCTION server to a DEV server. We only have a window of 5 hours.

What would be the quickest way to achieve this?

share|improve this question

migrated from Nov 23 '11 at 17:29

This question came from our site for professional and enthusiast programmers.

1. What do you mean by "a window of 5 hours"? Production will be down for 5 hours? 2. Do you need the absolute latest version of the production database, or will a recent backup suffice? 3. What recovery mode is your production database in? – Nick Chammas Nov 23 '11 at 18:36

There are two options you can do that will not cause any downtime to your production environment:

1) If you only want to grab the database structure itself (you don't need the data) you can simply script out the production database. Then simply run that on your dev server to create the database.

2) If you need the database and the data simply grab the latest backup of that database and restore it to your dev server. You will find that this is the most common method used to keep the development servers matched up to production data.

share|improve this answer

If you have

  • backup compression on?
  • a fast server-server network (the entry point is 1 GB)

...then just copy it over, restore. This can be scripted in powershell, perl, cmd.exe etc.

When you say "window" I assume you mean 5 hours window on production. It can't be that critical to load development.

share|improve this answer

Copy to locally attached hard disc.

Why only a limited window? Dont copy - load the backup to dev.

share|improve this answer
Agreed. No need to detach (necessitating a downtime), really, when restoring a backup will suffice. – p.campbell Nov 23 '11 at 17:26
I'll second the copying of a backup. There's no need to take down your production database to copy it anywhere. If you need a specific point in time, either make a copy_only backup (SQL 2005+) or copy down some tran log backups, too. – Ben Thul Nov 23 '11 at 17:54

Maybe my answer won't fit with your use case but, did you ever consider to setup, on your dev server, a stand-by database by using a technic like log shipping .... so you could restore your prod over your dev prior and keep this copy in sync with your prod ...

Hope this help

share|improve this answer

Create a backup sequence file. Lets say your entire backup size is 500 GB; you can divided it up by your need, create a 10 sequence backup that consists of 10 backup files, each 50 GB in size, and then try to restore or move the backup files one by one. It will reduce the network traffic and restore time.

share|improve this answer
Hello Subir and welcome to DBA.SE. I've edited your answer to improve its clarity and to remove the signature at the bottom per our guidelines. Thank you for your answer. – Nick Chammas Jun 6 '12 at 15:29
I fail to see how splitting the file into 10 individual files will make any difference to the time required to transport those files across the network. – Max Vernon Oct 19 '12 at 21:26

Your Answer


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