ITECH Blog

Entrañas Sistemáticas

Respaldos de Base de Datos en SQL Server

with 27 comments

transact_sql_restore

Es obligatorio para todo administrador de Base de Datos, sea SQL Server, Oracle, Access, MySQL, PostgreSQL, etc, realizar respaldo de la base de datos, más aun, crear una tarea programada que automáticamente realice esta tarea en un dia y hora determinada.

Un respaldo de la BD manual es sencillo, para SQL Server con solo seleccionar la BD boton derecho… sea en espanol o en ingles…

Pero realmente lo correcto para realizar un respaldo de una BD es programando una tarea, que realice el respaldo automticamente pautado en un dia a una hora. Lo correcto es programar un Job al cual se le indique de Lunes a Viernes realizar un respaldo a las 12am por ejemplo. Si en la empresa no posee un horaro corrido, puede hacerse dos respaldos al dia, uno a las 12m y el otro a las 12am, de tal forma que si ocurre un problema con la BD pueda restaurarse con el respaldo más reciente.

SQL Server 2000 y SQL Server 2005

SQL Server 2000 y SQL Server 2005

Un Job es una tarea programa donde le indicamos a SQL Server realizar una actividad bien sea principalmente respaldo de la base de datos (una o varias BDs), copiado de un archivo en una ubicación de la red a una tabla, etc. Esto se hace a traves del SQL Agent, en SQL Server se encuentra en el arbol de SQL Server donde se encuentran las BDs y demás funciones, la carpeta Management > SQL Agent. Para el caso de SQL Server 2005, se encuentra fuera, al final del arbol.

SQL Server 2000 y SQL Server 2005

SQL Server 2000 y SQL Server 2005

Seleccionamos Job boton derecho New Job… tanto para SQL Server 2000 como 2005 los pasos son iguales, por lo que se indican de la siguiente forma:

  • Sección General > Indicas el nombre de la tarea programada (la planificación automática), lo demas puedes dejarlo como está.
  • Steps > boton New > nombre del Step, indicas la BD y en Command debes indicar el script que realizara el Backup de la BD. Realmente no hay que indicar la BD en la parte de Database, ya que el script es en el que se indica cual BD y donde se realizara el respaldo de la misma.

El Script para generar el Backup puede ser uno como este:

BACKUP DATABASE [nombre_base_de_datos] TO DISK = N‘C:\Carpeta\Backup.bak’
WITH FORMAT, INIT, STATS = 10

Indicas la base de datos, la ubicación del disco, en red, un script sencillo que realizara el repaldo programado sin estar vigilante de hacer esto cada cierto tiempo, simplemente con chequear de vez en cuando que el respaldo se esta realizando es suficiente.

  • Schedules > Aqui es donde indicaremos la programación del Job > New Schedule, en la ventana que se abre indicamos el nombre, y en el boton Change > indicamos los dias (que en este caso) seria de Lunes a Viernes, tildando todos estos dias, a excepción de Sabado y Domingo, indicamos la hora a la que deseamos que se active esta tarea, 12:00:00 a.m.

Se indica como practica la hora 12am puesto que es muy seguro que a esa hora no deberia haber nadie trabajando, a diferencia de horas mas tempranas donde personal de algun departamento se quede trabajando hasta algunas horas de la noche, ya que al momento de iniciarse el backup, el sistema que dependa de esta base de datos, se tornara lento y posiblemente tambien podria presentarse algun problema que cuelgue el sistema por lo que para evitar problemas al personal que se quede algo tarde y evitarnos problemas nosotros, indicamos una hora en la que el servidor no deberia estar recibiendo peticiones de ninguna terminal (y que no sea un sistema administrativo que permita crear tareas programadas) o que alguien deje generando una consulta de informe excesivamente grande como para que se lleve horas de procesamiento a nivel de BD.

La parte de notificaciones, no se indicara aun en estos pasos, pero queda sujeto a anexarse, ya que al momento de que se realice el respaldo de la BD y en particular, en caso de haber un problema que ocasiona que esto no se realice, el que nos mande un correo informandonos sobre ello.

