copias de seguridad automáticos de sql server express

8/18/2019 Copias de Seguridad Automáticos de SQL Server Express

1/21

Backups automáticos de SQL Server Express• Backup SQL Server Express

• SQL Server Express

Es bien sabido que las versiones Express de SQL Server no disponen del agente de SQL Server para programar trabajos que se encarguen, entre otras cosas, de los backups de las diferentes bases de datos.

Hace das, buscando sobre el tema encontr! una soluci"n mu# simple que vo# a compartir #explicar en !sta entrada.

$ara %acer backups autom&ticos de las bases de datos de SQL Server Express, basta seguir los

siguientes pasos'

• Entrar a SQL Server (anagement Studio Expres

• )esplegar el nodo Server Objects # seleccionar el nodo Backup Devices como se indicaen la imagen.

http://www.juanfelipe.net/taxonomy/term/81http://www.juanfelipe.net/taxonomy/term/80http://www.juanfelipe.net/taxonomy/term/80http://www.juanfelipe.net/taxonomy/term/81


2/21

• *lic derec%o sobre Backup Devices # seleccionar la opci"n New Backup Device...

• Sobre el campo Device name usar un nombre de dispositivo que se va a usar para el backup. +o uso como nombre de dispositivo el mismo nombre de la base de datos a lacual le vo# %acer backup con este procedimiento, pero es indiferente. Si quiere puedellamarlo potato. o importa.Seleccionamos la opci"n Fie para %acer backup al disco duro # %acemos clic en el bot"n... para seleccionar la ruta de destino del backup.


3/21


4/21

• La configuraci"n del dispositivo de Backup debe quedar como indica la siguiente imagen.$ara finali7ar %acemos clic en el bot"n O$ .


5/21

•

El dispositivo de backup que se acaba de crear debe verse listado como lo indica lasiguiente imagen.


6/21

• $ara %acer backup en este dispositivo ejecutamos el siguiente comando en una lnea decomandos'

• sqlcmd -S \ -E -Q "BACKUP DAABASE ! "

)onde'

o 8Servidor9' 4ndica el nombre del servidor SQL.

o 84nstancia9' *orresponde al nombre de la instancia de SQL. Si el servidor SQL notiene instancia, omita este dato.

o 8Base de )atos9' 4ndica el nombre de la base de datos a la cual se le %ar& backup.

o 8)ispositivo de backup9' ombre del dispositivo de backup en el cu&l %ar& el backup.

$ara este caso concreto vo# %acer backup de la base de datos NavoriSQL en el


7/21

dispositivo de backup creado en este procedimiento que lleva su mismo nombre. $orejemplo'

sqlcmd -S SE&'ID!& -E -Q "BACKUP DAABASE (avoriSQ) ! (avoriSQ)"

En el directorio seleccionado para los backups, debe quedar el backup de la base de datos.


8/21

• El :ltimo paso sera insertar en un arc%ivo .B;3 el comando que reali7a el backup #mediante as tareas pro%ramas de &indows programar la ejecuci"n de este arc%ivo.B;3

• *abe apuntar que este procedimiento es v&lido para cualquier versi"n de SQL Server. osolo se limita a las versiones Express.

SQL Server Express: Hacer backups programados y con

retención SQL Server Express es una excelente opci"n para trabajar con SQL Server en pro#ectos peque


9/21

disponibilidad # replicaci"n, pero ofrece toda la funcionalidad %abitual -inclu#endo integraci"nde datos # reporting # las %erramientas de administraci"n. ;qu podr&s encontrar unacomparativa de todas las ediciones de SQL Server .

1na de las cosas que no est&n disponibles con SQL Server es el '%ente SQL. El agente nos

permite programar tareas que se ejecutar&n sobre las bases de datos cuando nosotros queramos.Esta carencia dificulta un poco, por ejemplo, la reali7aci"n de copias de seguridad, especialmentesi queremos mantener un periodo de retenci"n concreto -por ejemplo, las copias de los :ltimos Adas.

$or suerte esta carencia en concreto es mu# f&cil de solucionar, # en este artculo vo# a explicar c"mo lograrlo de manera sencilla.

Lo primero que tenemos que saber es que todas las ediciones de SQL Server inclu#en unautilidad de lnea de comandos que nos permite ejecutar instrucciones 3SQL arbitrarias contra

cualquier base de datos. Se trata de SQL()D.exe, generalmente ubicada en esta ruta'

"C*\Pro+ram ,iles\icroso.t SQ) Server\//0\ools\Binn\SQ)CD1E2E"

en el caso de SQL Server C@=C Express.

Esta utilidad tiene muc%os par&metros que nos permiten controlar su forma de trabajar. )os quenos interesan especialmente son'

