Estrategia de copias de seguridad, automatización y restauración en SQL Server

Configura las copias de seguridad de una instancia de SQL Server ya instalada (Linux o Windows; instancia única, FCI o réplicas de AG) y recupera a partir de ellas. Copia de seguridad: elección del modelo de recuperación, full semanal + differential diaria + copia de log cada n minutos, COMPRESSION, CHECKSUM, cifrado con certificado, retención y programación (jobs T-SQL de SQL Server Agent u Ola Hallengren; Task Scheduler / cron en Express), copia COPY_ONLY puntual, verificación de copias y comprobación de la cadena. Recuperación: restauración de la cadena full / differential / log, vuelta a un momento concreto con STOPAT (PITR), transacción marcada, restauración con otro nombre o en otro servidor WITH MOVE, tail-log + restauración tras la pérdida de un archivo de datos, page restore, restauración de master / msdb / model, asignación de logins (orphaned user) y carga del certificado para una copia cifrada.

Elija lo que necesita en la tarjeta de escenarios; también puede escribir el código de error (p. ej. 824, 4305, 33111, 18456) en la búsqueda del catálogo para ir directamente al escenario correspondiente. Para exportar una tabla o el resultado de una consulta a un archivo (movimiento de datos, no una copia de seguridad), consulte Exportación con bcp.

Topología: 1 servidor, Failover Cluster Instance o réplicas de Always On AG (restauración en otro servidor: origen + destino)

Escenarios

• Configurar una estrategia de copias de seguridad (copias programadas + retención) — Servidor nuevo o base de datos sin copias periódicas: se configuran los jobs de full / differential / log, la retención y la supervisión.
• Hacer una copia de seguridad puntual (p. ej. COPY_ONLY antes de un cambio) — Una única copia antes de un cambio, patch o upgrade, o para copiar a un entorno de pruebas; COPY_ONLY no rompe la cadena.
• Verificar las copias de seguridad (historial, VERIFYONLY, restauración de prueba) — Para demostrar que las copias se pueden restaurar de verdad: historial de copias, VERIFYONLY, HEADERONLY / FILELISTONLY y restauración de prueba en una base de datos temporal + CHECKDB.
• ¿Está intacta la cadena de copias? (hueco de LSN, archivo que falta) — Antes de una restauración o como auditoría periódica: reconstruye la cadena a partir del historial de msdb y localiza huecos de LSN, archivos que no están en disco y copias intercaladas. No modifica nada.
• Restaurar una base de datos desde copia de seguridad (cadena, STOPAT / STOPATMARK, MOVE / REPLACE, otro servidor) — Se borraron o actualizaron datos por error, se necesita una copia de la base de datos o se va a trasladar a otro servidor; la instancia está en ejecución. Cadena full + differential + log, un momento concreto o una transacción marcada, otro nombre / directorio, hasta el último momento con tail-log.
• La base de datos no se abre: archivo de datos perdido o dañado (tail-log + restauración) — La base de datos está en RECOVERY_PENDING o SUSPECT y no se puede leer el .mdf/.ndf, pero la instancia está en ejecución: primero se hace una copia tail-log con NO_TRUNCATE y después se restaura hasta el último momento. La base de datos no está disponible hasta que termina el procedimiento.
• Algunas páginas dañadas (Msg 823 / 824): page restore — Las consultas o CHECKDB fallan en páginas concretas (msdb.dbo.suspect_pages): solo se restauran desde la copia las páginas dañadas. En Enterprise/Developer la base de datos sigue en línea; en Standard/Express queda fuera de línea un breve tiempo.
• Restaurar la base de datos master (modo de usuario único) — master está dañada o se perdieron logins / linked servers / configuración del servidor: la instancia se inicia en modo de usuario único (-m), se restaura master y SQL Server se detiene solo. Requiere downtime; si la instancia no arranca en absoluto, primero se regeneran las bases de datos del sistema.
• Restaurar la base de datos msdb o model — Los jobs, el historial de copias, Database Mail (msdb) o la plantilla de las bases de datos nuevas (model) están dañados o se han perdido: se restauran con REPLACE desde una copia hecha en la misma build; Agent se detiene brevemente.
• Los usuarios no pueden conectarse tras la restauración (orphaned user, asignación de logins) — La base de datos se trasladó o restauró en otro servidor y la aplicación recibe 'Login failed': los logins se transfieren desde el origen con su SID y el hash de la contraseña, y los usuarios restantes se asignan a un login. La base de datos sigue en línea.
• La copia cifrada no se puede restaurar (falta el certificado, Msg 33111) — La copia (o la copia de una base de datos con TDE) está cifrada con un certificado que no existe en este servidor: se identifica la huella digital (thumbprint), se carga el certificado con su clave privada y se verifica la copia. No se toca ninguna base de datos existente.

Antes de empezar

