Results 1 to 7 of 7
  1. #1
    Join Date
    Oct 2014
    Posts
    268
    Provided Answers: 6

    Answered: Automating Export from one server Import to Another

    Both using SSMS 2012 Enterprise on Windows 2012

    I am able to import - export manually, how can you schedule this import export in a job? I want the table to drop and be rebuilt each time it runs. Since linking servers isn't recommended I want go with that approach.

    Thoughts?
    Last edited by VLOOKUP; 10-18-15 at 19:22.

  2. Best Answer
    Posted by Pat Phelan

    "Microsoft SQL Server Replication is the best supported, and the most repeatable of the methods.

    You can do a SQL Agent job to export/import the table. The general idea is:
    1. Use BCP in a command line job step to create a file with the data.
    2. Create the schema in another job step
    3. Load the data using BULK INSERT from Transact-SQL or BCP from the command line.

    Based on what you've described so far Replication would be my first choice, but I could be persuaded to use any of several other options.

    -PatP"


  3. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Microsoft SQL Server Replication is the best supported, and the most repeatable of the methods.

    You can do a SQL Agent job to export/import the table. The general idea is:
    1. Use BCP in a command line job step to create a file with the data.
    2. Create the schema in another job step
    3. Load the data using BULK INSERT from Transact-SQL or BCP from the command line.

    Based on what you've described so far Replication would be my first choice, but I could be persuaded to use any of several other options.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  4. #3
    Join Date
    Oct 2014
    Posts
    268
    Provided Answers: 6
    Pat - Could you set up an SSIS package to do something similar? Going across servers?

    Not familiar with BCP, looks like old school command line. I'll review since you suggested. Thanks

  5. #4
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Conceptually SSIS is the same as the SQL Agent job. If you have SSIS installed, then an SSIS package could also be used to copy a table from one server to another.

    Replication is the easiest to set up (fewest moving pieces, no custom code needed, and most supportable infrastructure). The SSIS package is custom code but its dependencies (SSIS) are well supported. The SQL Agent using BCP is also custom code but it has the fewest requirements (it can be set up on either server and has no dependencies).

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  6. #5
    Join Date
    Oct 2014
    Posts
    268
    Provided Answers: 6
    Pat can you guide me to some white pages and a walk through? I would really appreciate it.

  7. #6
    Join Date
    Oct 2014
    Posts
    268
    Provided Answers: 6
    Pat - So I went ahead and defaulted to the developers to handle this task, I like to do the heavy lifting usually and allow them to live Visual Studio, but I was unsure and went with them. Guess what they did, YEP linked the servers lol.

    Oh well it wasn't me......

    At least my reports are running and the senior director is very happy. lol

  8. #7
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    As I said earlier, Linked Servers are a key component of many Microsoft technologies but they aren't for the newcomer. Replication uses Linked Servers "under the hood" as do Availability Groups and many other technologies.

    With that said, Linked Servers aren't for the newcomer and even many "old hands" don't think about the dependencies and unexpected processing side effects that Linked Servers imply.

    As long as you've got what you need, then things are good for now. You probably want to note this dependency in your documentation, since I will guarantee that you (or your successor) will regret this if you ever forget about it!

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •