CODEWITHSUNDEEP
sqlsql-servert-sqlbackup
JayNaz
JayNaz

Reputation: 363

"BACKUP DATABASE" to shared location throws an error

I'm writing a SQL Server stored procedure to backup a database into a network shared location.

The command as follows: (whole D drive has been shared)

BACKUP DATABASE MyDB 
TO DISK = '\\192.168.1.50\d\Backups\MyDb_20200615-09.54.08.BAK'

The command is working fine for a local path, but for the shared path, it throws the following error:

Operating system error 1909(The referenced account is currently locked out and may not be logged on to.).

How to get rid of this error?

Thanks in advance.

Upvotes: 0

Views: 147

Answers (1)

Frank
Frank

Reputation: 489

Yeah, longstanding issue- UNC paths are a big pain with SQL server commands, and often not usable at all. Two possibilities:

  1. Drop the backup onto a local disk and then copy it to your network path.
  2. Map the drive. Note that there are significant and painful access issues because most SQL Server instances run as local SYSTEM and won't have the ability to access network drives.

Edit: the permissions issue is the reason you’re getting locked out. The SYSTEM account credentials won’t work on other machines. You need to create an account with matching credentials on both machines and run the SQL server instance as that account. This can have other implications. It’s easier (and possibly safer) to drop the backup to local disk and copy it with script credentials.

Upvotes: 2

Related Questions