Respaldar DB SQLSERVER comunicando 2 servidores a través de xp_cmdshell («Access is denied while copying data over the network»)

ms sqlserver

Realizando una labor de respaldo a través de sqlserver, me encontré con la dificultad de realizar este respaldo no solamente en la máquina donde se encuentran las bases, sino que trasladar las mismas a otro servidor, en este caso un NAS, para mantener los protocolos de seguridad de información y no tener el respaldo en el mismo lugar donde se aloja producción.

Para lograr esto debía programar a través de JOBS de MS Sqlserver, que conectasen ambos servidor para el movimiento de data.

El gran problema que tuve fue que al ejecutar el exec xp_cmdshell para el traspaso de dato me tiraba un error de conección («Access is denied while copying data over the network»), esto se debe a que sqlserver ejecuta el proceso como localmachine, por lo que el otro servidor no ecuentra las credenciales necesarias para autentificar y dar permiso de lectura ante la copia de la data.

Esto se soluciona bajo el siguiente código:

EXEC master..xp_cmdshell "net use t: \\\ /user: /persistent:yes"
EXEC master..xp_cmdshell 'copy D:\Data\file.txt \\MachineB\Documents'
EXEC master..xp_cmdshell "net use t: /delete"


Lo que hacemos con esto es informarle al otro server con que credenciales haremos el movimiento.

Finalemente mi código de respaldo realiza una copia local de la base de datos que se desea respaldar y posteriormente a través del exec xp_cmdhell envía la data hacia el server de destino, posteriormente se puede hacer una limpieza del server local para no ir duplicando la data tanto en el server de respaldo como en el de producción:

declare @user varchar(100)
declare @pass varchar(100)
declare @db varchar(MAX)
set @user = 'xxx'
set @pass = 'yyy'

declare @fecha varchar(MAX)
declare @archivo varchar(MAX)
declare @ruta varchar(MAX)
declare @cmd varchar(8000)
declare @net varchar(8000)

set @fecha = CONVERT(Varchar(max), GETDATE(),102)+'_'+SUBSTRING(CONVERT(varchar(10), getdate(),108),1,2)+SUBSTRING(CONVERT(varchar(10), getdate(),108),4,2)+'_hrs'
------------------------------------------------------------
------ BASE DATOS 1
------------------------------------------------------------
set @db = 'SistemaDB'
set @ruta = 'C:\Ruta\'+@db+'\Diarios\'
set @archivo = @ruta+'\'+@db+'_'+@fecha+'.bak'

BACKUP DATABASE @db
TO DISK = @archivo
WITH FORMAT,
MEDIANAME = 'D_SQLServerBackups',
NAME = 'Full Backup';

set @cmd = 'copy '+ @archivo +' \\Server2\Ruta\'+@db+'\Diarios'
set @net = 'net use t: \\Server2\Ruta '+@pass+' /user:domain\'+@user+' /persistent:yes'
EXEC master..xp_cmdshell @net
EXEC master..xp_cmdshell @cmd
EXEC master..xp_cmdshell 'net use t: /delete'