replicate DEV box - job failed

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

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

  • 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!

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

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

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

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

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

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

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

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

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