0

I've got sqlcmd installed on an Ubuntu LTS instance and I'm able to connect successfully to Windows SQL Server and do queries and such.

sqlcmd -S 192.168.0.100 -U SA -P secret

I need to be able to create a backup of the database (preferably as a gigantic db-backup.sql).

It appears that the BACKUP command can only save a .bak file to the remote Windows SQL Server, but I want to save it locally on the Linux client that's actually running sqlcmd.

BACKUP DATABASE [MyDatabaseName]
    TO DISK = N'./MS-SQL-Server-Test-DB.bak'
    WITH NOFORMAT, NOINIT,
    NAME = N'MS-SQL-Server-Test-DB Full Database Backup', SKIP, NOREWIND, NOUNLOAD,  STATS = 10

GO

How can I backup to the local client instead of the remote server?
(I have full sudo access on the Linux client, but only sqlcmd db backup user and SA user access to the Windows Server).

Note: It's Windows Server 2019 or something like that. It doesn't have ssh installed, and I don't think I can get it installed.

6
  • 2
    I can't answer your questions but I can tell you how to approach it. sqlcmd submits the TSQL to the database engine (in your case on the windows machine), so you need to a) make sure that SQL Server is running with a service account that can write to the network b) create a share on linux that windows can access including all the permission things c) change your backup command to write the file to your new linux share May 18 at 17:01
  • 1
    Can you create a Linux share, as suggested by Stephen Morris - Mo64, and Map a network drive in Windows using the option to connect using a different credential?
    – Ronaldo
    May 19 at 0:37
  • @StephenMorris-Mo64 Is it possible to map the network drive with authentication details using sqlcmd / TSQL without access to the Windows GUI?
    – coolaj86
    May 24 at 17:05
  • you can do it from CMD instead: getfilecloud.com/supportdocs/display/cloud/…
    – Ronaldo
    May 24 at 17:10
  • 1
    many commands you execute on CMD can be executed remotely (have you never run a shutdown to turn off a remote computer?) But if you need to do it from SQLCMD you can use xp_cmdshell.
    – Ronaldo
    May 24 at 18:09
0

the backup is done by the sql server process in a place reachable from the server. the options usually are:

  • a local path
  • a networkpath (like a samba or cifs share)
  • an cloud storage

So what you are asking can't be done in the way you want.

As alternative you can script the content:

https://docs.microsoft.com/en-US/sql/ssms/scripting/generate-scripts-sql-server-management-studio?view=sql-server-ver15

Schema and data.

enter image description here

1
  • Thanks, but I need something that works from sqlcmd, not the GUI. I'm on a Linux client accessing a Windows SQL Server.
    – coolaj86
    May 25 at 19:54

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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