SQL Server: cómo hacer copias de seguridad directamente en unidades de red

Generalmente, lo que más nos interesa a la hora de realizar copias de seguridad es hacerlas hacia alguna máquina o dispositivo especializado de la red local, distintos a la máquina en la que se ejecuta nuestra aplicación o -en nuestro caso concreto- el servidor de datos. Así podremos recuperarlos desde cualquier otra máquina ante cualquier contingencia que surja. En los Data Center (y en muchas oficinas) suelen existir sistemas NAS (Network Attached Storage, almacenamiento en red) cuyo propósito es precisamente albergar las copias de seguridad.

SQL Server, sin embargo, sólo ofrece soporte nativo para realizar copias de seguridad en unidades de disco o dispositvos de backup hardware locales. Esto siempre me ha parecido una seria limitación, ya que hacer copias de seguridad en local no me resulta útil en absoluto. Y tiene muchas limitaciones más (como no comprimir o cifrar las copias), aunque esto es bueno para las empresas que venden herramientas especializadas en ello, como la excelente SQL Backup de Red Gate Software.

Lo que muchos hemos hecho toda la vida ha sido lo siguiente: haces el backup en una carpeta local y programas, un tiempo prudencial después, la ejecución de un archivo .bat que mueva la copia a una unidad de red usando comandos del sistema operativo. Esto funciona pero añade complejidad ya que hay que coordinar ambas acciones y hay más puntos de fallo. Además hay una cuestión adicional que a mi ya me ha ocurrido en servidores viejos: si el disco local no tiene espacio suficiente no puedes hacer copias de seguridad (no te caben), cuando a lo mejor tienes cientos de GB libres en el NAS que no puedes aprovechar :-(

Lo ideal sería hacer la copia directamente en el NAS sin pasar por el disco local. En este post voy a contar cómo podemos conseguir precisamente esto: hacer backups de SQL Server directamente a la red. Además cuento cómo conseguir un backup diario, con un archivo para día de la semana, que se van sobrescribiendo automáticamente, por lo que conseguimos de manera sencilla una retención de 7 días.

Las instrucciones que doy a continuación funcionan con SQL Server 2005 y 2008, y las he sacado a base de prueba y fallo durante bastante tiempo. No he encontrado en Internet instrucciones algunas que contemplen esta operación por completo, sobre todo en lo referente a los pequeños detalles (como la seguridad) que hacen que llegue a funcionar.

1.- Cuenta de ejecución de SQL Server

Lo primero que tenemos que hacer es asegurarnos de que nuestro sistema SQL Server va a tener acceso a la red local. Tanto el motor de bases de datos como el agente de SQL Server se ejecutan suplantando a un determinado usuario del sistema operativo. Mucha gente instala SQL Server para que sus servicios se ejecuten bajo la cuenta de sistema, ya que ésta tiene acceso a cualquier recurso del sistema local, y simplifica la gestión. Esto, aparte de un posible problema de seguridad (en el que no voy a entrar), no es necesario en absoluto. Además hay una cuestión fundamental: la cuenta de sistema no tiene capacidades para acceder a la red. Por lo tanto si nuestro servidor de datos se ejecuta bajo System no podremos realizar copias de seguridad a unidades de red.

La cuenta recomendada para ejecutar SQL Server y conseguir acceso a la red es "Servico de Red" (o, en inglés, "Network Service"). Esta cuenta tiene los permisos suficientes para ejecutar SQL Server sin problema y además nos sirve para nuestro propósito. Lo podemos cambiar desde la configuración de Servicios de SQL Server, en las propiedades de cada servicio:

Si las copias de seguridad las vamos a hacer escribiendo el comando desde el SQL Management Studio, esta cuenta debemos asignarla al motor de SQL Server. Si, como es más común, las copias de seguridad serán automatizadas con el agente de SQL Server, es este servicio el que debe ejecutarse con esta cuenta. En cualquier caso (y sin ser especialista en absoluto en SQL Server), mi recomendación sería que pusiésemos ambos servicios a ejecutarse bajo esta cuenta.

2.- Creación de la cuenta para acceso a la red

Una cosa es la cuenta bajo la que se ejecuta el servidor y otra es la cuenta que usaremos para acceder al recurso de red. Tendrá que ser un usuario que tenga permisos de lectura y escritura en la carpeta compartida en la que queremos escribir el backup. Si no estamos bajo un mismo dominio de Directorio Activo -es decir, utilizamos usuarios diferentes para cada máquina- debemos crear en nuestra máquina local (en la que se ejecuta SQL Server) una cuenta de usuario con el mismo nombre y clave que el que usaremos para acceder a dicho recurso. Por ejemplo, si el NAS tiene un usuario llamado "NAS\Backup" con clave "backup", deberemos crear también en local este mismo usuario. Cuando accedemos interactivamente desde el explorador de Windows al recurso remoto podemos escribir el usuario y la clave en la ventan que aparece, pero con el Script SQL que usaremos aquí, o disponemos del usuario también en local, o no funcionará. El motivo no lo tengo muy claro, pero es así :-(

3.-Habilitar el comando xp_cmdshell

Este comando permite ejecutar comandos del sistema operativo desde scripts T-SQL. Vamos a necesitarlo para habilitar el acceso a los recursos remotos. Por defecto viene desactivado y no podremos usarlo, ya que reviste bastante peligro, puesto que otorga acceso a comandos del sistema que pueden ser muy peligrosos (como formatear el disco duro, por ejemplo). En SQL Serevr 2000 venía habilitado por defecto y las aplicaciones con problemas de seguridad debidas a inyeción SQL y ejecutadas bajo cuentas con demasiados privilegios eran una coladera, por eso en la versión 2005 y posteriores se ha deshabilitado por omisión.

En nuestro caso lo necesitaremos, así que tenemos que habilitarlo. Para ello debemos lanzar las siguientes instrucciones T-SQL desde SQL Management Studio:

-- Permitir el cambio de opciones avanzadas de SQL Server
EXEC sp_configure 'show advanced options', 1
GO
-- Reconfigurar para que permita modificarlas.
RECONFIGURE
GO
-- Habilitar la característica xp_cmdshell
EXEC sp_configure 'xp_cmdshell', 1
GO
-- Refrescar para que el cambio surta efecto
RECONFIGURE
GO

Ya está.

4.- Programar la copia de seguridad

Ahora ya tenemos las bases necesarias para que esto funcione, así que lo único que nos resta es crear una nueva tarea del agente SQL que se encargue de realizar la copia de seguridad. En el apartado de "pasos de la tarea" crearemos un nuevo paso con las siguientes instrucciones T-SQL:

SET LANGUAGE us_english
exec xp_cmdshell 'net use \\192.168.1.1\backups\SQL clave /user:backup'
DECLARE @Archivo AS nvarchar(100)
SET @Archivo = N'\\192.168.1.1\Backups\SQL\MiBaseDeDatos_' + DATENAME(WEEKDAY, GETDATE()) + '.bak'
BACKUP DATABASE [MiBaseDeDatos] TO  DISK = @Archivo WITH NOFORMAT, INIT, 
NAME = N'MiBaseDeDatos-Full Database Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10; exec xp_cmdshell 'net use \\192.168.1.1\Backups\SQL /D'

Lo que estamos haciendo es poner el lenguaje actual en inglés. Yo suelo usar siempre el inglés para todo y tengo los sistemas en este idioma porque considero que tiene muchas ventajas pero tú, claro está, puedes usar el idioma que prefieras. El hecho de establecer el idioma es para asegurarnos de que si transportamos el Script a otro servidor diferente los nombres de los archivos de copia de seguridad van a tener nombres consistentes, ya que usaremos el nombre del día de la semana para crear un archivo .bak cada día (lunes, martes, y así sucesivamente).

El comando xp_cmdshell de la segunda línea habilita la conexión a la carpeta de red \backups\SQL que está en nuestro NAS, con dirección IP 192.168.1.1. Podríamos haber usado el nombre de red (por ejemplo \\NAS o similar), pero con la IP nos aseguramos de que siempre va a funcionar, pues lo otro a veces he detectado que da problemas. En esta línea, por tanto, debes poner la ruta de red que queires usar e indicar la clave y nombre de usuario que usaremos para acceder (ver paso 2).

Las dos siguientes líneas declaran el nombre y la ruta del archivo de backup que vamos a crear. Lo que yo hago aquí es ponerle como sufijo el nombre del día de la semana en inglés, de forma que se me crean copias de seguridad diarias con el nombre "MiBaseDeDatos_Monday.bak", "MiBaseDeDatos_Tuesday.bak", y así sucesivamente. Con esto consigo tener una copia completa cada día de la semana, con una retención de 7 días, que se va sobrescribiendo automáticamente cuando pasa una semana. Para mi esto es más que suficiente, pero si quisieras más retención o más de una copia diaria al día tendrías que buscar una forma alternativa para nombrar los archivos.

La siguiente línea es una instrucción T-SQL normal y corriente para crear una copia de seguridad, sólo que en este caso ya se hará directamente sobre la carpeta de red, y no en local, que es lo que deseábamos.

Finalmente con xp_cmdshell, nos desconectamos del recurso de red. Esto es necesario para que no queden conexiones abiertas y nos impidan volver a reconectar en sucesivas ocasiones.

Espero que te resulte útil y que mis horas de prueba y error te ahorren a ti mucho tiempo. Si me loquieres agradecedr, ya sabes, matricúlate en alguno de nuestros cursos o compra alguno de nuestros libros ;-)

Archivado en:
Comparte este post:

Comentarios

# Fran Díaz said:

qué crack!!

Thursday, January 7, 2010 8:35 AM
# preguntoncojonero said:

qué peligro el xp_cmdshel !!! feliz año !!!

Thursday, January 7, 2010 8:53 AM
# Fran Díaz said:

Después de pensar un rato....por un lado es muy bueno, pero cuando estamos en redes con cortes o aunque no los tenga y pasa, es un peligro no crees?

Saludos!

Thursday, January 7, 2010 8:57 AM
# José M. Alarcón Aguín said:

Hola Fran:

Hombre, en una red local raro será que te quedes sin conexión entre equipos, y además, lo lógico desde mi punto de vista es siempre hacer los backups fuera del equipo en el que residen los datos originales (y si pudieras fuera del mismo edificio)...

Saludos!

JM

Thursday, January 7, 2010 9:52 AM
# Pedro said:

¿Y porque no crear un dispositivo apuntando a la ruta de red de un recurso compartido ?

EXEC master.dbo.sp_addumpdevice  

@devtype = N'disk',

@logicalname = N'LAN',

@physicalname = N'\\myserver\backupfolder\MYBAK.bak'

GO

BACKUP DATABASE [Sample]

TO  [LAN]

Eso si, el usuario tendra que tener permisos de escritura

Thursday, January 7, 2010 10:13 AM
# Fran Díaz said:

Ya ya, en eso estamos de acuerdo :D

Aún así, muy buen post ;)

