The example of issuing xp_cmdshell to execute a 'NET USE' and then issuing a BACKUP DATABASE won't work because the 'NET USE' is executed outside of the known environment of SQL Server.
The xp_cmdshell starts a separate shell|cmd session, executes the NET USE then closes the shell|cmd session and returns to SQL. So the cmd session that the net use/mapping was applied to no longer exits when the BACKUP DATABASE is executed.
You might try writing a .bat/.cmd file that contains:
SQLCMD -S -E -Q" Backup database....." -o backup.out
OR SQLCMD -S -E -i backup.sql -o backup.out
and then call backup.bat via xp_cmdshell "backup.bat"
But, I don't think that will work either.
The only way to make a mapped drive available to SQL is to have the NET USE execute when the Server/Host starts up, BEFORE the SQL Server service starts, and, the NET USE will need to be initiated under the same credentials as the SQL Server is using.
Maybe set the SQL Service to MANUALLY start and create a Scheduled Task that runs at startup that first maps the NET USE then does a NET START for SQL Server?
But I'm doubtful on that too...
Upon further investigation, it is possible to backup to a UNC path with SQL 2000, but, there are prerequisites:
Backups to a network folder require the following prerequisites:
1) The SQL Server service must be running under a domain-level account.
2) The SQL Server service account must have FULL CONTROL rights to the file system folder and to the share.
3) The remote share should only be accessed via UNC name. Mapped drives may not be consistently visible to the SQL Service.
After that, you can set vars in your SQL script and pass them to the backup command:
DECLARE @FileName AS VARCHAR(100)
DECLARE @Date AS CHAR(8)
SET @Date = CONVERT(CHAR(8), GETDATE(), 112)
SET @FileName = '\BackupHost\BACKUP\dbname' + @Date + '.bak'
BACKUP DATABASE dbname TO DISK = @FileName