How to backup SQL Express 13

8 views
Skip to first unread message

Tim Ritberg

unread,
May 9, 2022, 6:38:25 AMMay 9
to

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

Erland Sommarskog

unread,
May 9, 2022, 8:40:46 AMMay 9
to
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.

Tim Ritberg

unread,
May 9, 2022, 12:41:48 PMMay 9
to

Am 09.05.22 um 14:40 schrieb Erland Sommarskog:
> Tim Ritberg (t...@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

Erland Sommarskog

unread,
May 9, 2022, 4:59:39 PMMay 9
to
Tim Ritberg (t...@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.

Tim Ritberg

unread,
May 10, 2022, 3:15:12 AMMay 10
to
Am 09.05.22 um 22:59 schrieb Erland Sommarskog:
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

Erland Sommarskog

unread,
May 10, 2022, 3:41:44 AMMay 10
to
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.

Tim Ritberg

unread,
May 10, 2022, 3:52:36 AMMay 10
to
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

Erland Sommarskog

unread,
May 10, 2022, 10:13:05 AMMay 10
to
Tim Ritberg (t...@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.

Tim Ritberg

unread,
May 10, 2022, 12:11:50 PMMay 10
to

Am 10.05.22 um 16:13 schrieb Erland Sommarskog:
I think, I need a sql user for this. The account of normal user does not
work.

Tim

Erland Sommarskog

unread,
May 10, 2022, 4:56:21 PMMay 10
to
Tim Ritberg (t...@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?

Tim Ritberg

unread,
May 11, 2022, 3:37:19 AMMay 11
to
Am 10.05.22 um 22:56 schrieb Erland Sommarskog:
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

Anton Shepelev

unread,
May 11, 2022, 5:54:46 AMMay 11
to
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]

Tim Ritberg

unread,
May 11, 2022, 6:21:51 AMMay 11
to
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


Anton Shepelev

unread,
May 11, 2022, 6:40:15 AMMay 11
to
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.

Anton Shepelev

unread,
May 11, 2022, 8:28:44 AMMay 11
to
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.

Erland Sommarskog

unread,
May 11, 2022, 2:00:12 PMMay 11
to
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?

Tim Ritberg

unread,
May 11, 2022, 4:34:58 PMMay 11
to
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

Erland Sommarskog

unread,
May 11, 2022, 4:42:21 PMMay 11
to
Tim Ritberg (t...@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.

Tim Ritberg

unread,
May 11, 2022, 5:02:38 PMMay 11
to
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
Reply all
Reply to author
Forward
0 new messages