• -S: nos permite especifcar contra qué servidor/instancia se ejecutarán las

sentencias T-SQL.

• -i: permite especifcar una ruta a un archivo (normalmente con etensi!n .sql"que contiene las instrucciones T-SQL que queremos ejecutar contra elservidor. #s$ podemos incluir scripts más complejos que una simple l$nea.

Sabiendo la existencia de esta %erramienta, conseguir backups gracias a ella es mu# sencillo.

1. Construir as instrucciones !-SQL base para "acer e backup

Lo primero es conseguir los comandos 3SQl para %acer un backup. Lo m&s sencillo es usar las%erramientas integradas en el )icroso*t SQL Server )ana%ement Studio -(SS(S. Dbrelo,

busca la base de datos que te interesa copiar en el explorador de objetos # pulsa el bot"n derec%odel rat"n sobre ella. En el men: contextual elige la opci"n de 3areasFbackupG'

http://msdn.microsoft.com/en-us/library/cc645993(v=SQL.110).aspxhttp://msdn.microsoft.com/en-us/library/ms162773.aspxhttp://msdn.microsoft.com/en-us/library/cc645993(v=SQL.110).aspxhttp://msdn.microsoft.com/en-us/library/ms162773.aspx


10/21


11/21

)esde esta ventana elegimos la base de datos a copiar # la ruta en la que queremos guardar dic%acopia de seguridad -normalmente le damos como extensi"n al arc%ivo .bak, pero puede ser cualquiera o incluso no tener extensi"n.

;dem&s si pulsamos en la p&gina 0pciones en el lateral podemos configurar algunas cosas m&s,como por ejemplo -mu# recomendable que se verifique el backup al terminar de %acerlo'


12/21


13/21

*on esto obtendramos una base de datos que se sobrescribira en cada nueva copia, Lointeresante de las copias de seguridad es tener copias con una retenci"n de varios das, para poder comprobar datos anteriores o restaurar los datos a un estado anterior.

#. $etocar e script para dare una semana de retención

Supongamos que queremos %acer una copia de seguridad diaria # que queremos mantener lascopias durante A das, de modo que podamos recuperar los datos desde cualquier copia de

seguridad de la :ltima semana. $ara ello vamos a retocar el script anterior de modo que cada dale cambie el nombre al arc%ivo de copia de seguridad. $ara ello vamos a declarar una variableque servir& para guardar la ruta # el nombre del arc%ivo de copia de seguridad, cambi&ndolo enfunci"n, en este caso, del da de la semana en el que nos encontremos. En este caso sera as'

DEC)A&E 3dest nvarc4ar56778

SE 3dest 9 :C*\Bac$%sBBDD\SE),;: CAS5DAEPA&5=ee$da? @EDAE588 AS nvarc4ar5/88 :1#a$:

La funci"n D',E-', con el valor weekda/ para el primer par&metro nos devuelve un n:mero para cada da de la semana, empe7ando por el domingo -un = %asta el s&bado -un A. *omo le pasamos la fec%a actual -!E,D',E como segundo par&metro lo que obtendremos en la

variable dest es cada da un nombre diferente para la base de datos, a


14/21

)ado que en caso de que un arc%ivo exista de backup se sobrescribir&, en la pr&ctica con estescript lo que conseguimos es que siempre %a#a A copias como m&ximo en el %ist"rico.

%. Crear un bat para reai&ar e backup

;%ora que #a tenemos el c"digo necesario para crear las copias de seguridad lo que debemos

%acer es crear un arc%ivo .bat que nos permita ejecutar este c"digo 3SQL cuando queramos. $araello usaremos SQL*().E2E, escribiendo esta instrucci"n'

"C*\Pro+ram ,iles\icroso.t SQ) Server\//0\ools\Binn\SQ)CD1E2E"

-S SERVIDOR\INSTANCIA -i "C*\Bac$%sBBDD\Bac$%SE),1sql" >>

lo+1tt

)ebemos sustituir SE?4)0?M4S3;*4; por el nombre de nuestro servidor # la instancia deSQL Server sobre la que queremos trabajar. En el par&metro i debemos indicar la ruta alarc%ivo .sql con las instrucciones para la copia de seguridad que acabamos de crear.

La :ltima instrucci"n 99 log.txt nos permite guardar el resultado de la ejecuci"n en un arc%ivode texto que podemos consultar para ver cu&ndo se %a reali7ado cada copia, cu&nto %a tardado #cualquier otro mensaje que se derive de la ejecuci"n del script. E spor eso que me gusta colocarleal principio del script una instrucci"n m&s como esta'

