Transact SQL to backup remote database

Posted on 2007-10-11
Medium Priority
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

twoboats earned 1000 total points
ID: 20065115
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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
Windocks is an independent port of Docker's open source to Windows.   This article introduces the use of SQL Server in containers, with integrated support of SQL Server database cloning.
The viewer will learn how to count occurrences of each item in an array.
The viewer will learn how to look for a specific file type in a local or remote server directory using PHP.
Suggested Courses

612 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