Saludos!

Thursday, January 7, 2010 10:34 AM
# José M. Alarcón Aguín said:

Hola Pedro:

Gracias por la aportación.

Como bien dices sp_addumpdevice  añade un dispositvo de respaldo en una unidad de red, pero en realidad si el usuario bajo el que ejecutas SQL Server (o el agente) ya tienen permisos para aceder a la red ni siquiera lo necesitarías pues podrías especificar la ruta sin problemas.

Lo que he tratado de explicar es cómo conseguir esto usando una instalación típica de SQL Server que se ejecuta con alguna de las cuentas predeterminadas (en este caso Network Service), y sin tener que crear un usuario específicamente para ejecutar SQL Server y que tenga permisos de acceso a recursos remotos. Es más complicado pero creo que te ayuda a mantener separados la operación normal del gestor de datos y las copias de seguridad, que pienso que no deberían estar tan entrelazados.

En mi opinión debería ser todo más fácil y SQL Server debería ofrecer una opción para suplantar a un usuario específico a la hora de hacer backups (que es en el fondo lo que se hace con esto que explico), lo haría que todo fuese más fácil.

Un saludo

JM

Thursday, January 7, 2010 11:32 AM
# Jose said:

Cordial saludo Jose Manuel, me parece muy bien explicado este post. Quisiera compartirte un problema que tengo a ver si puedes ayudarme, uso SQL Server 2008 y tengo un script que ejecutando comandos de xp_cmdshell en un script sql crea en mi BD todos los procedimientos almacenados que tengo guardados en una ruta, hasta ahi todo muy bien pero tengo un problema y es que la ruta donde estan los archivos debe ser local al servidor donde esta la BD (para cualquier ruta diferente sale este mensaje: The system cannot find the file specified.), esto nos coloca una limitación y también un problema ya que no siempre vamos a poder acceder al servidor local de la BD, la mayoria de las veces los fuentes residen en otro directorio o en otro servidor de BD, como lo puedo solucionar? gracias por tu valiosa ayuda!!

