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 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 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.
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.
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.
So sqlcmd of mssql-tools wouldn't do the job remote? Or maybe with a netshare?
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 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: domainser,
user, user@
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 Ritberg:I dont want a windows user, because I dont want to install Kerberos. I
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:
So sqlcmd of mssql-tools wouldn't do the job remote? Or
maybe with a netshare?
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""
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.
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.
It seems that you are running SQLCMD from a Linux box. On what OS doesWindows 10
SQL Server Express run?
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.
Sysop: | Keyop |
---|---|
Location: | Huddersfield, West Yorkshire, UK |
Users: | 285 |
Nodes: | 16 (2 / 14) |
Uptime: | 62:28:36 |
Calls: | 6,488 |
Calls today: | 1 |
Files: | 12,096 |
Messages: | 5,274,568 |