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

user_447458 0
Viewed 295603

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?

backup sql-server sql
24 Answers

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_12890460

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 -> URL: \\MyMachine\MySharedFolder

  • Remote SQL Server:

    Select your database -> Tasks -> Back Up -> Destination -> Add -> Apply '\\MyMachine\MySharedFolder\BACKUP_NAME.bak'

user_21710400

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_77246290

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_29409240

Using a stored procedure in SQL Server 2019

CREATE PROCEDURE [dbo].[BackupDB]
@backupPath nchar(200), 
@dbname nchar(50)
AS
BEGIN
    SET NOCOUNT ON;
    DECLARE @backupDate as nchar(10) 
    SELECT @backupDate = CONVERT(VARCHAR(10),GETDATE(),112) 
    IF DB_ID(TRIM(@dbname)) IS NOT NULL
    BEGIN
        SET @backupPath = TRIM(@backupPath) + TRIM(@dbname) + '_' + TRIM(@backupDate) + '.BAK'
        BACKUP DATABASE @dbname TO DISK = @backupPath
    END 
END
GO
user_12682580
Related