0

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.

2
  • It's a permissions issue on the network share. The SQL Server service account does not have permissions to it.
    – Tara Kizer
    Apr 20, 2017 at 19:25
  • After your NET USE, you should try using xp_cmdshell to do a dir B:\ to verify you can read the directory, and type nul>>B:\text.txt to verify that you can write to the directory.
    – AMtwo
    Apr 20, 2017 at 19:34

2 Answers 2

1

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

1
  • That seems right. I doubt they will let me run domain account run their sql service and get full control right. so I am going to have to put this on hold and update if i have any new developments
    – Pol
    Apr 24, 2017 at 16:54
0

The backup command likely failed as you do not need the full server path in the backup command. You mapped "\server\folder\xxxx\sql" as "B:". Therefore, just backup to "B:\yourfile.bak".

EXEC master.dbo.xp_cmdshell 'NET USE B: \\xxx\backups\xxxx\sql "password" /user:xxxx\xxxx'
GO
BACKUP DATABASE [XXX] TO DISK = N'B:\XXX.bak' WITH 
NOFORMAT, INIT, NAME = N'XXX-Full Database Backup', SKIP, REWIND, NOUNLOAD, 
STATS = 10
GO
5
  • That did not work. I got the same error
    – Pol
    Apr 20, 2017 at 18:04
  • I am more of looking for any other approach than the one I tried.
    – Pol
    Apr 20, 2017 at 18:05
  • This sounds like a permissions issue. The above script works for me, but I don't have access to SQL 2000. I would create a local shared folder on the \\xxx server. Grant "Everyone" with read/write permissions on that shared folder. Execute sql command: Backup Database [xxx] to Disk = N'\\xxx\sharename\xxx.bak'.
    – Gary
    Apr 20, 2017 at 18:30
  • ok. Are you also trying to take a backup from a server that is on a different network domain?
    – Pol
    Apr 20, 2017 at 18:47
  • Yes. I tested across different domains and subnets. If there is a trust between your domains, and the SQL 2000 instance is running under a domain account, then you can grant read/write permissions on the destination folder to that domain account (SQL service account).
    – Gary
    Apr 20, 2017 at 19:24

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.