• El RPO (pérdida de datos aceptable) y el RTO (interrupción aceptable) se han acordado por escrito con el área de negocio. Plan elegido: …como máximo … minutos de pérdida de datos…vuelta a la última copia full/differential (SIMPLE: hasta un día de pérdida de datos)…; el tiempo de restauración se medirá con una restauración de prueba de la base de datos más grande.
• SQL Server está en ejecución; mssql-tools18 (sqlcmd) está instalado y … es sysadmin. Antes de ejecutar el script, guarde la contraseña en una variable sin mostrarla en pantalla: read -rsp 'Contraseña: ' SQLCMDPASSWORD; export SQLCMDPASSWORD y sudo --preserve-env=SQLCMDPASSWORD bash <script>.sh.
• PowerShell se ha abierto como administrador (Inicio → "PowerShell" → clic derecho → Run as administrator; el título muestra Administrator:) con una cuenta de Windows que es sysadmin en SQL Server; sqlcmd está en el PATH.
• El directorio de copias (…) está en un disco distinto del de datos y tiene espacio libre suficiente para al menos una semana de copias (aproximadamente: tamaño de datos usado × 2 + cambio diario × número de días; con compresión, normalmente de 3 a 5 veces menos).
• El directorio de copias (…) está en un disco distinto del de datos y tiene espacio libre suficiente para al menos una semana de copias (aproximadamente: tamaño de datos usado × 2 + cambio diario × número de días; con compresión, normalmente de 3 a 5 veces menos).
• Se conoce el tamaño de las bases de datos; la ventana de copia (lo que tarda la full) no coincide con el horario laboral.
• Está decidido cómo se copian las copias de seguridad fuera del servidor (otro servidor, NAS, almacenamiento de objetos, software de copias). Una copia que está en el mismo disco no sirve si se pierde el disco o el servidor.
• La versión de SQL Server en la que se restaura es la misma o más reciente que aquella en la que se hizo la copia. Una copia hecha en una versión más reciente no se puede restaurar en una anterior (p. ej. 2022 → 2019 no es posible).
• Se ha informado al equipo de la aplicación y la aplicación está detenida: se cortan todas las conexiones a la base de datos sobre la que se restaura y no estará disponible durante la restauración.
• En el destino existen los directorios de datos (…) y de log (…), su propietario es mssql y tienen espacio libre para los tamaños de archivo de la base de datos que se restaura (no el tamaño de la copia; los valores Size de FILELISTONLY).
• En el destino existen los directorios de datos (…) y de log (…), la cuenta de servicio de SQL Server tiene permiso de escritura y hay espacio libre para los tamaños de archivo de la base de datos que se restaura.
• Desde el servidor de destino (…) se accede al de origen (…) …por SSH (root o un usuario con sudo)…a través del recurso compartido administrativo SMB (…)…; los archivos de copia se pueden copiar.

Fases de la guía

• Estado actual — Listar las bases de datos y las últimas copias
• Estrategia de copias de seguridad — Preparar el directorio de copias · Preparar el directorio de copias · Configurar el modelo de recuperación (…) · Configurar la preferencia de copia del AG (…) · Comprobar la réplica preferida · Crear el certificado y hacer su copia (cifrado) …
• Copia puntual — …Hacer una copia full COPY_ONLY…Hacer una copia full…Hacer una copia differential…Hacer una copia de log… (…) · Revisar el historial de copias (msdb.dbo.backupset)
• Restauración — Obtener la cadena de restauración del historial de copias · Copiar los archivos de copia al servidor de destino · Copiar los archivos de copia al servidor de destino · Cargar el certificado en el servidor de destino · Cargar el certificado en el servidor de destino · Examinar los archivos de copia (VERIFYONLY, HEADERONLY, FILELISTONLY) …
• Verificación de copias — Revisar el historial de copias (msdb.dbo.backupset) · Examinar el archivo de copia (VERIFYONLY, HEADERONLY, FILELISTONLY) · Hacer una restauración de prueba y ejecutar DBCC CHECKDB
• Comprobación de la cadena de copias — Diagnóstico: estado de la cadena (base de datos + historial de msdb) · ¿Están los archivos de copia en disco? · ¿Están los archivos de copia en disco? · Verificar cada copia de la cadena (VERIFYONLY) · Después: si la cadena está rota, hacer una nueva copia full
• Archivo de datos perdido / dañado: tail-log + restauración — Diagnóstico: estado de la base de datos y de sus archivos · Diagnóstico: ¿están los archivos en disco?, ¿está intacto el archivo de log? · Diagnóstico: ¿están los archivos en disco?, ¿está intacto el archivo de log? · Red de seguridad: copiar los archivos que quedan · Red de seguridad: copiar los archivos que quedan · Hacer la copia tail-log (NO_TRUNCATE) …
• Page restore (páginas dañadas) — Diagnóstico: localizar las páginas dañadas (suspect_pages, CHECKDB) · Diagnóstico: ¿a qué tabla / índice pertenece la página? · Red de seguridad: hacer una copia de log y detener los jobs de copia de log · Obtener el orden de restauración del historial de msdb (PAGE) · Hacer la copia tail-log (NORECOVERY): la base de datos deja de estar disponible · Restaurar las páginas dañadas y aplicar las copias de log (NORECOVERY) …
• master restore — Diagnóstico: archivo de copia, versión y lo que se perderá · Diagnóstico: ¿por qué no arranca SQL Server? · Diagnóstico: ¿por qué no arranca SQL Server? · Regenerar las bases de datos del sistema (master no arranca) · Regenerar las bases de datos del sistema (master no arranca) · Iniciar SQL Server en modo de usuario único …
• Restauración de msdb / model — Diagnóstico: estado, versión y archivo de copia · Red de seguridad: copia de la base de datos … actual · Detener SQL Server Agent · Restaurar la base de datos … (REPLACE) · Iniciar SQL Server Agent · Verificar
• Asignación de logins (orphaned user) — Diagnóstico: localizar los usuarios huérfanos (destino) · Generar en el origen el script de logins (SID + hash de la contraseña) · Generar en el origen el script de logins (SID + hash de la contraseña) · Copiar el script de logins al destino y ejecutarlo · Copiar el script de logins al destino y ejecutarlo · Vincular los usuarios restantes a un login (ALTER USER WITH LOGIN) …
• Copia cifrada: cargar el certificado — Diagnóstico: certificados y master key de este servidor · Diagnóstico: huella digital del archivo del certificado · Diagnóstico: huella digital del archivo del certificado · Cargar el certificado con su clave privada · Cargar el certificado con su clave privada · Verificar: la copia ya se puede leer …