replicate DEV box - job failed

Question

replicate DEV box - job failed

bluepaperbag

SSCrazy

Points: 2055
More actions
October 2, 2007 at 3:01 pm

#179495

I have a DTS copy objects that copies the tables from PROD to DEV everynight. The database is called myDatabase.
One extra column were added to a table on myDatabase which affected the sql job failed and made it to fail.
I want to prevent this from developers to modify the tables on myDatabase. The thing is it's a DEV box and they are supposed to do anything there. Should I design another Database called MyDatabase2 and do the extact copy?
How do you people replicate the DEV box? Ideas will be appreacited...

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

homebrew01 SSC Guru Points: 55548 More actions · Answer 1

You can't reliably copy data from one database to another if the target is being changed without your knowledge. You need better communication with the developers, and then you can coordinate when to copy hte data.

Lowell SSC Guru Points: 323518 More actions · Answer 2

also, a DTS job is slower thant he time it takes to backup, copy and restore a database, as well as being prone to the changes you are seeing.

I'd recommend that over a DTS job to copy this database each night.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

bluepaperbag SSCrazy Points: 2055 More actions · Answer 3

homebrew01 (10/2/2007)
You can't reliably copy data from one database to another if the target is being changed without your knowledge. You need better communication with the developers, and then you can coordinate when to copy hte data.

I forgot about the copy objects package. Otherwise i would have tell him not to modify the table. Thats why i need a solution for future reference.

bluepaperbag SSCrazy Points: 2055 More actions · Answer 4

Lowell (10/2/2007)
also, a DTS job is slower thant he time it takes to backup, copy and restore a database, as well as being prone to the changes you are seeing.
I'd recommend that over a DTS job to copy this database each night.

The db is 35GB I have to send the .bak thru the network and it's going to take very long.

homebrew01 SSC Guru Points: 55548 More actions · Answer 5

But if the developers are using the database, you don't want to restore on top of it. Hard to decide on the best solution with limited info.

bluepaperbag SSCrazy Points: 2055 More actions · Answer 6

homebrew01 (10/2/2007)
But if the developers are using the database, you don't want to restore on top of it. Hard to decide on the best solution with limited info.

I want to know how to create a DEV box. Currently we use DTS copyobjects to copy all the tables everynight. The db that contain the tables is 35GB. If i want to bakcup and restore, I will have to transfer the 35GB bak file trhu the network and this is going to take me very long.

Maverick100877 SSCertifiable Points: 5240 More actions · Answer 7

Do the developers require the production data also every night?? But why do they require the production data everynight?? You will have to ask the purpose for this. And if they really want the data every night then the best way would be restore the Prod database on the dev box with a different name probably if the developers don't want it to overwritten.I would suggest backing up the db with some 3rd party tools like Lite Speed and then using Robocopy to copy the file to the dev box and restore it. This way t will not take too long to copy to the dev box.

homebrew01 SSC Guru Points: 55548 More actions · Answer 8

We use Idera's SQLSafe for backups, which I think is similar to Litespeed. It creates backup files that are compressed about 80 %, and in about 1/3 less time. It would then be quicker to copy a 7 G backup than a 35 G backup.

You could also replicate the data. If your tables have primary keys, you can use transactional replication which will only copy over changes to the tables after the initial snapshot build is complete.

rlondon SSCarpal Tunnel Points: 4653 More actions · Answer 9

You need to know why you are shipping the database over to dev every night. If it is just to use for development then overwriting the database nightly might not be the best answer. As stated previously you may be overwriting the developer's work. If the reason for copying nightly is to ensure there is a current backup of production in case of emergencies, then developers should not be using it as a dev site. In order to know the best procedure for you, you need to determine what exactly the copy is being used for.