Many DBAs encounter a common limitation when attempting to back up SQL Server databases to network locations. While you might have a mapped drive (e.g., M: pointing to \\otherbox\someshare), SQL Server typically blocks backups to UNC paths by default due to security considerations.

SQL Server executes backups under the SQL Server service account context. This account needs proper permissions to access remote locations. The key approaches to enable remote backups are:

-- Example backup statement that would fail without proper configuration
BACKUP DATABASE AdventureWorks 
TO DISK = 'M:\Backups\AdventureWorks.bak'
WITH COMPRESSION, STATS = 10;

The most reliable method is to use UNC paths instead of mapped drives:

-- Working example with UNC path
BACKUP DATABASE AdventureWorks 
TO DISK = '\\fileserver\sqlbackups\AdventureWorks.bak'
WITH COMPRESSION, STATS = 10;

For scenarios requiring mapped drives, you can:

1. Configure the SQL Server service to run under a domain account
2. Grant that account permissions on the remote share
3. Enable xp_cmdshell (note security implications)

-- Enable xp_cmdshell if needed
EXEC sp_configure 'show advanced options', 1;
RECONFIGURE;
EXEC sp_configure 'xp_cmdshell', 1;
RECONFIGURE;

-- Then use xp_cmdshell for backup
EXEC xp_cmdshell 'sqlcmd -S . -Q "BACKUP DATABASE AdventureWorks TO DISK = ''M:\Backups\AdventureWorks.bak''"';

For production environments, consider these robust approaches:

-- Use maintenance plans with UNC paths
-- Or create a SQL Agent job with PowerShell step:
$backupFile = "\\backupserver\sql\$(Get-Date -Format 'yyyyMMdd')_AdventureWorks.bak"
$sql = "BACKUP DATABASE AdventureWorks TO DISK = '$backupFile' WITH COMPRESSION"
Invoke-Sqlcmd -Query $sql -ServerInstance "YourServer"

If you encounter errors, verify:

• The SQL Server service account has write permissions on target share
• Firewall allows SMB traffic (TCP 445)
• Antivirus isn't blocking SQL Server's access
• Sufficient disk space exists on target

For SQL Server 2000 specifically, you might need to use trace flag 1807 to enable network backups:

-- Add to SQL Server startup parameters
-T1807

Many DBAs encounter this common scenario: You want SQL Server (2000/2005/2008) to back up databases to a network share or mapped drive (e.g., M: mapped to \\otherbox\someshare), but the backup operation fails with permission errors. This happens because SQL Server service accounts typically don't have the required permissions to access network resources.

By default, SQL Server prevents backups to remote locations due to security considerations. The SQL Server service runs under a specific account (often Local System or Network Service), and these accounts lack network access privileges. Microsoft intentionally designed this restriction to prevent potential security vulnerabilities.

The most reliable method is to bypass mapped drives entirely and use UNC paths:

BACKUP DATABASE YourDatabase 
TO DISK = '\\otherbox\someshare\backup.bak'
WITH COMPRESSION, STATS = 10;

If you must use mapped drives, follow these steps:

1. Change SQL Server service to run under a domain account (not Local System)
2. Grant this account full control permissions on the network share
3. Ensure the account has backup operator rights on the SQL Server
4. Restart SQL Server services

For legacy systems (not recommended for security reasons):

-- Enable xp_cmdshell if disabled
sp_configure 'show advanced options', 1;
RECONFIGURE;
sp_configure 'xp_cmdshell', 1;
RECONFIGURE;

-- Execute backup via command line
EXEC xp_cmdshell 'sqlcmd -S . -Q "BACKUP DATABASE YourDB TO DISK = ''M:\backup.bak''"';

• Always test backup/restore operations after configuration
• Monitor backup durations - network backups may be slower
• Consider using maintenance plans for scheduled backups
• Document your backup locations and permissions

If backups still fail:

-- Check SQL Server error logs
EXEC xp_readerrorlog;

-- Verify share permissions
EXEC xp_cmdshell 'dir \\otherbox\someshare';