Jose

Thursday, January 7, 2010 9:11 PM
# Bethania Sanz said:

Hola Jose, fijate me entre a este foro buscando ayuda y creo que eres el mejor moderador de foros para encontrar una solucion, fijate es que tengo un problema para realizar un backup y quisiera ver si me puedes ayudar..resulta que tengo un servidor al que no puedo entrar porque han perdido la clave de administrador, me ha tocado recuperar la base de datos sql 05, pero se graba localmente y no puedo acceder, intente agregandole un USB y hacer el backup en el pero no me permite, conoces algun comando o una manera de como puedo recuperar la base de datos antes de que se formatee ese servidor..

Agradezco inmensamente si me puedes ayudar..

Wednesday, January 27, 2010 3:55 PM
# Jonn said:

Una pregunta mi buen amigo, el comando xp_cmdshell se puede usar tambien en server 2008

Thursday, February 25, 2010 7:38 PM
# Francisco said:

Hay una forma mas sencilla y es utilizando una herramienta gratuita llamada ExpressMaint. Les dejo un link donde tienen el detalle completo sobre como hacer el back-up periodico: www.ittraining.com.ar/.../Back-up-periodico-en-SQL-Server-Express-2008-(tambien-en-version-2005).aspx

Sunday, September 5, 2010 4:25 AM
# Andres Restrepo said:

hola, estoy utilizando el codigo que nos das para hacer el backup pero me aparece el siguiente error a que se debe?

Mens. 3201, Nivel 16, Estado 1, Línea 4

Cannot open backup device '\\192.168.2.165\olas\MiBaseDeDatos_Friday.bak'. Operating system error 5(Acceso denegado.).

Mens. 3013, Nivel 16, Estado 1, Línea 4

BACKUP DATABASE is terminating abnormally

gracias por la colaboracion

Friday, March 18, 2011 7:47 PM
# José M. Alarcón Aguín said:

Hola:

Eso es que no tienes permiso de acceso a la ruta.

saludos,

JM

Friday, March 18, 2011 9:34 PM