Un respaldo de la BD, se realizaria iniciandolo muy parecido a lo indicado de crear el Backup.

En SQL Server 2000 seria boton derecho en la BD a restaurar > Todas las tareas > Restore Database…

En SQL Server 2005 seleccionamos la BD, boton derecho > Tasks > Restore > Database

En la ventana que se despliega, selecciona la opción From device para buscar la ruta donde se encuentra el respaldo de la BD el cual es un archivo que no posea extensión o sea .bak. También en la ventana indicar Database – complete al menos en el caso en que el respaldo se haya hecho asi, la base de datos completa, con todas sus transacciones, log, etc, pulsamos el boton Aceptar y deberia iniciarse al Restore, el cual dependera su demora (igual como lo fue con el Backup) del tamano de la BD.

Para el caso de SQL Server 2005 (el cual a mi corta experiencia de manejo) se observa que no es tanto del estilo Asistente (wizard) sino mas de presentarse el scribir los script tanto para el backup para el Restore. Con un Script como el siguiente podriamos realizar un Restore Database de nuestra BD.

RESTORE DATABASE [nombre_base_datos]
FROM DISK = ‘D:\Carpeta\Backup.bak’
WITH REPLACE

El nombre de la base de datos no tiene que estar realmente entre [ ].

Para un continio conocimiento y manejo de SQL Server, se recomienda descargar de la web de Microsoft el Libro en Pantalla (Books Online) de SQL Server, una ayuda que descargas a tu equipo y el cual tanto local como online proporcionara consultas sobre el manejo de SQL Server.

Libros en Pantalla de SQL Server 2005

Libros en Pantalla de SQL Server 2008

Finalizando el tema, por ejemplo, indiquemos que tenemos una BD la cual hemos hecho Backup de un Servidor y en esta ocasión vamos a hacer un Restore de la misma. Indiquemos que sera en otro equipo, otro servidor o en un equipo de trabajo para trabajar con ella y no con la BD Online del servidor.

Creamos primero el esquema de la BD, puesto que si indicamos un Restore directamente, ocasionara error, al menos en mi experiencia desde SQL Server 2000 debia hacer la BD en blanco y luego hacer un Restore de la misma, de esta forma SQL Server agrega lo que necesita para gestionarse con la BD y luego es que aplicamos el Restore en dicha estructura ya montada. Esto es obvio ya que si intentas montar un Restore, la ventana de Restore te pedira restore de cuál BD?, la cual no estara presente en el arbol de bases de datos, por ello, se debe hacer primero una BD nueva con el mismo nombre para luego realizar el restore de la misma.

En este ejemplo, el Restore fue tomado de una ubicación distinta a la que se Restaurara. SQL Server al restaurar busca cargar el Restore en la misma ruta original de la base de datos. Si el Restore lo hacemos en otro equipo o deseamos restaurar en otra ruta aunque sea del mismo equipo, debemos ajustar la ruta del Restore, de lo contrario dara el siguiente error:

error_restore_bd

En la ventana de Restore debemos ir a Opciones, ahi veremos la ruta en la que SQL Server intenta Restaurar la BD, ruta la cual cambiaremos o crearemos dicha ruta que necesita para su restauración.

restore_bd_kf

Cambiamos la ruta de ser necesario de los archivos .MDF y .LDF para no tener problemas con la ruta de restauración al momento de hacerlo del archivo de respaldo .bak.

restore_ubicacion

Vamos a enrriquecer un poco el trabajo, hagamos que SQL Server nos haga un Backup de la BD no una que la reemplace cada dia, mejor hagamos 1 backup por dia en los 5 días laborales de la semana para lo cual le pediremos que el archivo de respaldo .BAK termine su nombre con la fecha del dia, es decir, nombrebasedatos_fecha.bak, si el caso fuese dos backup al dia, podria indicarse tambien la hora, aunque para este caso, y normalmente en vista de que los respaldos son 1 diario, haremos uso de indicar solo la fecha.

