Planes típicos de mantenimiento de SQL Server: copia de seguridad automatizada de la base de datos
Hay muchos mensajes que exigen una simple verdad: debe realizar copias de seguridad con regularidad. Las personas siempre se dividirán en dos categorías: las que hacen copias de seguridad y las que no. La primera categoría, que ignora este consejo, a menudo se puede encontrar en foros relevantes con aproximadamente las mismas preguntas:
- Mis discos han fallado / alguien borró mi base de datos… ¿Cómo puedo recuperar mis datos?
- ¿Tienes la última copia de seguridad?
- No.
Para evitar esta desagradable situación, es necesario hacer un mínimo esfuerzo. Lo primero que debe hacer es elegir una matriz de discos para realizar la copia de seguridad. Debido a que respaldar junto con los archivos de la base de datos no es nuestra elección, el segundo paso será crear un plan de mantenimiento de respaldo de la base de datos.
A continuación, analizaremos algunas de las complejidades asociadas con la copia de seguridad.
Vamos a crear una tabla para registrar los mensajes de error de copia de seguridad:
USE [master]
GO
IF OBJECT_ID('dbo.BackupError', 'U') IS NOT NULL
DROP TABLE dbo.BackupError
GO
CREATE TABLE dbo.BackupError (
db SYSNAME PRIMARY KEY,
dt DATETIME NOT NULL DEFAULT GETDATE(),
msg NVARCHAR(2048)
)
GO
Puede usar el siguiente script para hacer una copia de seguridad de su base de datos diariamente:
USE [master]
GO
SET NOCOUNT ON
TRUNCATE TABLE dbo.BackupError
DECLARE
@db SYSNAME
, @sql NVARCHAR(MAX)
, @can_compress BIT
, @path NVARCHAR(4000)
, @name SYSNAME
, @include_time BIT
--SET @path="\pubbackup"
IF @path IS NULL
EXEC [master].dbo.xp_instance_regread
N'HKEY_LOCAL_MACHINE',
N'SoftwareMicrosoftMSSQLServerMSSQLServer',
N'BackupDirectory', @path OUTPUT, 'no_output'
SET @can_compress = ISNULL(CAST((
SELECT value
FROM sys.configurations
WHERE name="backup compression default") AS BIT), 0)
DECLARE cur CURSOR FAST_FORWARD READ_ONLY LOCAL FOR
SELECT d.name
FROM sys.databases d
WHERE d.[state] = 0
AND d.name NOT IN ('tempdb')
OPEN cur
FETCH NEXT FROM cur INTO @db
WHILE @@FETCH_STATUS = 0 BEGIN
IF DB_ID(@db) IS NULL BEGIN
INSERT INTO dbo.BackupError (db, msg) VALUES (@db, 'db is missing')
END
ELSE IF DATABASEPROPERTYEX(@db, 'Status') != 'ONLINE' BEGIN
INSERT INTO dbo.BackupError (db, msg) VALUES (@db, 'db state != ONLINE')
END
ELSE BEGIN
BEGIN TRY
SET @name = @path + 'T' + CONVERT(CHAR(8), GETDATE(), 112) + '_' + @db + '.bak'
SET @sql="
BACKUP Base de datos " + QUOTENAME(@db) + '
TO DISK = ''' + @name + ''' WITH NOFORMAT, INIT' +
CASE WHEN @can_compress = 1 THEN ', COMPRESSION' ELSE '' END
--PRINT @sql
EXEC sys.sp_executesql @sql
END TRY
BEGIN CATCH
INSERT INTO dbo.BackupError (db, msg) VALUES (@db, ERROR_MESSAGE())
END CATCH
END
FETCH NEXT FROM cur INTO @db
END
CLOSE cur
DEALLOCATE cur
Cuándo base de datos de correo está configurado en el servidor, puede agregar una notificación por correo electrónico a la secuencia de comandos sobre los problemas que pueden ocurrir:
IF EXISTS(SELECT 1 FROM dbo.BackupError) BEGIN
DECLARE @report NVARCHAR(MAX)
SELECT @report = STUFF((
SELECT '
' + db + ' (' + CAST(dt AS VARCHAR(20)) + '):' + msg
FROM dbo.BackupError
FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 1, '')
EXEC msdb.dbo.sp_send_dbmail
@recipients="[email protected]",
@subject="Backup Problems",
@body = @report,
@body_format="HTML"
END
En este punto, el script de copia de seguridad en funcionamiento está listo para usarse. Solo queda crear una tarea que ejecute este script según lo programado.
Vale la pena señalar a los propietarios de las publicaciones Express, como Servidor SQL Express la edición no ofrece la posibilidad de uso Agente de servidor SQL. Por triste que parezca, en realidad se puede resolver fácilmente. La forma más fácil es crear un archivo bat con contenido similar:
sqlcmd -S -i D:backup.sql
A continuación, abra el archivo Programador de tareas y crear una nueva tarea en él.
La segunda opción es utilizar un software de terceros que le permita realizar tareas programadas. Entre ellos están Programador de SQL - una práctica herramienta gratuita.
Ahora hablemos de las cosas útiles relacionadas con la copia de seguridad.
Compresión de copia de seguridad
Función de compresión de copia de seguridad ahora: Servidor SQL 2008 (Edición Enterprise/Desarrollador solamente), 2008R2 / 2012/2014 (Solo en las ediciones Enterprise, Business Intelligence, Standard y Developer). Hubo un tiempo en que trabajaba con Servidor SQL 2005Tuve que comprimir copias de seguridad con 7Zip. Ahora todo es mucho más fácil.
Debemos tener en cuenta que la compresión de respaldo solo se utilizará al ejecutar un archivo TAPÓN DE RESERVA equipo con COMPRESIÓN o habilite la compresión predeterminada con el siguiente comando:
USE [master] GO EXEC sp_configure 'backup compression default', 1 RECONFIGURE WITH OVERRIDE GO
Por cierto, las copias de seguridad comprimidas tienen una serie de ventajas:
1) necesitas menos espacio para almacenarlos;
2) La recuperación de bases de datos a partir de copias de seguridad comprimidas suele llevar menos tiempo;
3) Las copias de seguridad comprimidas son más rápidas y requieren menos operaciones de E/S.
Hay varias desventajas: cuando se trabaja con copias de seguridad comprimidas, aumenta la carga en el procesador.
La siguiente consulta devuelve el tamaño de este último. COMPLETO copia de seguridad tanto comprimida como sin comprimir:
SELECT
database_name
, backup_size_mb = backup_size / 1048576.0
, compressed_backup_size_mb = compressed_backup_size / 1048576.0
, compress_ratio_percent = 100 - compressed_backup_size * 100. / backup_size
FROM (
SELECT
database_name
, backup_size
, compressed_backup_size = NULLIF(compressed_backup_size, backup_size)
, RowNumber = ROW_NUMBER() OVER (PARTITION BY database_name ORDER BY backup_finish_date DESC)
FROM msdb.dbo.backupset
WHERE [type] = 'D'
) t
WHERE t.RowNumber = 1
Normalmente la compresión llega al 40-90%, dejando de lado los datos binarios:
database_name backup_size_mb compressed_backup_size_mb compress_ratio_percent ------------------------------- ---------------- -------------------------- ------------------------ AdventureWorks2012 190.077148437 44.652716636 76.50810894222767 DB_Dev 1530.483398437 295.859273910 80.66890015190163 BinDocuments 334.264648437 309.219978332 7.49246748707956 locateme 37.268554687 17.247792243 53.72025454546944 master 3.643554687 0.654214859 82.04459888434736 model 2.707031250 0.450525283 83.35721895292208 msdb 17.147460937 2.956551551 82.75807967958028 OnlineFormat 125.078125000 23.639108657 81.10052524545207 Refactoring 286.076171875 35.803841590 87.48450758543927 ReportServer$SQL_2012 4.045898437 0.696615219 82.78218719828627 ReportServer$SQL_2012TempDB 2.516601562 0.428588867 82.96953822273962
Si cambia la consulta anterior, puede realizar un seguimiento de las bases de datos que se respaldaron:
SELECT
d.name
, rec_model = d.recovery_model_desc
, f.full_time
, f.full_last_date
, f.full_size
, f.log_time
, f.log_last_date
, f.log_size
FROM sys.databases d
LEFT JOIN (
SELECT
database_name
, full_time = MAX(CASE WHEN [type] = 'D'
THEN CONVERT(CHAR(10), backup_finish_date - backup_start_date, 108) END)
, full_last_date = MAX(CASE WHEN [type] = 'D' THEN backup_finish_date END)
, full_size = MAX(CASE WHEN [type] = 'D' THEN backup_size END)
, log_time = MAX(CASE WHEN [type] = 'L'
THEN CONVERT(CHAR(10), backup_finish_date - backup_start_date, 108) END)
, log_last_date = MAX(CASE WHEN [type] = 'L' THEN backup_finish_date END)
, log_size = MAX(CASE WHEN [type] = 'L' THEN backup_size END)
FROM (
SELECT
s.database_name
, s.[type]
, s.backup_start_date
, s.backup_finish_date
, backup_size =
CASE WHEN s.backup_size = s.compressed_backup_size
THEN s.backup_size
ELSE s.compressed_backup_size
END / 1048576.0
, RowNum = ROW_NUMBER() OVER (
PARTITION BY s.database_name, s.[type]
ORDER BY s.backup_finish_date DESC
)
FROM msdb.dbo.backupset s
WHERE s.[type] IN ('D', 'L')
) f
WHERE f.RowNum = 1
GROUP BY f.database_name
) f ON f.database_name = d.name
Si utiliza Servidor SQL 2005que esta línea:
backup_size = CASE WHEN s.backup_size = s.compressed_backup_size THEN ...
debe cambiarse a:
backup_size = s.backup_size / 1048576.0
Los resultados de esta consulta pueden ayudar a prevenir muchos problemas:
name rec_model full_time full_last_date full_size log_time log_last_date log_size ---------------------------- --------- --------- ------------------- ----------------- --------- ------------------- ------------ master SIMPLE 00:00:01 2015-11-06 15:08:12 0.654214859 NULL NULL NULL tempdb SIMPLE NULL NULL NULL NULL NULL NULL model FULL 00:00:00 2015-11-06 15:08:12 0.450525283 NULL NULL NULL msdb SIMPLE 00:00:00 2015-11-06 15:08:12 2.956551551 NULL NULL NULL ReportServer$SQL_2012 FULL 00:00:01 2015-11-06 15:08:13 0.696615219 NULL NULL NULL ReportServer$SQL_2012TempDB SIMPLE 00:00:00 2015-11-06 15:08:13 0.428588867 NULL NULL NULL DB_Dev FULL 00:00:13 2015-11-06 15:08:26 295.859273910 00:00:04 2015-11-01 13:15:39 72.522538642 BinDocuments FULL 00:00:05 2015-11-06 15:08:31 309.219978332 00:00:01 2015-11-06 13:15:39 2.012338638 Refactoring SIMPLE 00:00:02 2015-11-06 15:08:33 35.803841590 NULL NULL NULL locateme SIMPLE 00:00:01 2015-11-06 15:08:34 17.247792243 NULL NULL NULL AdventureWorks2012 FULL 00:00:02 2015-11-06 15:08:36 44.652716636 NULL NULL NULL OnlineFormat SIMPLE 00:00:01 2015-11-06 15:08:39 23.639108657 NULL NULL NULL
Puede ver inmediatamente si todas las bases de datos tienen copias de seguridad COMPLETAS a la fecha actual.
A continuación, puede consultar la hora de la copia de seguridad. Usted puede preguntar por qué? Suponga que la copia de seguridad de la base de datos DB_Dev tomó 5 segundos y luego duró 1 hora. Las razones de esto pueden ser diferentes: los discos no pueden soportar la carga, los datos en la base de datos han crecido enormemente, el disco falló en RAID y disminuyó la velocidad de escritura.
Si la base de datos contiene un archivo COMPLETO o BULK_LOGGED modelos de recuperación, es recomendable hacer una copia de seguridad del registro de vez en cuando para evitar que el servidor crezca constantemente LDF Archivo. Puede ver qué tan completos están el archivo de datos y el registro para la base de datos con esta consulta:
IF OBJECT_ID('tempdb.dbo.#space') IS NOT NULL
DROP TABLE #space
CREATE TABLE #space (
database_id INT PRIMARY KEY,
data_used_size DECIMAL(18,6),
log_used_size DECIMAL(18,6)
)
DECLARE @SQL NVARCHAR(MAX)
SELECT @SQL = STUFF((
SELECT '
USE [' + d.name + ']
INSERT INTO #space (database_id, data_used_size, log_used_size)
SELECT
DB_ID()
, SUM(CASE WHEN [type] = 0 THEN space_used END)
, SUM(CASE WHEN [type] = 1 THEN space_used END)
FROM (
SELECT s.[type], space_used = SUM(FILEPROPERTY(s.name, ''SpaceUsed'') * 8. / 1024)
FROM sys.database_files s
GROUP BY s.[type]
) t;'
FROM sys.databases d
WHERE d.[state] = 0
FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 2, '')
EXEC sys.sp_executesql @SQL
SELECT
database_name = DB_NAME(t.database_id)
, t.data_size
, s.data_used_size
, t.log_size
, s.log_used_size
, t.total_size
FROM (
SELECT
database_id
, log_size = SUM(CASE WHEN [type] = 1 THEN size END) * 8. / 1024
, data_size = SUM(CASE WHEN [type] = 0 THEN size END) * 8. / 1024
, total_size = SUM(size) * 8. / 1024
FROM sys.master_files
GROUP BY database_id
) t
LEFT JOIN #space s ON t.database_id = s.database_id
Resultados de la consulta en mi local Una instancia de SQL Server:
database_name data_size data_used_size log_size log_used_size total_size ------------------------------ ------------- --------------- ------------- -------------- -------------- master 4.875000 3.562500 1.750000 0.781250 6.625000 tempdb 8.000000 4.500000 0.500000 0.632812 8.500000 model 4.062500 2.562500 1.250000 0.609375 5.312500 msdb 16.687500 16.062500 26.187500 2.804687 42.875000 ReportServer$SQL_2012 4.062500 3.937500 10.125000 1.570312 14.187500 ReportServer$SQL_2012TempDB 4.062500 2.437500 1.312500 0.500000 5.375000 DB_Dev 1782.812500 1529.562500 7286.125000 42.570312 9068.937500 BinDocuments 334.000000 333.500000 459.125000 12.031250 793.125000 Refactoring 333.125000 285.625000 127.882812 0.851562 461.007812 locateme 591.000000 36.500000 459.125000 8.585937 1050.125000 AdventureWorks2012 205.000000 189.125000 0.750000 0.453125 205.750000 OnlineFormat 125.375000 124.437500 1.015625 0.414062 126.390625
También hay un par de trucos divertidos que pueden hacerte la vida más fácil. Especifique varias rutas durante el comando BACKUP y el archivo de copia de seguridad resultante se cortará en partes de aproximadamente el mismo tamaño:
BACKUP Base de datos AdventureWorks2012
TO
DISK = 'D:AdventureWorks2012_1.bak',
DISK = 'D:AdventureWorks2012_2.bak',
DISK = 'D:AdventureWorks2012_3.bak'
GO
Esto puede ser útil si está realizando una copia de seguridad en una unidad flash USB con un sistema de archivos FAT32que tiene un límite en el tamaño máximo de archivo.
Otra característica interesante es la copia de seguridad (solo Enterprise / Developer). En lugar de hacer una copia de seguridad en la carpeta predeterminada y copiar manualmente el script en una carpeta compartida, simplemente puede usar el siguiente comando:
BACKUP Base de datos AdventureWorks2012
TO DISK = 'D:AdventureWorks2012.bak'
MIRROR TO DISK = 'E:AdventureWorks2012_copy.bak'
WITH FORMAT
GO
Artículos de interés