How to take a remote SQL server database backup to local machine?

Question

I've to take a SQL database backup from remote machine to local Linux machine and through command line. Hence executed following command -

sqlcmd -S xxx.xxx.xxx.xxx -U <username> -P <password> -Q "BACKUP DATABASE [TestUser] TO DISK='TestUser.bak';"

But this takes backup on machine where database server is installed. Is there any way I can get backup on machine where it is executed?

**NOTE: ** I can see many questions which asks same but are old ones and answer - either this is not supported or use of third party tools. Hence asking question again, in case has solution with latest stuff.

Answer 1

try to use an unc path in this way:

sqlcmd -S xxx.xxx.xxx.xxx -U <username> -P <password> -Q "BACKUP DATABASE [TestUser] TO DISK='\\server\shared_folder\TestUser.bak';"

You have to give SQL Server Service User the right to write to the remote path \\server\shared_folder\

To do this you have to configure a local samba share connected to the source domain (as a domain member)

Answer 2

If you prefer to save the classic SQL Server database backup (.bak) to another machine, then the only option is to use a BACKUP DATABASE with a shared folder.

If, in general, the backup format is not so important to you, then you can perform Export Data-Tier Application using the sqlpackage utility.

SqlPackage is not a third-party tool, but a Microsoft database migration utility.

sqlpackage.exe /action:Export /SourceServerName:<Server name> /SourceDatabaseName:<db name> /SourceUser:admin /SourcePassword:<password> /TargetFile:<patch to target .bacpac>

Export Data-Tier Application has two drawbacks:

1. Works slower, and loads a database server, because of this, it is poorly applicable for large databases.
2. Does not provide transactional data consistency. To get around this, it is recommended to create a snapshot of the database, and export the snapshot.

CREATE DATABASE AdventureWorks_Snapshot ON  
    ( NAME = AdventureWorks, FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\Data\AdventureWorks_data_1800.ss' )  
    AS SNAPSHOT OF AdventureWorks;

Once the export is completed, the snapshot can be removed.

DROP DATABASE [AdventureWorks_Snapshot]

More details can be found here:

How to Copy SQL Server Database from Windows to Linux

Export and import a database on Linux with SSMS or SqlPackage.exe on Windows