• How to backup SQL Express 13

    From Tim Ritberg@21:1/5 to All on Mon May 9 12:38:20 2022
    Hi!

    A friend has got a software, which uses a SQL Express instance.
    We would like to backup one database via external sql dump.

    How to setup a sql user?
    We have SQL Studio installand and can connect via windows user.

    Tim

    --- SoupGate-Win32 v1.05
    * Origin: fsxNet Usenet Gateway (21:1/5)
  • From Erland Sommarskog@21:1/5 to Tim Ritberg on Mon May 9 14:40:41 2022
    Tim Ritberg (tim@server.invalid) writes:
    A friend has got a software, which uses a SQL Express instance.
    We would like to backup one database via external sql dump.

    How to setup a sql user?
    We have SQL Studio installand and can connect via windows user.


    Not sure why you would set up an SQL user. To backup the databaes
    you would say;

    BACKUP DATABASE TheDB TO DISK = 'C:\temp\TheDB.bak' WITH INIT

    The file path is of course an example. However, the service account
    for SQL Server needs to have access to the location, so it's not a
    good idea to select a folder under C:\Users.

    --- SoupGate-Win32 v1.05
    * Origin: fsxNet Usenet Gateway (21:1/5)
  • From Tim Ritberg@21:1/5 to All on Mon May 9 18:41:44 2022
    Am 09.05.22 um 14:40 schrieb Erland Sommarskog:
    Tim Ritberg (tim@server.invalid) writes:
    A friend has got a software, which uses a SQL Express instance.
    We would like to backup one database via external sql dump.

    How to setup a sql user?
    We have SQL Studio installand and can connect via windows user.


    Not sure why you would set up an SQL user. To backup the databaes
    you would say;

    To connect from another host, which is doing backup.


    Tim

    --- SoupGate-Win32 v1.05
    * Origin: fsxNet Usenet Gateway (21:1/5)
  • From Erland Sommarskog@21:1/5 to Tim Ritberg on Mon May 9 22:59:32 2022
    Tim Ritberg (tim@server.invalid) writes:
    To connect from another host, which is doing backup.


    And what do you want the final result to be? Where do you want the backup?
    And I don't still don't see why you would a separate user. Then again, I
    have a feeling that you have not told the full story yet.

    --- SoupGate-Win32 v1.05
    * Origin: fsxNet Usenet Gateway (21:1/5)
  • From Tim Ritberg@21:1/5 to All on Tue May 10 09:15:08 2022
    Am 09.05.22 um 22:59 schrieb Erland Sommarskog:
    Tim Ritberg (tim@server.invalid) writes:
    To connect from another host, which is doing backup.


    And what do you want the final result to be? Where do you want the backup? And I don't still don't see why you would a separate user. Then again, I
    have a feeling that you have not told the full story yet.

    Same procedure like mysqldump. I dont know which format is best for
    MsSQL, a SQL dump or a binary file? Just some file to store somewhere
    else, but not that desktop pc.


    Tim

    --- SoupGate-Win32 v1.05
    * Origin: fsxNet Usenet Gateway (21:1/5)
  • From Tim Ritberg@21:1/5 to All on Tue May 10 09:52:32 2022
    Am 10.05.22 um 09:41 schrieb Erland Sommarskog:

    Sorry, this is a newsgroup for Microsoft SQL Server. You can't expect
    people here to know what mysqldump is. I have a vague recollection that
    is actually a list of INSERT statements. A backup that you take with
    the BACKUP statement in SQL Server is indeed a binary file.

    Since it is SQL Server that writes the backup, the backup must be written
    to a destination where SQL Server has write access, either local disk
    or a file share. But it is a good idea to copy the backup elsewhere,
    once you have taken it.


    So sqlcmd of mssql-tools wouldn't do the job remote? Or maybe with a
    netshare?

    Tim

    --- SoupGate-Win32 v1.05
    * Origin: fsxNet Usenet Gateway (21:1/5)
  • From Erland Sommarskog@21:1/5 to Tim Ritberg on Tue May 10 09:41:38 2022
    Tim Ritberg (tim@server.invalid) writes:
    Am 09.05.22 um 22:59 schrieb Erland Sommarskog:
    Tim Ritberg (tim@server.invalid) writes:
    To connect from another host, which is doing backup.


    And what do you want the final result to be? Where do you want the
    backup? And I don't still don't see why you would a separate user. Then
    again, I have a feeling that you have not told the full story yet.

    Same procedure like mysqldump. I dont know which format is best for
    MsSQL, a SQL dump or a binary file? Just some file to store somewhere
    else, but not that desktop pc.

    Sorry, this is a newsgroup for Microsoft SQL Server. You can't expect
    people here to know what mysqldump is. I have a vague recollection that
    is actually a list of INSERT statements. A backup that you take with
    the BACKUP statement in SQL Server is indeed a binary file.

    Since it is SQL Server that writes the backup, the backup must be written
    to a destination where SQL Server has write access, either local disk
    or a file share. But it is a good idea to copy the backup elsewhere,
    once you have taken it.

    Yes, you can can also generate INSERT statemnents, but such a dump may not
    be transactionally consistent. This is why you want a real backup.

    --- SoupGate-Win32 v1.05
    * Origin: fsxNet Usenet Gateway (21:1/5)
  • From Erland Sommarskog@21:1/5 to Tim Ritberg on Tue May 10 16:13:00 2022
    Tim Ritberg (tim@server.invalid) writes:
    So sqlcmd of mssql-tools wouldn't do the job remote? Or maybe with a netshare?


    You can use SQLCMD to run the BACKUP statement, just like you can use
    it to run any other T-SQL command.

    --- SoupGate-Win32 v1.05
    * Origin: fsxNet Usenet Gateway (21:1/5)
  • From Tim Ritberg@21:1/5 to All on Tue May 10 18:11:47 2022
    Am 10.05.22 um 16:13 schrieb Erland Sommarskog:
    Tim Ritberg (tim@server.invalid) writes:
    So sqlcmd of mssql-tools wouldn't do the job remote? Or maybe with a
    netshare?


    You can use SQLCMD to run the BACKUP statement, just like you can use
    it to run any other T-SQL command.

    I think, I need a sql user for this. The account of normal user does not
    work.

    Tim

    --- SoupGate-Win32 v1.05
    * Origin: fsxNet Usenet Gateway (21:1/5)
  • From Erland Sommarskog@21:1/5 to Tim Ritberg on Tue May 10 22:56:17 2022
    Tim Ritberg (tim@server.invalid) writes:
    I think, I need a sql user for this. The account of normal user does not work.


    What error message do you get?

    --- SoupGate-Win32 v1.05
    * Origin: fsxNet Usenet Gateway (21:1/5)
  • From Tim Ritberg@21:1/5 to All on Wed May 11 09:37:14 2022
    Am 10.05.22 um 22:56 schrieb Erland Sommarskog:
    Tim Ritberg (tim@server.invalid) writes:
    I think, I need a sql user for this. The account of normal user does not
    work.


    What error message do you get?

    I tried this, with different account formats: domain\user, user, user@

    sqlcmd -H office2 -U user -Q "BACKUP DATABASE [contura] TO DISK = N'/var/backup/demodb.bak' WITH NOFORMAT, NOINIT, NAME = 'demodb-full',
    SKIP, NOREWIND, NOUNLOAD, STATS = 10"

    Sqlcmd: Error: Microsoft ODBC Driver 17 for SQL Server :
    Translated: "login error with user "user""


    Tim

    --- SoupGate-Win32 v1.05
    * Origin: fsxNet Usenet Gateway (21:1/5)
  • From Anton Shepelev@21:1/5 to All on Wed May 11 12:54:42 2022
    Tim Ritberg:

    I tried this, with different account formats: domainser,
    user, user@

    If you want to login as a Windows user, rather than an SQL
    user, avoid both -U and -P. This is well documented:

    ,----[https://docs.microsoft.com/en-us/sql/tools/sqlcmd-utility:]
    | If neither the -U option or the -P option is specified,
    | sqlcmd tries to connect by using Microsoft Windows
    | Authentication mode. Authentication is based on the
    | Windows account of the user who is running sqlcmd. `---------------------------------------------------

    It is always better to consult the documentation than to try
    things at random.

    --
    () ascii ribbon campaign - against html e-mail
    /\ http://preview.tinyurl.com/qcy6mjc [archived]

    --- SoupGate-Win32 v1.05
    * Origin: fsxNet Usenet Gateway (21:1/5)
  • From Anton Shepelev@21:1/5 to All on Wed May 11 13:40:11 2022
    Tim Ritberg:

    I dont want a windows user, because I dont want to install
    Kerberos.

    Then the only alternative I know is an SQL user -- a user
    with login and password registered inside an MSSQL instance.
    You can pass his credentials via -U and -P.

    I guees, I have to setup this user first.

    I don't know. Login to your MSSQL instance via Management
    Studio and see what users are present under

    <instance> -> Security -> Logins

    If there are no SQL users (which is strange) go ahead and
    create one, with all the required permissions. In my
    experiece, however, there is always an administrator-user
    `sa'.

    But I do not have any admin user. Could that be a local
    windows admin?

    Since you said you do not want authentification by a Windows
    user, it cannot. Create a proper MSSQL user.

    --
    () ascii ribbon campaign - against html e-mail
    /\ http://preview.tinyurl.com/qcy6mjc [archived]

    --- SoupGate-Win32 v1.05
    * Origin: fsxNet Usenet Gateway (21:1/5)
  • From Tim Ritberg@21:1/5 to All on Wed May 11 12:21:46 2022
    Am 11.05.22 um 11:54 schrieb Anton Shepelev:
    Tim Ritberg:

    I tried this, with different account formats: domainser,
    user, user@

    If you want to login as a Windows user, rather than an SQL
    user, avoid both -U and -P. This is well documented:
    I dont want a windows user, because I dont want to install Kerberos. I
    guees, I have to setup this user first.
    But I do not have any admin user. Could that be a local windows admin?

    Tim

    --- SoupGate-Win32 v1.05
    * Origin: fsxNet Usenet Gateway (21:1/5)
  • From Anton Shepelev@21:1/5 to All on Wed May 11 15:28:40 2022
    Tim Ritberg:

    So sqlcmd of mssql-tools wouldn't do the job remote? Or
    maybe with a netshare?

    It will work remotely with an SQL user. The path supplied
    to the backup statement is relative to the machine on which
    MSSQL instance is running. You are not required to make it
    available to the machine whence you invoke `sqlcmd', but it
    may be useful in order to save connecting to the Windows
    machine to get the backup files. I have used Samba to mount
    a Windows share on a Linux machine for two-way interaction.

    --
    () ascii ribbon campaign - against html e-mail
    /\ http://preview.tinyurl.com/qcy6mjc [archived]

    --- SoupGate-Win32 v1.05
    * Origin: fsxNet Usenet Gateway (21:1/5)
  • From Erland Sommarskog@21:1/5 to Tim Ritberg on Wed May 11 20:00:05 2022
    Tim Ritberg (tim@server.invalid) writes:
    I tried this, with different account formats: domain\user, user, user@

    sqlcmd -H office2 -U user -Q "BACKUP DATABASE [contura] TO DISK = N'/var/backup/demodb.bak' WITH NOFORMAT, NOINIT, NAME = 'demodb-full',
    SKIP, NOREWIND, NOUNLOAD, STATS = 10"

    Sqlcmd: Error: Microsoft ODBC Driver 17 for SQL Server :
    Translated: "login error with user "user""

    And where do you get this name "user" from? I mean, since the server
    apparently is a user, there ia a user that can log in. Why not use that one?

    It seems that you are running SQLCMD from a Linux box. On what OS does
    SQL Server Express run?

    --- SoupGate-Win32 v1.05
    * Origin: fsxNet Usenet Gateway (21:1/5)
  • From Erland Sommarskog@21:1/5 to Tim Ritberg on Wed May 11 22:42:16 2022
    Tim Ritberg (tim@server.invalid) writes:
    I startet Server Manager Studio as local admin and created a user for sql.

    This is my backup command:
    sqlcmd -U remotebackup -P foobar -S tcp:office2,49762 -Q "BACKUP
    DATABASE [contura] TO DISK = 'testback' WITH NOFORMAT, NOINIT, NAME = 'demodb-full', SKIP, NOREWIND, NOUNLOAD, STATS = 10"

    Strange that port 49762 and backup went into a sqlserver subfolder. I
    guess sql server can't write directly to net shares.


    The port number is instead of the instance name. Which you need if the
    Browser server is not running or blocked by firewall.

    It went to that folder, because you did not specify the full path. You can change the default path from SSMS under Server Properties, but I don't
    really recommend that.

    SQL Server can write to net shares - but the service account for SQL Server needs to have write access to the share.

    --- SoupGate-Win32 v1.05
    * Origin: fsxNet Usenet Gateway (21:1/5)
  • From Tim Ritberg@21:1/5 to guess sql server can't on Wed May 11 22:34:53 2022
    Am 11.05.22 um 20:00 schrieb Erland Sommarskog:

    And where do you get this name "user" from? I mean, since the server apparently is a user, there ia a user that can log in. Why not use that one?
    Now I got it.
    I startet Server Manager Studio as local admin and created a user for sql.

    This is my backup command:
    sqlcmd -U remotebackup -P foobar -S tcp:office2,49762 -Q "BACKUP
    DATABASE [contura] TO DISK = 'testback' WITH NOFORMAT, NOINIT, NAME = 'demodb-full', SKIP, NOREWIND, NOUNLOAD, STATS = 10"

    Strange that port 49762 and backup went into a sqlserver subfolder. I
    guess sql server can't write directly to net shares.


    It seems that you are running SQLCMD from a Linux box. On what OS does
    SQL Server Express run?
    Windows 10


    Tim

    --- SoupGate-Win32 v1.05
    * Origin: fsxNet Usenet Gateway (21:1/5)
  • From Tim Ritberg@21:1/5 to All on Wed May 11 23:02:35 2022
    Am 11.05.22 um 22:42 schrieb Erland Sommarskog:
    SQL Server can write to net shares - but the service account for SQL Server needs to have write access to the share.
    Nevermind, I'll setup a user task and oh wait.
    I have to change file permissions. I'll do some stuff and move that
    elsewhere ;-)

    Tim

    --- SoupGate-Win32 v1.05
    * Origin: fsxNet Usenet Gateway (21:1/5)