Shulou(Shulou.com)06/01 Report--

SQL Server remote backup error: Operating system error 1326

Problem description:

Execute the following command through SSMS under an account with write access to the remote UNC path:

BACKUP DATABASE master TO DISK ='\\ 192.168.11.108\ szsql01_backup\ SystemDB\ master.bak'

Do not report errors when encountered as follows:

Message 3201, level 16, status 1, line 1

Unable to open backup device'\ 192.168.11.108\ szsql01_backup\ SystemDB\ master.bak'. Operating system error 1326 (login failed: unknown user name or wrong password.) .

Message 3013, level 16, status 1, line 1

BACKUP DATABASE is terminating abnormally.

The SQL Server error log is described as follows:

BackupDiskFile::CreateMedia: Backup device'\ 192.168.11.108\ szsql01_backup\ SystemDB\ master.bak' failed to create. Operating system error 1326

Validation error:

I query the remote shared directory through xp_cmdshell:

Xp_cmdshell 'dir\\ 192.168.11.108\ szsql01_backup\ SystemDB'

The following error will be returned:

Logon failure: unknown user name or bad password.

Solution:

This is an account verification problem. We need to make sure that a disk is mapped to SQL Server. As described in the online help:

"For a network share to be visible to SQL Server, the share must be mapped as a network drive in the session in which SQL Server is running"

Use the following command to map the shared directory to Z:

EXEC xp_cmdshell 'net use Z:\\ 192.168.11.108\ szsql01_backup\ SystemDB xxxxxxxx / User:domain\ user'

After the execution is successful, we query Z: to verify:

EXEC xp_cmdshell 'dir Z:\'

After that, Z: can back up the data as a mapped disk in SQL Server.

BACKUP DATABASE master TO DISK ='Z:\ master.bak'

Read in depth:

Create a backup device or create a startup stored procedure to map disks

Https://blogs.msdn.microsoft.com/varund/2009/06/04/backup-and-restore-sql-server-database-to-a-network-shared-drive/

Other uses:

When the mirror server runs out of disk space, we can map the remote share path to the local disk. Put the mirror database on the mapped disk to do asynchronous mirroring.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.