Transact SQL to backup remote database

Posted on 2007-10-11
Last Modified: 2013-12-13
I am wanting to write a transact SQL query to backup a remote SQL server database to a local file (database) on our server using IP address and user name and password and SQL server authentication. Can anyone advise on this. I have attempted to use wizards to copy databases in SQL management studio, and am baffled by integration services. I think a transact SQL statement would be far easier to maintain. The remote server runs SQL server 2000, and I am running 2005.
Question by:klycett
    1 Comment
    LVL 14

    Accepted Solution

    If you mean a database backup (using backup database), you can't run a backup command on the remote server that specifies a file that is across the network.

    If you are just after the data out of a table, suggest using the command line utility bcp to output the data to a file, and then copy the file across the wire.

    You can use xp_cmdshell in a stored proc or sql script, that will run the bcp and filecopy.

    Featured Post

    IT, Stop Being Called Into Every Meeting

    Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

    Join & Write a Comment

    Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
    Password hashing is better than message digests or encryption, and you should be using it instead of message digests or encryption.  Find out why and how in this article, which supplements the original article on PHP Client Registration, Login, Logo…
    The viewer will learn how to count occurrences of each item in an array.
    The viewer will learn how to create and use a small PHP class to apply a watermark to an image. This video shows the viewer the setup for the PHP watermark as well as important coding language. Continue to Part 2 to learn the core code used in creat…

    746 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    18 Experts available now in Live!

    Get 1:1 Help Now