Cómo recuperar registros de tabla eliminados en SQL Server

Si ha ejecutado accidentalmente un comando DROP o DELETE contra una tabla con una cláusula WHERE incorrecta y ha perdido registros importantes, puede recuperarlos utilizando cualquiera de los siguientes métodos.

Nota: Las operaciones de truncado no pueden recuperarse. Sólo se pueden recuperar las operaciones de borrado.

Métodos para recuperar registros de tabla eliminados en SQL Server

Método 1 – Utilizar el número de secuencia de registro (LSN)

Nota: Este método no funcionará si la copia de seguridad del registro de transacciones está dañada o no existe. En ese caso, pase al siguiente método.

Cada registro de transacciones de SQL Server se identifica unívocamente mediante un número de secuencia de registro (LSN). Puede construir secuencias de restauración utilizando el LSN de un registro de registro en el que se produjo un evento significativo.

Veamos un ejemplo para entender cómo se puede utilizar el método del número de secuencia de registro para recuperar registros borrados de una tabla. En este ejemplo, crearemos una base de datos y una tabla, insertaremos filas en la tabla, eliminaremos algunas filas, obtendremos información sobre los datos eliminados y recuperaremos los datos.

Pasos para Recuperar Registros Borrados de una Tabla SQL con LSN

En esta sección, crearemos una base de datos de prueba y una tabla en la que ejecutaremos una operación DELETE. A continuación, encontraremos los datos eliminados y los recuperaremos utilizando LSN.

Paso 1: Crear una base de datos

Ejecute la siguiente consulta para crear una base de datos llamada ‘RecoverDeletedRecords’ y una tabla llamada ‘Employee’:

USE [master]; 

IR A

CREAR BASE DE DATOS RecoverDeletedRecords;

IR A

USE RecuperarRegistrosBorrados;  

IR A

