none
Realizar backup de base de datos de forma rápida en sql server 2008r2 RRS feed

  • Pregunta

  • Buenas tardes expertos.

    Tengo un problema con el respaldo diario de mis bases de datos.

    Actualmente administro 73 base de datos, todas bajo sql server 2008r2. El problema radica que al momento de ejecutar el jod que me realiza el respaldo ese proceso dura 7 horas y contando. En ese jod se hace el respaldo en disco y comprime con winrar.

    Me gustaría que me orientaran en este caso,  aqui les dejo el codigo que realiza ese trabajo.

    USE master
    GO
    DECLARE @Count int, @i int
    DECLARE @NombreRAR varchar(8000)
    DECLARE @NombreCompleto varchar(8000)
    DECLARE @NombreBaseDato varchar(8000)
    DECLARE @Ruta varchar(8000)
    DECLARE @Orden varchar(8000)
    
    IF EXISTS (SELECT * FROM sys.tables WHERE object_id = OBJECT_ID(N'#temp_database') AND type in (N'U'))
    	DROP TABLE #temp_database
    
    CREATE TABLE  #temp_database (database_id INT, name NVARCHAR(20))
    INSERT #temp_database
    SELECT database_id, name FROM sys.databases
    
    SELECT @Count = COUNT(*) FROM #temp_database
    PRINT @Count;
    SET @i = 1;
    BEGIN TRY
    	WHILE (@i <= (SELECT MAX(database_id) FROM #temp_database))
    		BEGIN
    			IF(@i >= 5)
    				BEGIN
    					SELECT @NombreBaseDato = name FROM #temp_database WHERE database_id = @i
    					SET @NombreRAR='G:\Backups\'+ @NombreBaseDato + '_' + (RTRIM(CONVERT(DATE,GETDATE(),112))) + '.rar'
    					SET @NombreCompleto='G:\Backups\'+ @NombreBaseDato + '_' + RTRIM(CONVERT(DATE,GETDATE(),112)) + '.bak'
    					SET @Ruta='G:\Backups\'
    					
    					BACKUP DATABASE @NombreBaseDato TO DISK=@NombreCompleto
    					WITH INIT, NOUNLOAD, NAME = @NombreBaseDato, SKIP, STATS = 10, FORMAT
    
    					SET @Orden ='C:\Program Files\WinRAR\WinRAR.exe a -m5 -df ' + @NombreRAR + ' ' + @NombreCompleto
    					EXEC master.dbo.xp_cmdshell @Orden
    
    					SET @Ruta = 'E:\BACKUPS\'
    				END
    			SET @i =@i + 1; 
    		END
    	DROP TABLE #temp_database
    END TRY
    BEGIN CATCH
    		SELECT
    			 ERROR_NUMBER() AS ErrorNumber
    			,ERROR_SEVERITY() AS ErrorSeverity
    			,ERROR_STATE() AS ErrorState
    			,ERROR_PROCEDURE() AS ErrorProcedure
    			,ERROR_LINE() AS ErrorLine
    			,ERROR_MESSAGE() AS ErrorMessage;
    END CATCH

    Muchas gracias.


    Gerson Requena

    viernes, 7 de octubre de 2016 20:13

Respuestas

  • Tu version de SQL es enterprise?

    Jose Miguel Salas C

    • Marcado como respuesta Gerson Requena martes, 18 de octubre de 2016 17:37
    viernes, 7 de octubre de 2016 23:03
  • Saludos

    Un punto importante es que comprimir en WinRAR no te dara muchas opciones y en ocasiones es un proceso tardado que no ayudara en mucho a la compression en especial si ya comprimiste el backup, una de las mejoras que puedes hacer es comprimir el backup nativo esto genera un consumo de cpu pero como el backup es mas pequeño existe menos informacion siendo transmitida al subsistema de IO lo cual hace que demoren menos.

    También ten muy en cuenta las recomendaciones de Alberto.

    • Marcado como respuesta Gerson Requena martes, 18 de octubre de 2016 17:37
    sábado, 8 de octubre de 2016 4:44
  • ¿Has hecho pruebas de rendimiento a ver dónde se te pierde el tiempo? Es decir, medición de la velocidad a la que es capaz de extraer los datos desde los .mdf, medición de la velocidad a la que se graban en la unidad G:,  medición de la velocidad a la que se transmiten a través de la red (si G: está en otro equipo), medición del tiempo que tardan en comprimirse a rar.

    Una vez que sepas cuál es el punto más débil (el que menos velocidad te da), ese es el "cuello de botella" que tienes que optimizar para que tus copias tarden más. Por ejemplo, si el problema esté en el disco de destino, puedes añadir más de una unidad en paralelo, bien sea mediante RAID o (si tu edición de SQL Server lo permite) haciendo el backup a un Media Set. O podrías hacer la copia a un disco SSD (y luego ya con calma trasvasarla desde ahí a un medio más barato, pero aunque eso sea lento ya te da igual porque no "molesta" al servidor de base de datos, del que ya se han sacado esos datos).

    Similarmente, si el problema está en origen, puedes añadir más ficheros de base de datos y procesarlos en paralelo, o hacer particionamiento y guardar en una unidad los datos históricos y en otra los "vivos", lo que te permitiría hacer copia únicamente del filegroup vivo y las copias serían mucho más pequeñas. En fin, que todo depende de dónde esté el cuello de botella, esa sería la parte que habría que optimizar una vez que sepas cuál es.

    viernes, 7 de octubre de 2016 20:34

Todas las respuestas

  • ¿Has hecho pruebas de rendimiento a ver dónde se te pierde el tiempo? Es decir, medición de la velocidad a la que es capaz de extraer los datos desde los .mdf, medición de la velocidad a la que se graban en la unidad G:,  medición de la velocidad a la que se transmiten a través de la red (si G: está en otro equipo), medición del tiempo que tardan en comprimirse a rar.

    Una vez que sepas cuál es el punto más débil (el que menos velocidad te da), ese es el "cuello de botella" que tienes que optimizar para que tus copias tarden más. Por ejemplo, si el problema esté en el disco de destino, puedes añadir más de una unidad en paralelo, bien sea mediante RAID o (si tu edición de SQL Server lo permite) haciendo el backup a un Media Set. O podrías hacer la copia a un disco SSD (y luego ya con calma trasvasarla desde ahí a un medio más barato, pero aunque eso sea lento ya te da igual porque no "molesta" al servidor de base de datos, del que ya se han sacado esos datos).

    Similarmente, si el problema está en origen, puedes añadir más ficheros de base de datos y procesarlos en paralelo, o hacer particionamiento y guardar en una unidad los datos históricos y en otra los "vivos", lo que te permitiría hacer copia únicamente del filegroup vivo y las copias serían mucho más pequeñas. En fin, que todo depende de dónde esté el cuello de botella, esa sería la parte que habría que optimizar una vez que sepas cuál es.

    viernes, 7 de octubre de 2016 20:34
  • Tu version de SQL es enterprise?

    Jose Miguel Salas C

    • Marcado como respuesta Gerson Requena martes, 18 de octubre de 2016 17:37
    viernes, 7 de octubre de 2016 23:03
  • Saludos

    Un punto importante es que comprimir en WinRAR no te dara muchas opciones y en ocasiones es un proceso tardado que no ayudara en mucho a la compression en especial si ya comprimiste el backup, una de las mejoras que puedes hacer es comprimir el backup nativo esto genera un consumo de cpu pero como el backup es mas pequeño existe menos informacion siendo transmitida al subsistema de IO lo cual hace que demoren menos.

    También ten muy en cuenta las recomendaciones de Alberto.

    • Marcado como respuesta Gerson Requena martes, 18 de octubre de 2016 17:37
    sábado, 8 de octubre de 2016 4:44
  • Tu version de SQL es enterprise?
    Seguramente Jose Miguel te pregunta esto porque la edición Enterprise permite comprimir el backup directamente (cláusula ...WITH COMPRESSION en la sentencia BACKUP). No lo comprime tanto como el RAR, pero a cambio tiene la ventaja de que como tiene que escribir menos bytes en el dispositivo de salida, se acelera la copia en el caso de que el cuello de botella esté en la grabación sobre dicho dispositivo.
    sábado, 8 de octubre de 2016 9:32