Con este script hacemos igual el respaldo de la BD per indicandole en el nombre del archivo .bak la fecha del dia. Recordando la tarea que programamos donde indicamos que se realice el backup de Lunes a Viernes, SQL Server reemplazaria el backup anterior solo si fuese el mismo dia, al ser otro dia, agrega el backup y se mantiene el anterior. De esta forma tenemos 5 respaldos de la base de datos, mejor que uno y sabremos cual es cual ya que tendra la fecha indicada en el nombre. Aunque el sistema operativo puede decirnos la fecha cuando fue creado un archivo, en ocasiones, al momento de mover o editar un archivo, la fecha cambia, aunque se mantiene la fecha de creación, es mejor dejar que el mismo SQL Server nos haga este favor indicandolo al momento de crear el respaldo.

Este script podemos probarlo desde en Analyzer de SQL Server o desde New Query y ejecutarlo, y agregarlo a la tarea programada.

declare @fecha varchar(20)
declare @archivo varchar(250)

set @fecha=convert(varchar(10),getdate(),112)
set @archivo=‘D:\bkp_db\MiBackup_Full_BD_’+@fecha+‘.bak’

backup database dce05_ejemplos_estrella1
to disk=@archivo
with noformat, init, name=N‘BKP FULL de DCE05’, skip, norewind, nounload, stats=10
go

screenshot_T_SQL_bkp_bd_fecha

Con esto, repasando el tema, hemos hecho un Backup de una BD (backup completo, no diferencial), una tarea programada (un job) de respaldo diario de una BD y una Restauración de la misma.

Anuncios

Written by jocdz

julio 22, 2009 a 3:36 pm

27 comentarios