CREAR TABLA [Empleado] (

[INT IDENTITY,

[Fecha] DATETIME DEFAULT GETDATE (),

[Ciudad] CHAR (25) DEFAULT ‘Ciudad1’);

Paso 2: Insertar datos en la tabla

Hemos creado una tabla llamada ‘RecoverDeletedRecords’ y una tabla ‘Employee’ con tres columnas. Ahora, insertaremos filas en la tabla ejecutando la siguiente consulta:

USE RecuperarRegistrosBorrados;

IR A

INSERT INTO Empleado DEFAULT VALUES;

IR AL 100

Paso 3: Eliminar filas de la tabla

Ahora vamos a eliminar algunas filas ejecutando lo siguiente:

USE RecuperarRegistrosBorrados

Vaya a

DELETE Empleado

WHERE [Nº Sr] < 10

IR A

Seleccionar * de Empleado

Como se muestra en la imagen anterior, se han eliminado de la tabla ‘Empleado’ todas las filas que tienen un Nº Sr. inferior a 10.

Paso 4: Obtener información sobre las filas eliminadas

A continuación, obtendremos información sobre las filas eliminadas buscando en el registro de transacciones:

USE RecuperarRegistrosBorrados

IR A

SELECCIONE

 [LSN actual],  

 [ID de transacción],

     Operación,

     Contexto,

     AllocUnitName

DESDE

    fn_dblog(NULL, NULL)

DONDE

    Operación = ‘LOP_DELETE_ROWS’

Después de obtener los ID de transacción de las filas eliminadas, tendremos que encontrar la hora en la que se eliminaron las filas.

Paso 5: Obtener el número de secuencia del registro LOP_BEGIN_XACT

Para encontrar el momento exacto en que se eliminaron las filas, debemos utilizar el ID de transacción para obtener el LSN del registro LOP_BEGIN_XACT de una transacción:

USE RecuperarRegistrosBorrados

IR A

SELECCIONE

 [LSN actual],  

 Operación,

     [ID de transacción],

     [Hora de inicio],

     [Nombre de la transacción],

     [Transacción SID]

DESDE

    fn_dblog(NULL, NULL)

DONDE

    [ID de transacción] = ‘0000:0000020e’

Y

    [Operación] = ‘LOP_BEGIN_XACT’

En la captura de pantalla anterior, podemos ver el LSN actual de la transacción junto con detalles como la hora ‘2021/03/15 19:36:59:337’ cuando se ejecutó una sentencia DELETE con LSN ‘00000014:0000001a:0001’ bajo el ID de transacción ‘0000:0000020e’. Para recuperar los datos eliminados de la tabla de base de datos SQL, continúe con el siguiente paso.

Paso 6: Recuperar registros borrados en SQL Server

Para recuperar los registros borrados de la tabla SQL, necesitaremos convertir los valores LSN de hexadecimal a decimal.  Para ello, añada ‘0x’ antes del número de secuencia de registro como se muestra en el código siguiente:

-Restauración Copia de seguridad completa con norecovery.

RESTORE DATABASE RecoverDeletedRecords_COPY

    FROM DISK = ‘C:\Program Files\Microsoft SQL Server\MSSQL10_50.STELLAR\MSSQL\Backup\RecoverDeletedRecords.bak’

CON

    MUEVE ‘RecoverDeletedRecords’ A ‘C:\Program Files\Microsoft SQL Server\MSSQL10_50.STELLAR\MSSQL\Backup\RecoverDeletedRecords.mdf’,

    MOVE ‘RecoverDeletedRecords_log’ TO ‘C:\Program Files\Microsoft SQL Server\MSSQL10_50.STELLAR\MSSQL\Backup\RecoverDeletedRecords.ldf’,

    REEMPLAZAR, NI RECUPERAR;

    IR A

-Restaurar copia de seguridad del registro con la opción STOPBEFOREMARK para recuperar el LSN exacto.

   RESTORE LOG RecoverDeletedRecords_COPY

DESDE

    DISK = N’C:\Program Files\Microsoft SQL Server\MSSQL10_50.STELLAR\MSSQL\Backup\RecoverDeletedRecords_tlogbackup.trn’

CON

    STOPBEFOREMARK = ‘lsn:0x00000014:0000001a:0001’

Ahora que sus datos están restaurados, compruebe si se han recuperado los registros borrados. Para ello, ejecute la siguiente consulta para ver las diez primeras filas de la tabla que se eliminaron (en el paso 3 anterior).

USE RecoverDeletedRecords_COPY

IR A

SELECT * from Empleado

Si los registros eliminados de la tabla no se han recuperado, pruebe a utilizar una herramienta de recuperación de SQL como Stellar Repair for MS SQL para recuperar los datos eliminados.

Nota: En el código anterior, sustituya la ruta ‘C:\Program Files\Microsoft SQL Server\MSSQL10_50.STELLAR\MSSQL\Backup\RecoverDeletedRecords.bak’ por la ruta donde se encuentra su archivo de copia de seguridad. Asimismo, sustituya la ruta de los archivos .mdf y .ldf por la ruta donde haya guardado los archivos de base de datos.

Método 2 – Uso de software de recuperación de SQL

El software de recuperación SQL de Stellar® puede ayudarle a recuperar todos los registros eliminados de una base de datos SQL Server en unos sencillos pasos. Además de los datos eliminados, el software también puede recuperar todos los demás objetos como vistas, desencadenadores, procedimientos almacenados, etc. manteniendo la integridad de los datos.

Antes de continuar

Antes de utilizar el software para restaurar los registros, asegúrese de que su sistema cumple los siguientes requisitos previos:

• En primer lugar, necesitará tener instalado SQL Server. Puede utilizar cualquier SQL Server instalado en Windows.
• En segundo lugar, asegúrese de que tiene instalado SQL Server Management Studio (SSMS)
• Software Stellar Repair for MS SQL

Pasos para recuperar registros de tabla eliminados con Stellar Repair for MS SQL

Descargue la versión demo del software de recuperación de SQL para recuperar filas eliminadas o bases de datos dañadas:

• Inicie el software Stellar Repair for MS SQL. La interfaz principal del software se abre con una instrucción para detener su base de datos SQL Server y crear una copia en una ubicación diferente. Pulse OK.

• Examine el archivo MDF de la base de datos SQL que desea reparar. Si no conoce la ubicación del archivo MDF, haga clic en Buscar para encontrar y seleccionar el archivo:

Nota: Si no sabe dónde se encuentra su base de datos, puede encontrarla en SSMS en las propiedades de la base de datos, en la página del archivo de datos (consulte la imagen siguiente).

• Tras seleccionar el archivo de base de datos, seleccione la opción Incluir registros eliminados y, a continuación, haga clic en Reparar.

• El software le pedirá que seleccione un modo de análisis adecuado para reparar el archivo de base de datos. Seleccione “Análisis estándar” o “Análisis avanzado” y, a continuación, haga clic en Aceptar.

• Haga clic en Aceptar cuando se abra el cuadro de mensaje “Reparación completada”.

• El software muestra una vista previa de los registros de tabla recuperables. También restaura otros objetos de la base de datos como vistas, sinónimos, procedimientos almacenados, funciones, reglas, valores predeterminados, tipos de datos, desencadenadores, secuencias y conjuntos. El software también muestra un informe de registro que contiene información como el tamaño de la base de datos, la intercalación, la ruta del archivo mdf y la lista de tablas y objetos.

• A continuación, haga clic en Guardar en el menú Archivo para guardar los datos recuperados.

• En el cuadro de diálogo ‘Guardar base de datos’, puede elegir guardar los datos recuperados en formatos de archivo MDF, CSV, HTML o XLS (Excel). En este ejemplo, elegiremos MSSQL:

• Puede crear una nueva base de datos o trabajar con la base de datos en línea. Puede restaurar en otra instancia de SQL Server y especificar el tipo de autenticación, es decir, autenticación de Windows o autenticación de SQL Server. También puede especificar la ubicación de los archivos de la base de datos:

• Haga clic en Guardar.

Los registros de tabla recuperados y otros datos seleccionados se guardarán en el archivo MDF reparado.

Vea el proceso de trabajo completo del software SQL Recovery:

Conclusión

En este artículo, aprendimos cómo recuperar registros eliminados en SQL Server. Estas soluciones pueden funcionar en cualquier versión de SQL Server en Windows. Si dispone de la copia de seguridad completa de la base de datos, puede intentar recuperar los registros eliminados utilizando el registro de transacciones con Log Sequence Number (LSN). Si la copia de seguridad está dañada, utilice Stellar Repair for MS SQL para recuperar los datos eliminados en SQL Server o en otros formatos como archivos de valores separados por comas (CSV), HTML o XLS.

El software de reparación de bases de datos SQL no sólo ayuda a recuperar las filas eliminadas, sino que también restaura las bases de datos SQL dañadas por ataques de virus, fallos de hardware u otros motivos.

Was this article helpful?

YES0

NO

Sobre el autor

Himanshu Shakya

Himanshu is a Tech Enthusiast & Blogger at Stellar and having knowledge of Japanese Language. And apart from this in his spare time he likes playing Chess.