P&I( CAS5@EDAE58 AS nvarc4ar8 : - C!PIA DE SE@U&IDAD I(ICIADA A) A&CI'!* : 3dest

)e este modo aparecer& en el arc%ivo Log.txt un mensaje al principio de cada copia de seguridadindicando la fec%a de creaci"n # el nombre del arc%ivo. $odemos incluir del mismo modocualquier otra informaci"n que consideremos relevante.

'. (rogramar a tarea

;%ora que #a tenemos un script para %acer la copia de seguridad, # adem&s %emos creado un .bat para ejecutarlo, lo :nico que nos falta es crear una tarea programada para poder lan7arla con la periodicidad que nos convenga -en principio cada da.

$ara ello abrimos el administrador de tareas programadas del sistema # creamos una nueva tarea.Lo :nico que tendremos que %acer es indicar que queremos ejecutar el arc%ivo .bat del pasoanterior as como a qu! %ora del da lo vamos a %acer'


15/21

*on esto %abremos conseguido que todos los das a las C'@@ de la ma


16/21

*ada uno de esos arc%ivos se corresponde con la copia de seguridad del domingo -=, lunes -C,martes NK, etcG

Si quisi!semos un periodo de retenci"n de un mes, por ejemplo, sera tan f&cil como cambiar el par&metro de );3E$;?3 por da# de modo que se pusiera el n:mero de da del mes. $odemos jugar con los distintos valores del primer par&metro de );3E$;?3 para conseguir otros periodos, como por ejemplo, si %acemos m&s de una copia al da, a


17/21

Crear copia de seguridad programada en S) Server Express

#**+*omo muc%os #a os %abr!is dado cuenta, la versi"n Sql Server Express C@@N no inclu#e el agentede tareas. + para que sirve el agente de tareasR os preguntareis otros. $ues bien el agente de

tareas es el que se encarga de llevar a cabo todas las tareas relacionadas con el mantenimiento dela base de datos #a sea reali7ar copias de seguridad programadas -diarias, semanales,mensualesG, reali7ar una programaci"n para limpiar registros, indexar la base de datos, etcG

En nuestro caso lo que buscamos de manera m&s urgente es poder reali7ar copias de seguridad programadas para evitar accidentes como la p!rdida de informaci"n o borrado accidental o no dela base de datos. Siempre que trabajemos con una base de datos %a# que tener un respaldo por siacaso el ordenador falla # %ubiera que restaurarla en otro pc o para poder evitar cualquier p!rdida

accidental de los datos.

$ara reali7ar este ejercicio, es necesario un conocimiento previo del lenguaje SQL. $or ellovamos a ver previamente como se reali7ara una copia de seguridad completa normal de una basede datos a una ruta concreta'

B;*1$ );3;B;SE nombredelabasededatos30 )4S T U*'Mnombredelabasededatos.bakVW43H I0?(;3X

El funcionamiento es reali7ar la copia de seguridad con el nombre de la base de datos quequeremos respaldar e indicar la ruta en la que queremos que se guarde la copia de seguridad -porejemplo en c'M. Lo que se encuentra en letra naranja es lo que tendremos que cambiar paraindicar el nombre de la base de datos a respaldar # el nombre de la copia de seguridad.

1na ve7 tenemos claro el funcionamiento de la copia de seguridad, vamos a darle una fec%a antesdel nombre para poder crear una copia cada da # poder distinguirlas en formato a


18/21

;qu lo que %emos %ec%o es declarar dos variables una con la ruta # otra con la fec%a para quequede claro su uso de manera mu# sencilla. )eclaramos las variables como nvarc%ar,establecemos el valor con set que van a tener cada una, en el caso de la fec%a %a# que convertir elformato datetime que usa currentJtimestamp a nvarc%ar que es cadena # luego lo utili7aremos enese formato para anteponerlo al nombre # as distinguir los diversos arc%ivos de las copias de

seguridad. + en el caso de la variable ruta %emos indicado simplemente la ruta donde se va aguardar el arc%ivo con su fec%a # nombre correspondiente.

;%ora vamos a crear un arc%ivo .bat para ejecutar el c"digo mediante la consola de comandos de\indo\s conocida como *() # as con el programador de tareas de \indo\s poder programar la tarea.

= *reamos con el bloc de notas un arc%ivo # lo llamamos ]tarea.bat^ -sin las comillas. Ledamos bot"n derec%o # editar.

Editar .bat

C 1na ve7 estemos en modo edici"n, insertamos el siguiente c"digo'

