1

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.

Improve this question
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
    – Stephen Morris - Mo64
    May 18, 2021 at 17:01
  • 2
    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, 2021 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, 2021 at 17:05
  • you can do it from CMD instead: getfilecloud.com/supportdocs/display/cloud/…
    – Ronaldo
    May 24, 2021 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, 2021 at 18:09

2 Answers 2

Reset to default
1

I know this was 2 years ago, but I only saw it yesterday. If you've got the sa login for the SQL Server, and sudo on your Linux box, there's a way to do it. Here's how I managed to do it between a Windows 10 machine and a Linux Mint guest running in VirtualBox.

Prerequisite: You will need to enable xp_cmdshell which is a bad thing to do (normally), but is necessary in this instance:

exec sp_configure 'show advanced options';
go
reconfigure with override;
go
exec sp_configure 'xp_cmdshell';
go
reconfigure with override;`
go

  1. Create folder in Linux Mint for backups sudo mkdir /backups

  2. Install & start Samba

    sudo apt install samba

    systemctl enable --now smb

  3. Create new group sudo groupadd backups

  4. Add myself to the group sudo usermod -aG backups $USER

  5. Change owner of backups folder (or something...) sudo chgrp -R backups /backups

  6. Set permissions sudo chmod -R g+rw /backups

  7. Create backup user and set password

    sudo useradd backupuser

    sudo passwd backupuser

  8. Add user to backups group sudo usermod -a -G backups backupuser

  9. Edit samba config and put new entry at bottom of file sudo nano /etc/samba/smb.conf

[backups]
   comment = Database Backups
   path = /backups
   browseable = yes
   read only = no
   guest ok = no
   write list = backupuser
   valid users = backupuser

  1. Set Samba password for user (I used same as earlier) sudo smbpasswd -a backupuser

  2. Restart Samba sudo systemctl restart smbd

  3. Run sqlcmd with sa login sqlcmd -S 192.168.56.102 -U sa -P password

  4. Create new share using xp_cmdshell (you should see "Command completed successfully"):

    xp_cmdshell 'net use Z: \\192.168.56.103\backups [password] /user:backupsuser';'

    go

  5. Confirm you can see drive

    xp_cmdshell 'dir Z:\';

    go

  6. Create a backup

    backup database [master] to disk = 'Z:\master_db_202306091337.bak' with init, checksum, compression, stats = 10;

    go

  7. Check that you can see the backup in the share via SQL Server

    xp_cmdshell 'dir Z:\';

    go

  8. Make doubly sure that the backup exists by checking the folder in your Linux client

    ls /backups

Improve this answer
1
  • 1
    Thanks. I wish I had seen this a little earlier. I just had to do this again last week for the first time in a few years and created another hacky-do solution.
    – coolaj86
    Jul 12 at 21:34
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://learn.microsoft.com/en-US/sql/ssms/scripting/generate-scripts-sql-server-management-studio?view=sql-server-ver15

Schema and data.

enter image description here

Improve this answer
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, 2021 at 19:54

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.