In-depth Analysis and Resolution of SQL Server 2008 Backup Error 5

Error Phenomenon and Background Analysis

In SQL Server database management practice, backup operations are critical for ensuring data security. However, many administrators encounter Operating System Error 5 during backup tasks, specifically manifested as: Cannot open backup device 'C:\backup.bak'. Operating system error 5(failed to retrieve text for this error. Reason: 15105). This error code 15105 typically indicates insufficient access permissions, requiring thorough investigation at the system level.

Core Problem Diagnosis

The essence of Error 5 is that the SQL Server service account lacks write permissions to the target backup file. In Windows operating systems, each service runs under a specific security context, with SQL Server services defaulting to the NT SERVICE\MSSQL$SQLEXPRESS account (for named instances) or NT SERVICE\MSSQLSERVER account (for default instances). When these accounts lack sufficient permissions to access specified paths, operating system-level access denial errors are triggered.

Taking the C drive root directory as an example, this location typically has strict permission controls, and ordinary service accounts often lack write permissions. The following example code demonstrates checking the current SQL Server service account using PowerShell:

# Get SQL Server service information
Get-WmiObject Win32_Service | Where-Object {$_.Name -like "*SQL*"} | Select-Object Name, StartName, State

Permission Configuration Solutions

The core of resolving permission issues lies in correctly configuring the SQL Server service account's access permissions to the backup directory. Specific operational steps include:

First, identify the running account of the SQL Server service. This can be viewed through SQL Server Configuration Manager or using the following T-SQL query:

-- Query current service account information
EXEC xp_instance_regread 
    N'HKEY_LOCAL_MACHINE', 
    N'SYSTEM\CurrentControlSet\Services\MSSQLSERVER', 
    N'ObjectName'

Second, grant full control permissions to the service account for the backup directory. Taking the C:\Backup directory as an example, right-click the directory, select "Properties", navigate to the "Security" tab, add the SQL Server service account, and grant "Full Control" permissions.

Best Practice Recommendations

To avoid similar permission issues, the following best practices are recommended:

Create dedicated backup directories, avoiding system-sensitive locations such as the C drive root directory. Ideal backup paths should possess the following characteristics:

-- Recommended backup path example
BACKUP DATABASE [YourDatabase] 
TO DISK = N'D:\SQLBackups\YourDatabase_Full.bak'
WITH FORMAT, STATS = 10

Regularly check disk space availability to ensure backup operations have sufficient storage. The following query can be used to monitor disk space:

-- Check disk space
EXEC xp_fixeddrives

Advanced Troubleshooting

When basic permission configurations fail to resolve the issue, more complex scenarios need consideration:

If the SQL Server service runs under a domain account, ensure this account has appropriate permissions on the local computer. Simultaneously, check for group policy restrictions or security software interference.

For network path backups, shared permissions and NTFS permissions require dual verification. The following code demonstrates testing network path accessibility:

-- Test network path access
EXEC xp_cmdshell 'dir \\server\share\backup'

Preventive Maintenance Strategies

Establish a comprehensive backup monitoring system, regularly verifying backup file integrity and recoverability. The following preventive measures are recommended:

Set up automated backup jobs and monitor job execution status through SQL Server Agent. Configure appropriate alert mechanisms to promptly notify administrators when backups fail.

Regularly review and update service account permissions, ensuring alignment with current security policies. Particularly after system upgrades or migrations, all relevant permission settings need revalidation.