How can I backup a remote SQL Server database to a local drive?

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 backs up to a drive on 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?

Solution 1:

In Microsoft SQL Server Management Studio you can right-click on the database you wish to backup and click Tasks -> Generate Scripts.

This pops open a wizard where you can set the following in order to perform a decent backup of your database, even on a remote server:

• Select the database you wish to backup and hit next,
• In the options it presents to you:
  1. In 2010: under the Table/View Options, change 'Script Data' and 'Script Indexes' to True and hit next,
  2. In 2012: under 'General', change 'Types of data to script' from 'Schema only' to 'Schema and data'
  3. In 2014: the option to script the data is now "hidden" in step "Set Scripting Options", you have to click the "Advanced" and set "Types of data to script" to "Schema and data" value
• In the next four windows, hit 'select all' and then next,
• Choose to script to a new query window

Once it's done its thing, you'll have a backup script ready in front of you. Create a new local (or remote) database, and change the first 'USE' statement in the script to use your new database. Save the script in a safe place, and go ahead and run it against your new empty database. This should create you a (nearly) duplicate local database you can then backup as you like.

If you have full access to the remote database, you can choose to check 'script all objects' in the wizard's first window and then change the 'Script Database' option to True on the next window. Watch out though, you'll need to perform a full search & replace of the database name in the script to a new database which in this case you won't have to create before running the script. This should create a more accurate duplicate but is sometimes not available due to permissions restrictions.

Solution 2:

First, grant full control permissions to a local path on your machine (as shown below) with Everyone. (Or alternatively grant permissions specifically to the SQL Server Agent account).

Second, execute the following:

BACKUP DATABASE [dev] TO  DISK = N'\\myMachine\c\dev.bak' WITH COPY_ONLY, INIT;

Solution 3:

To copy data and schema only (will not copy stored procedures, functions etc.), use the SQL Server Import and Export Wizard, and choose New... when choosing the destination database.

Right Click Database > Tasks > Import Data.

Choose a Data Source

• Data Source: SQL Server Native Client
• Server Name: the remote server
• Authentication:
• Database: the db name

Choose a Destination

• Data Source: SQL Server Native Client
• Server Name: the local server
• Authentication:
• Database: New...

The rest is straight forward.