Post reply

Copy SQL Object Questions

  • February 9, 2004 at 1:59 pm

    #59646

    I am going to be copying my primary database to a new db server and am curious is anyone knows roughly what the time ratio is for this process and is it an exculsive use type of function.

    I apologize in advance for my lack of knowledge on the subject but I have just entered the SQL world and am very aware of my weaknesses in this area and hopefully wont be too much of a bother to you folks, I thank you in advance for any assistance you are willing to provide.

    So my db is roughly 2.1gb in size and I am copying it to a box right next to it on the same subnet, same switch, different domain and am wondering what reprecusions I can expect. Is it going to lock the db in question while the copy process runs and roughly how long would you imagine this copy job would take?

    Thanks again for any insight you can provide.

  • February 11, 2004 at 4:46 am

    #493591

    Would it not be possible to backup the original one manually and restore it on the new server.

  • February 11, 2004 at 3:56 pm

    #493721

    The copy SQL Objects task will BCP the data out to a text file, truncate the source table and then BCP the data back in. This happens for each selected table in alphabetical order. So it will lock each table in turn. If this is a straight SQL to SQL transfer, you might get just as good performance by doing a Truncate Table and DTS Datapump. You can then change the order the tables are loaded by applying some workflow in the DTS package.

    As to how long, well that's an open question. What sort of CPU's do you have in the servers? What sort of Disks do you have? How many columns are in the table? What type of data is in the tables? Etc...etc... Best option is to run it a few times to see what the performance is like.

     

    --------------------
    Colt 45 - the original point and click interface

  • February 26, 2004 at 6:03 pm

    #496061

    Thanks for the insight folks, DTS did very very bad things to my DB when I tried to use it to transfer the db onto another server, it lost all my primary keys and blew my indexes and as I am not fluent in script I was not able to script these back into existance easily. Sooo I just restored the database from a tape backup by redirecting it to the new server and adjusted the users by hand, took all of 15 minutes, including the user manipluation, all hail Backup Exec v9

    As a trivia bit, the server has dual Xeon 1.8 procs with Quantum hard drives, 2mg buffer with 7200rpm iirc.

    Thanks again for the insight, I appreciate it.

Viewing 4 posts - 1 through 3 (of 3 total)

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