Sql-server – A way to take a backup of SQL Server 2000 database on a different domain network

backupsql server

I am trying to take backups of databases on SQL Server 2000. This server is on a different network domain. I can access SQL Server Management Studio using sa login and password from the network domain I work on. I did some research for a work around and tried the following recommended script I read about. 

EXEC master.dbo.xp_cmdshell 'NET USE B:\\xxx\backups\xxxx\sql /user: xxxx\xxxx xxxxxxx'
GO
BACKUP DATABASE [XXX] TO DISK = N'B:\\xxx\backups\xxxx\sql\XXX.bak' WITH NOFORMAT, INIT, NAME = N'XXX-Full Database Backup', SKIP, REWIND, NOUNLOAD, STATS = 10
GO

Failed with an error:

Msg 3201, Device error or device off-line

Is it possible to take a backup on a different network domain using SQL Server Management Studio as in my case? If so, any suggestions.

Best Answer

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:
NET USE
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:
https://support.microsoft.com/en-us/help/555128

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

Related Question