How can I backup a remote SQL Server database to a 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 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

User · Answer

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

User · Answer

Create a local shared folder  with  everyone  read write privileges  Connect to the target database  start the backup and point to the share like below   mymachine shared folder mybackup bak   Tried on Windows domain environment

User · Answer

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  amp  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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

I know this is an older post  but for what it s worth  I ve found that the  simplest  solution is to just right-click on the database  and select  Tasks  -   Export Data-tier Application   It s possible that this option is only available because the server is hosted on Azure  from what I remember working with Azure in the past  the  bacpac format was quite common there    Once that s done  you can right-click on your local server  Databases  list  and use the  Import Data-tier Application  to get the data to your local machine using the  bacpac file   Just bear in mind the export could take a long time  Took roughly two hours for mine to finish exporting   Import part is much faster  though

User · Answer

For 2019  I would recommend using mssql-scripter if you want an actual local backup  Yes it s scripts but you can adjust it to include whatever you want  which can include all of the data  I wrote a bash script to do automated daily backups using this on a linux machine  Checkout my gist   https   gist github com tjmoses 45ee6b3046be280c9daa23b0f610f407

User · Answer

If you are in a local network you can share a folder on your local machine and use it as destination folder for the backup  Example   Local folder  C  MySharedFolder - gt  URL    MyMachine MySharedFolder  Remote SQL Server  Select your database - gt  Tasks - gt  Back Up - gt  Destination - gt  Add - gt  Apply    MyMachine MySharedFolder BACKUP NAME bak

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

Look at this blog for a description how to copy a remote database   Backup a SQL Server 2008 Database From a Shared Hosting Environment

User · Answer

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

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

Examples related to sql

Examples related to sql-server

Examples related to backup