How to backup remote SQL Server database to local drive?

Question

I need to copy a database from a remote server to a local one. I tried to use SQL Server Management Studio but it only backup to a drive in the remote server.

Some points:

• I do not have access to the remote server in a way that i could copy files;
• I do not have access to setup a UNC path to my server;

Any ideas of how can I copy this database? Will I have to use 3rd party tools?

Thanks!

Answer 1

You cannot create a backup from a remote server to a local disk - there is just no way to do this. And there are no third-party tools to do this either, as far as I know.

All you can do is create a backup on the remote server machine, and have someone zip it up and send it to you.

Answer 2

As Martin Smith said, if you have no access to the machine or the filesystem, you will need to use third party tools, like Red Gate or Adept to do a compare on the source and destination systems. Red Gate's tools will allow you to copy the objects and schemas AND the data.