1

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.

2 Answers 2

4

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)

1

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.