E*H0 ejecutando tareaE*H0. pauseosql S ]nombredelainstanciasqlMsqlexpress^ E Q ]declare ruta as nvarc%ar -Z@Xdeclarefec%a as nvarc%ar -Z@Xset fec%a T *0E?3-nvarc%ar, *1??E3J34(ES3;($ , ==Csetruta T U*'MV [ fec%a [ Unombredelabasededatos.bakV B;*1$ );3;B;SEnombredelabasededatos 30 )4S T ruta W43H I0?(;3X^E*H0 tarea ejecutada pause*LSE243

K *ambiamos lo que est& escrito en naranja por lo que corresponda a la informaci"n de nuestrainstancia de Sql Server Express C@@N -suele ser el nombre del equipo Ysqlexpress, a no ser que se%a#a cambiado en la instalaci"n # nombre de la base de datos a respaldar.

O Ejecutamos el arc%ivo para comprobar que no %emos cometido ning:n error de c"digo.


19/21

*md

Z Si nos aparece como en la imagen # nos crea la copia de seguridad con !xito tendremos que%acer un cambio en el c"digo para quitarle las pausas de comprobaci"n # que lo %agadirectamente sin pulsar ninguna tecla. Si nos fijamos en la imagen %a# unas flec%as que nosindican lo que en el c"digo significa pause. ;s que volvemos al fic%ero # lo editamos borrandolas pause, nos quedara as'

E*H0 ejecutando tareaE*H0.sqlcmd S ]nombredelainstanciasqlMsqlexpress^ E Q ]declare ruta as nvarc%ar -Z@Xdeclarefec%a as nvarc%ar -Z@Xset fec%a T *0E?3-nvarc%ar, *1??E3J34(ES3;($ , ==Csetruta T U*'MV [ fec%a [ Unombredelabasededatos.bakV B;*1$ );3;B;SEnombredelabasededatos 30 )4S T ruta W43H I0?(;3X^E*H0 tarea ejecutada*LSE243

;%ora vamos como paso final a programar la tarea para que se ejecute a diario.

= $ara ello entramos en 4nicio _ $rogramas _ ;ccesorios _ Herramientas del sistema # pulsamosclick en 3areas programadas.


20/21

3areas $rogramadas

C Le damos a agregar una nueva tarea # cuando nos salga el asistente pulsamos siguiente.Seleccionamos en examinar el arc%ivo tarea.bat que %emos creado # pulsamos siguiente de

nuevo.

K $ulsamos en la siguiente pantalla a diario para que se ejecute todos los dias # le damos asiguiente.

O ;%ora es el momento de seleccionar que %ora # con que frecuencia as como cuando se debeiniciar la tarea. $ulsamos en siguiente una ve7 configurado # nos va a pedir un nombre de usuariodel equipo # una contrase


21/21

)e este modo quedara completado el captulo de como crear una programaci"n de un backup enSql Server Express C@@N.

Espero que os sirva # as poder %aberos a#udado.

'(,0'L12'(13N"1mportante

Esta caracterstica se quitar& en una versi"n futura de SQL Server. Evite utili7ar estacaracterstica en los nuevos trabajos de programaci"n # planee modificar las aplicaciones queactualmente la utili7an. sqlcmd instead.^9En su lugar, use s4cmd. sqlcmd 1tilit#.^9$ara obtenerm&s informaci"n, vea sqlcmd -utilidad.

%ttp'YYmsdn.microsoft.comYesesYlibrar#[email protected]

)ebido a las :ltimas peticiones %e decidido incluir el Script para %acer backup de todas las basesde datos de usuario, recordad que para que funcione deb!is cambiar el nombre de la instancia'

E*H0 ejecutando tareaE*H0.sqlcmd S ]nombredelainstanciasqlMsqlexpress^ E Q ])E*L;?E BB))1suariosJcursor*ursor I0? SELE*3 name I?0( master.dbo.s#sdatabases WHE?E name 03 4-UmasterV,VmodelV,VmsdbV,VtempdbV declare nombre)B varc%ar-Z@Xdeclare ruta as nvarc%ar-Z@X declare fec%a as nvarc%ar -Z@X set fec%a T *0E?3-nvarc%ar,*1??E3J34(ES3;($ , ==C 0$E BB))1suariosJcursor Ietc% E23 I?0(BB))1suariosJcursor 430 nombre)B WH4LE IE3*HJS3;31S T @ BE>4 set

ruta T U*'MBackupMV [ fec%a [ UJV [ nombre)B [ U.bakV B;*1$ );3;B;SEnombre)B 30 )4S T ruta W43H I0?(;3X Ietc% E23 I?0( BB))1suariosJcursor430 nombre)B E) *L0SE BB))1suariosJcursor )E;LL0*;3EBB))1suariosJcursor^E*H0 tarea ejecutada*LSE243
http://msdn.microsoft.com/es-es/library/ms162806.aspxhttp://msdn.microsoft.com/es-es/library/ms162806.aspx

copias de seguridad automáticos de sql server express

Documents