Muchas veces las cosas que parecen complicadas son más fáciles de lo que se cree y esta es una de las ventajas de usar T-SQL para que se encargue de hacer las tareas repetitivas. Una de estas tareas, por ejemplo, puede ser el hacer copias de seguridad de todas las bases de datos en el servidor. A lo mejor si tienes pocas bases de datos es fácil controlarlo, pero evidentemente existen escenarios donde hay más cientos de bases de datos en la misma instancia de SQL Server. Para esto se podría usar SQL Server Management Studio o usar los planes de mantenimiento, pero usar T-SQL es una práctica (en mi caso al menos) mucho más simple y rápida.
Al usar T-SQL se pueden generar loa comandos de Backup en un cursor para luego sacar una copia de todas las bases de datos una por una mediante un bucle. Este es un proceso muy sencillo y sólo necesita unos cuantos comandos para hacer esto.
A continuación muestro el script que permite hacer una copia de seguridad de cada Base de Datos en la instancia de SQL Server.
Importante: Se tendrá que cambiar el valor del parámetro @path con la ruta deseada.
-- El nombre del fichero tendrá este Formato DB_YYYYDDMM.BAK DECLARE @name VARCHAR(50) -- Nombre de la Base de Datos DECLARE @path VARCHAR(256) -- Ruta para las copias de seguridad DECLARE @fileName VARCHAR(256) -- Nombre del Fichero DECLARE @fileDate VARCHAR(20) -- Usado para el nombre del fichero -- Ruta para las copias de seguridad SET @path = 'd:\BackupSQL\' -- Formato del nombre del fichero SELECT @fileDate = CONVERT(VARCHAR(20),GETDATE(),112) -- excluir estas bases datos DECLARE db_cursor CURSOR FOR SELECT name FROM master.dbo.sysdatabases WHERE name NOT IN ('master','model','msdb','tempdb') OPEN db_cursor FETCH NEXT FROM db_cursor INTO @name WHILE @@FETCH_STATUS = 0 BEGIN SET @fileName = @path + @name + '_' + @fileDate + '.BAK' BACKUP DATABASE @name TO DISK = @fileName FETCH NEXT FROM db_cursor INTO @name END CLOSE db_cursor DEALLOCATE db_cursor
Notas adicionales:
En este script se omiten las bases de datos del sistema, pero podrían también incluirse. También es posible convertir esto en un procedimiento almacenado y pasarle como parámetro el nombre de la base de datos de la cual se quiere una copia o pasarle NULL si queremos la copia de seguridad de todas las bases de datos.
Sea la forma en la que lo uses, este script te da un punto de partida para obtener una copia de todas tus bases de datos. Además es posible pasar por alto cualquier base de datos que no se quiera incluir en la copia, agregando su nombre en la sección NOT IN.
Si quieres más trucos de este tipo te invito a echarle un vistazo a este libro de SQL Server 2014 en Español
Comprar en Amazon |
Hola:
Primero que nada gracias por la aportación, segundo podría poner este script para que corra en el administrador de tareas de windows??, ya que el sql server management studio no lo tengo.
Desde ya Gracias!!!!
crear un archivo .bat con lo siguiente:
@set serverffmm=TUservidorSQLServer
@set bddffmm=master
osql -E -S %serverffmm% -d %bddffmm% -e -n -m-1 -i scriptdearriba.sql -o salida_Full.log
Gracias por el aporte Mario.
saludes a todos mi consulta es la siguiente como puedo adjuntar datos de una base de datos que están en dos PC que no tengo conectada en red (la Base de Datos es la misma es decir que tengo que importar los datos pero sin que estos me borren, datos de la pc que es mi servidor y me mantengan el historial del registro actualizado ya que esta pasaran luego a las dos pc que no están en red )
las tablas son las siguiente : la BD ES : Reg_alumnos y sus tablas son: Alumno y Materia
Hola, si tus equipos no están en red, definitivamente no puedes implementar ninguna estrategia de replicación. La única forma de tener la misma base de datos en cada equipo es que hagas una copia manual de la base de datos de un ordenador y lo copies al otro, con un pendrive o disco externo.
Si no vas a restaurar la base de datos completa y solo quieres traspasar una o varias tablas o solo algunos registros, lo puedes hacer con el asistente de scripts (Click derecho a la base de datos | Tareas | Generar Scripts), esto te exportará los datos en scripts que luego puedes ejecutar en el otro ordenador.
Mi recomendación de todas formas es que veas la forma de conectar los equipos a medida de lo posible, porque estar haciendo eso manualmente, no solo es una perdida de tiempo sino que además siempre tendrás algún desfase de la información y tendrás que estar haciendo revisiones manualmente. Conectar dos equipos en red hoy en día no es costoso.
Un saludo,
Funciono!!!! mil gracias, era lo que buscaba
Muchassss gracias por la aportacion lo he agregado y funciona a las mil maravillas.
saludos
Hola Jesús, me alegro que te haya servido.
Un saludo,
una pregunta como seria la sintaxis para que losa respaldos sean con compression?
Saludos
Esa sería una tarea de un agente aparte. SQL por defecto no comprime. Lo que podrías hacer es crear una tarea con el Administrador de tareas de Windows que periódicamente verifique que si hay ficheros *.BAK en la carpeta los comprima usando winzip/winrar o cualquier otro utilitario de comprensión que te permita ejecutarse mediante línea de comando. Podrías crear un script de tipo BAT o CMD para que se encargue de esa tarea.
hola buenos dias me podrian ayudar porfavor si hubiera un codigo en sql al guardar un backups un codigo para zipiar
Hola, SQL por defecto no comprime la información. Lo que puedes hacer es un script que automatize la comprensión de ficheros y carpetas. Hay muchas herramientas de terceros que hacen eso, por ejemplo winrar (http://www.rarlab.com/) en modo consola.
Amigo, hola. una consulta….al ejecutar tu script me da error código 5…he hecho de todo, agregue al usuario y los permisos necesarios para la lectura y escritura pero nada…tienes alguna idea de que podría ser ??? saludos amigo y muy buen script.
Antes de nada, te sugiero probar primero con el usuario SA directamente, recuerda que para sacar copias de seguridad se debe tener los privilegios adecuados.
Hola una consulta. Para que en cada corrida del SQL, se genere automaticamente, la carpeta de destino del backup, eso es posible?
Por ejemplo en
— Ruta para las copias de seguridad
SET @path = ‘d:BackupSQL\20190314’
y en la proxima ejecución
SET @path = ‘d:BackupSQL\20190325’
y así sucesivamente.
Es posible?
Gracias!
Muchas gracias!!
Buenas sobre lo que pregunta Sergio para ello habría que adecuar el script para que cree una carpeta basada en la fecha del sistema, algo así como:
SET @path = ‘d:BackupSQL\’ + SELECT CONVERT(varchar,GETDATE(),12)
SET @path = ‘d:BackupSQL\’ + CONVERT(varchar,GETDATE(),12)
asi seria la sintaxis perfecta.
gracias por el aporte
Excelente, funciono a la primera. Gracias.
Consulta adicional, cual es la sintaxis para path cuando los nombres de las carpetas tienen espacios.
Ejemplo:
C:\Users\Administrator\XXXX XXXX XXX XX\AAAA – BBB\respaldo.bak
Excelente Aporte, Gracias!!!
Cordial saludo, te pregunto puedo generar un script que solo me traiga copia de funciones, vistas y procedimientos almacenados de determinda base de datos, gracias por tu respuesta.
Buenas noches, si quiero guardar esos backup a un sftp como lo haría
el backup que se genere mediante el código, como se puede guardar directamente a un servidor sftp