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?
-
71. 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 ChammasCommented Nov 23, 2011 at 18:36
Add a comment
|
5 Answers
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.
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.
Copy to locally attached hard disc.
Why only a limited window? Dont copy - load the backup to dev.
-
Agreed. No need to detach (necessitating a downtime), really, when restoring a backup will suffice. Commented Nov 23, 2011 at 17:26
-
2I'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 ThulCommented Nov 23, 2011 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
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.