Subscribe to comments with RSS.

  1. Excelente documento, muy util para los que recien comenzamos con esta BD

    Alvaro

    agosto 24, 2009 at 7:21 pm

  2. Muy buen tutorial sencillo, corto y muy util
    Gracias me has ayudado harto.

    benja

    noviembre 30, 2009 at 2:30 pm

  3. Hola…
    El archivo mdf y ldf se generar al mismo tiempo y quedan implicitos en el archivo .bak?

    agradezco sus respuestas!

    Carlos

    enero 11, 2010 at 4:50 pm

    • Sí estimado. Al restaurar la base se dará cuenta que se referencian los dos archivos.

      Admin SQL|

      enero 22, 2010 at 4:03 am

  4. en este documento que version de sql utilizas para generar el sqlagent

    karla

    enero 29, 2010 at 7:50 pm

  5. […] sobre SQL Server, para esas fechas Instalacion de SQL Server 2005 (Developer Edition obviamente) y Respaldos de Bases de Datos en SQL Server (uso del SQL Agent que no viene en la version Express) donde hago una comparación de esto entre la […]

  6. Es una excelente pagina para novatos en BD

    Miguel Tarifa

    julio 29, 2010 at 10:19 pm

  7. una consulta y como hago una tarea programada, que me haga ese backup de mi base de datos

    nadiama

    febrero 9, 2011 at 1:04 pm

  8. Compadre muy agradecido muy buen trabajo para los que comenzamos
    con esta labor

    alexalvarez0310

    junio 15, 2011 at 3:24 pm

  9. Después de tener un respaldo se le pueden aplicar querys??

    Ana

    julio 2, 2011 at 5:06 pm

  10. Buenos días.

    Tienes la función para agregarle la hora.

    Saludos

    jtorres87

    julio 18, 2011 at 4:48 pm

  11. Excelente tutorial, alguien sabe como hacer el mismo proceso pero hacia una unidad de red?

    Andresin

    agosto 4, 2011 at 4:58 pm

    • SQL solo reconoce unidades logicas
      Saludos

      Ernesto

      septiembre 27, 2012 at 2:57 pm

  12. Excelente documento, me ha servido mucho, gracias

    Jesus

    octubre 28, 2011 at 7:22 pm

  13. SUPER, ERA LO QUE BUSCABA…

    MACA

    noviembre 9, 2011 at 7:00 pm

  14. esta mal hay un posible tercer error

    si

    diciembre 21, 2011 at 8:37 pm

  15. Una consulta como puedo generar despues de creado los backup un archivo log donde señale que todo los backups se han generado bien o hubo algun problema al generarlos.

    Gracias de antemano por la pronta respuesta

    Carlos Ramirez

    diciembre 27, 2011 at 4:02 am

  16. tengo una consulta, yo hago un respaldo diario de mi base de datos por medio de una tarea (o job), pero como haria para que por ejemplo: cada 7 dias se eliminen los backup hechos anteriormente.
    Yo solo quiero tener un maximo de 7 backups guardados en mi disco duro. gracias. mi correo: e_vasquez13@hotmail.com

    elias ricardo

    diciembre 27, 2011 at 10:10 pm

  17. para hacerlo en red amigo andresin solo en set @archivo=‘\\192.168.1.70\carpeta\backup’+@fecha+‘.bak’
    y listo solo darle la ruta nada mas.
    muy buen blog estare pendiente 🙂 gracias

    elias ricardo

    diciembre 27, 2011 at 10:13 pm

  18. Excelente gracias por la guia

    Corsario007

    enero 13, 2012 at 5:05 pm

  19. Exelente me sirvio de mucho, una consulta ¿a ese archivo que se genera dia a dia se le puede agregar en el nombre la fecha del dia automaticamente?

    Rony

    marzo 20, 2012 at 1:57 pm

  20. hasta me senti experto.. excelente

    ICM

    junio 26, 2012 at 10:54 pm

  21. Estupendo soy un Administrador de sistemas y necesitaba algo así que me ayudara automatizar mi proceso de Backups, sabía que existía pero no lo avía puesto en práctica excelente tutorial. Felicitaciones desde Guatemala / Guatemala.

    Colter

    julio 8, 2012 at 7:53 pm

  22. Muy buen tutorial… tengo un problema ya con los archivos de respaldo generados, todos son de igual peso en mb, cuando hace poco los generaba super pesados, hasta en Gb… lo que he observado es que el disco duro, en la partición que me genera los respaldos ya no tiene espacio, solo le quedan 50 Mb… supongo que ésta debe ser la razón…

    Heriberto

    septiembre 12, 2012 at 4:26 pm

    • CREA UN EJECUTABLE .BAT
      O SI TIENES SQL SERVER 2005 – 2008 PEGALO EN JOBS Y CREAS UN SCHEDULE EN LA BD ESPECIFICA
      Y PEGAS ESTO:

      DECLARE @fecha VARCHAR(10),@ruta varchar(500);
      SET @fecha=CAST(DAY(GETDATE())AS varchar(2))+CAST(MONTH(GETDATE()) AS varchar(2))+CAST(YEAR(GETDATE()) AS varchar(4));
      set @ruta=’RUTA_DE_LA_BASE DE_DATOS’+@fecha+’.bak’;
      BACKUP DATABASE NOMBRE_DE_LA_BASE_DE_DATOS TO DISK =@ruta;

      Daniel_alvarado

      diciembre 3, 2012 at 3:34 pm

  23. shinghhu3n a zhu madhre, shoy de33l k0o0Nal1ep!!!

    sdfsdf

    junio 7, 2013 at 11:21 pm

  24. Buenas, no se me eliminan los .bak después de una fecha dado en sql server 2005, como podré resolver esto

    yoandy

    julio 18, 2013 at 4:29 pm


Responder

Introduce tus datos o haz clic en un icono para iniciar sesión:

Logo de WordPress.com

Estás comentando usando tu cuenta de WordPress.com. Cerrar sesión / Cambiar )

Imagen de Twitter

Estás comentando usando tu cuenta de Twitter. Cerrar sesión / Cambiar )

Foto de Facebook

Estás comentando usando tu cuenta de Facebook. Cerrar sesión / Cambiar )

Google+ photo

Estás comentando usando tu cuenta de Google+. Cerrar sesión / Cambiar )

Conectando a %s

A %d blogueros les gusta esto: