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

sql sql-server backup
Fernando picture Fernando · Oct 15, 2010 · Viewed 248.8k times · Source

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?

Answer

Daniel Gill picture Daniel Gill · Feb 4, 2012

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.

Related questions

How can I retrieve the logical file name of the database from backup file

I was looking into the steps of how to Restore Database Backup using SQL Script (T-SQL). Here are the steps: Database YourDB has full backup YourBackUpFile.bak. It can be restored using following two steps: Step 1: Retrieve the logical file …

Read More
SQL Server: how to query when the last transaction log backup has been taken?

I would like to query for all databases (in SQL Server 2008 instance) date when the last transaction log backup has been taken. How to do that? I know that this information is somewhere, but I don't know where.

Read More
How do I UPDATE from a SELECT in SQL Server?

In SQL Server, it is possible to insert rows into a table with an INSERT.. SELECT statement: INSERT INTO Table (col1, col2, col3) SELECT col1, col2, col3 FROM other_table WHERE sql = 'cool' Is it also possible to update a …

Read More