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.
- 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?
20 Solutions collect form web for “How can I backup a remote SQL Server database to a local drive?”
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:
- In 2010: under the Table/View Options, change ‘Script Data’ and ‘Script Indexes’ to True and hit next,
- In 2012: under ‘General’, change ‘Types of data to script’ from ‘Schema only’ to ‘Schema and data’
- 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.
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.
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
- Database: the db name
Choose a Destination
- Data Source: SQL Server Native Client
- Server Name: the local server
The rest is straight forward.
I know this is late answer but I have to make a comment about most voted answer that says to use generate scripts option in SSMS.
Problem with that is this option doesn’t necessarily generate script in correct execution order because it doesn’t take dependencies into account.
For small databases this is not an issue but for larger ones it certainly is because it requires to manually re-order that script. Try that on 500 object database 😉
Unfortunately in this case the only solution are third party tools.
I successfully used comparison tools from ApexSQL (Diff and Data Diff) for similar tasks but you can’t go wrong with any other already mentioned here, especially Red Gate.
You can try SQLBackupAndFTP. It will create scripts to create all the objects in your database and INSERT statements for all the rows in your tables. In any database you can run this script file and the entire database will be re-created.
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;
Look at this blog for a description how to copy a remote database:
Backup a SQL Server 2008 Database From a Shared Hosting Environment
You can use Copy database … right click on the remote database … select tasks and use copy database … it will asks you about source server and destination server . that your source is the remote and destination is your local instance of sql server.
it’s that easy
The AppHarbor gang has been struggling with this and has developed a temporary solution using SQL server management objects and SqlBulkCopy.
Check out their blog post about it, or go straight to the code.
They’ve only tested it with AppHarbor but it may be worth checking out.
The answers above are just not correct. A SQL Script even with data is not a backup. A backup is a BAK file that contains the full database in its current structure including indizes.
Of course a BAK file containg the full backup with all data and indizes from a remote SQL Server database can be retrieved on a local system.
This can be done with commercial software, to directly save a backup BAK file to your local machine, for example This one will directly create a backup from a remote SQL db on your local machine.
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.
just try this one:
1)Share a folder with full permission in your computer
2) in your SQL server :
control panel -> administrative tools -> services -> right click on all SQL services
on log on tab should start with your domain administrator
3) in maintenance wizard of sql server place the back up location and folder (\yourcomputername\sharedfoldernam)
I did remote backup on 8 server of sql server 2008 in our company
There is the 99% solution to get bak file from remote sql server to your local pc. I described it there in my post http://www.ok.unsode.com/post/2015/06/27/remote-sql-backup-to-local-pc
In general it will look like this:
execute sql script to generate bak files
execute sql script to insert each bak file into temp table with varbinary field type and select this row and download data
repeat prev. step as many time as you have bak files
execute sql script to remove all temporary resources
that’s it, you have your bak files on your local pc.
I could do that once…TO do this you have to have a share opened on the remote server. then you can directly place the backup on the share itself, than the default location…
Usually the admin takes the backup and shares it with us in some shared folder. I tried if that will work if i place the backup there. It worked.
yone way you could take a backup from a remote SQL Server instance to your local drive, given the following condition is met:
- You have a shared folder on your local drive.
- the shared folder is accessible from the SQL Server box.
Now when specifying the backup command, use the shared folder path when specifying the disk option.
If you use the Generate Scripts under SSMS, click the Advanced button. Under the ‘Generate Scripts for the dependent objects’ option, click True. By clicking that any dependencies of each object will also be scripted out in proper order.
Some third-party backup programs allow setting file transferring with specific network permissions. It it very useful when SQL Server service is running under restricted account and does not have enough network permissions. Try using EMS SQL Backup which solves this task.
I’m astonished that no-one has mentioned the scripted backup solution offered by Ola Hallengren which absolutely does allow you to backup a DB from a remote server to a UNC path on your network for free (I’m actually using it as I type to backup a DB from a dev server to which I have no remote access other than through SSMS to a share on my dev PC). This has been available since 2008 and works on SQL Server 2005 through to 2014.
You need to ensure that the share you set up has enough access: I tend to allow full read/write to the ‘Everyone’ AD group for the duration of the backup process because I’m too lazy to figure out anything more restrictive but that’s personal choice.
It’s well-used, well-documented and very flexible. I tend to put the procs and the logging table in their own little utility database and then fire it up. Provided everything is in your AD domain and not remote in the sense that it’s out on a co-located server or something, this works very well.
Apologies for adding to a very old thread but I came across this when looking for something else and figured it was a worthwhile addition for anyone looking for this topic.
I use Redgate backup pro 7 tools for this purpose. you can create mirror from backup file in create tile on other location. and can copy backup file after create on network and on host storage automatically.
I think the “correct” way to do this would be by backing SQL Server locally (to the same machine), and then using ntbackup/windows backup to backup the .bak files to a remote location.