Si has perdido el log de transacciones de tu base de datos, lo más seguro y fiable sería que restaures con el último backup. Lo que paso a comentaros como alternativas, si no disponéis de backups actualizados, son medidas desesperadas con bajas probabilidades de éxito.
Si no tienes backups (aparte de anotarte que debes hacerlos), tendrías que recurrir a sp_attach_single_file_db. Este proceso te regenera el log, pero no seas demasiado optimista, es posible que la base de datos no levante (ya por no haber realizado un apagado ordenado y controlado de la base de dado) o puede que levante, pero perdiendo información.
El procedimiento que vamos a comentar, para recuperar la base de datos lo usaremos en última instancia, aconsejo antes de empezar, realizar una copia de los archivos de esta base de datos por si algo sale mal o se producen errores irreparables, yo nunca he visto fallar este procedimiento, a menos que estemos ante ficheros .mdf corruptos.
Antes de empezar, pararemos el servicio de SQL Server, copiaremos a otro directorio el fichero .mdf,… sin eliminar de su ruta los ficheros originales, y volveremos a arrancar el Servicio de SQL Server.
Ahora que tienes backup del fichero .mdf en un directorio distinto, procede a eliminar la bbdd, para posteriormente hacer un attach con sp_attach_single_file_db para que este procese regenere un nuevo .ldf
sp_attach_single_file_db @dbname = 'emergencydemo',
@physname = 'D:\MSSQL2005\MSSQL.1\MSSQLDATA1\emergencydemo.mdf';
File activation failure. The physical file name "E:MSSQL2005MSSQL.1MSSQLDATAemergencydemo_log.ldf" may be incorrect.
The log cannot be rebuilt because the database was not cleanly shut down.
Msg 1813, Level 16, State 2, Line 1
Could not open new database 'IM_AUSTORE'. CREATE DATABASE is aborted.
Lamentablemente observamos que no se ha podido realizar el attach de la base de datos, regenerando el .ldf,
Voy a comentar los pasos a realizar a través de un ejemplo. Parto de una base de datos llamada emergencydemo, donde la base de datos no tiene ningún archivo de log de transacciones, con una de sus tablas dañada.
Primero vamos a Crearemos una base de datos nueva que se llame como tu anterior base de datos y que tenga los mismos nombres físicos para sus ficheros de datos, luego paras el servicio de sql server, sobrescribes el fichero .mdf con el .mdf que quieres recuperar y arrancas el servicio de sql server. La base de dato levantará en modo "suspect" y en ese estado, trataremos de poner ONLINE la bdd, sólo para ver lo que sucede:
USE master
GO
EXEC SP_CONFIGURE 'Allow updates',1
GO
RECONFIGURE WITH OVERRIDE
GO
ALTER DATABASE emergencydemo SET ONLINE;
GO
File activation failure. The physical file name "C:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLDATAemergencydemo_log.LDF" may be incorrect.
The log cannot be rebuilt because the database was not cleanly shut down.
Msg 945, Level 14, State 2, Line 1
Database 'emergencydemo' cannot be opened due to inaccessible files or insufficient memory or disk space. See the SQL Server errorlog for details.
Failed to restart the current database. The current database is switched to master.
Msg 5069, Level 16, State 1, Line 1
ALTER DATABASE statement failed.
El primer mensaje anterior tiene sentido, SQL Server sabe que necesita recuperar la bbdd porque no se cerró limpiamente, pero el archivo del log de transacciones no se encuentra en su ubicación. El segundo mensaje es de la nueva característica de 2005, Nos sugiere que creemos automáticamente un archivo para el log de transacciones, se puede hacer usando el procedimiento sp_attach_single_file_db siempre y cuando la base de datos haya cerrado sin errores.
Ahora Vamos a ejecutar la reparación de emergencia en modo:
DBCC CHECKDB (emergencydemo, REPAIR_ALLOW_DATA_LOSS) WITH NO_INFOMSGS;
GO
Msg 945, Level 14, State 2, Line 1
Database 'emergencydemo' cannot be opened due to inaccessible files or insufficient memory or disk space. See the SQL Server errorlog for details.
Vemos en la salida del anterior paso, como falla el CHECKDB, ya que tiene problemas para acceder a la base de datos. Vamos a mirar en qué estado se encuentra la base de datos:
SELECT state_desc FROM sys.databases WHERE name='emergencydemo';
GO
state_desc
------------------------------------------------------------
RECOVERY_PENDING
Ahora vamos a poner en modo de emergencia la bbdd y despues ejecutar el CHECKDB:
ALTER DATABASE emergencydemo SET EMERGENCY;
GO
DBCC CHECKDB (emergencydemo, REPAIR_ALLOW_DATA_LOSS) WITH NO_INFOMSGS;
GO
Msg 7919, Level 16, State 3, Line 1
Repair statement not processed. Database needs to be in single user mode.
Como nos indica la anterior salida, es necesario poner la bbdd en SINGLE_USER despues de que se encuentre en estado de EMERGENCY, para poder ejecutar el CHECKDB, ahora volvemos a ejecutarlo de la forma correcta:
ALTER DATABASE emergencydemo SET SINGLE_USER;
GO
DBCC CHECKDB (emergencydemo, REPAIR_ALLOW_DATA_LOSS) WITH NO_INFOMSGS;
GO
File activation failure. The physical file name "C:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLDATAemergencydemo_log.LDF" may be incorrect.
The log cannot be rebuilt because the database was not cleanly shut down.
Warning: The log for database 'emergencydemo' has been rebuilt. Transactional consistency has been lost. The RESTORE chain was broken, and the server no longer has context on the previous log files, so you will need to know what they were. You should run DBCC CHECKDB to validate physical consistency. The database has been put in dbo-only mode. When you are ready to make the database available for use, you will need to reset database options and delete any extra log files.
Esta vez funcionó. A continuación aparece una advertencia sobre el registro de transacciones que ha sido reconstruido, Si hubiera habido corrupción, se hubiera reflejado en la salida del DBCC CHECKDB.
2007-10-02 17:21:20.95 spid51 Starting up database 'emergencydemo'.
2007-10-02 17:21:20.96 spid51 Error: 17207, Severity: 16, State: 1.
2007-10-02 17:21:20.96 spid51 FileMgr::StartLogFiles: Operating system error 2(The system cannot find the file specified.) occurred while creating or opening file 'C:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLDATAemergencydemo_log.LDF'. Diagnose and correct the operating system error, and retry the operation.
2007-10-02 17:21:20.96 spid51 Starting up database 'emergencydemo'.
2007-10-02 17:21:21.10 spid51 Starting up database 'emergencydemo'.
2007-10-02 17:21:21.18 spid51 Warning: The log for database 'emergencydemo' has been rebuilt. Transactional consistency has been lost. The RESTORE chain was broken, and the server no longer has context on the previous log files, so you will need to know what they were. You should run DBCC CHECKDB to validate physical consistency. The database has been put in dbo-only mode. When you are ready to make the database available for use, you will need to reset database options and delete any extra log files.
2007-10-02 17:21:21.18 spid51 Warning: The log for database 'emergencydemo' has been rebuilt. Transactional consistency has been lost. The RESTORE chain was broken, and the server no longer has context on the previous log files, so you will need to know what they were. You should run DBCC CHECKDB to validate physical consistency. The database has been put in dbo-only mode. When you are ready to make the database available for use, you will need to reset database options and delete any extra log files.
2007-10-02 17:21:21.99 spid51 EMERGENCY MODE DBCC CHECKDB (emergencydemo, repair_allow_data_loss) WITH no_infomsgs executed by ROADRUNNERPRpaul found 0 errors and repaired 0 errors. Elapsed time: 0 hours 0 minutes 1 seconds.
Chequeamos o miramos en qué estado esta la base de datos ahora:
SELECT state_desc FROM sys.databases WHERE name='emergencydemo';
GO
state_desc
------------------------------------------------------------
ONLINE
Finalmente ponemos la base de datos en MULTI_USER:
ALTER DATABASE emergencydemo SET MULTI_USER;
GO
USE master
GO
EXEC SP_CONFIGURE 'Allow updates',0
GO
RECONFIGURE WITH OVERRIDE
GO
USE EMERGENCYDEMO;
GO
SELECT * FROM salaries;
GO
FirstName LastName Salary
-------------------- -------------------- -----------
John Williamson 10000
Stephen Brown 0
Jack Bauer 10000
(3 row(s) affected)
A pesar de que el registro de transacciones ha sido reconstruido y reparado, la transacción original nunca tuvo la oportunidad de deshacerse, porque se perdió o eliminó el registro de transacciones.
Recuerde, sólo se debe usar este procedimiento como un último recurso, pero si te ves en problemas, porque no dispones de backup reciente, puede serte útil
Muy brevemente me gustaría comentaros que es posible
recuperar una página de datos daña/s, y solo esa pagina o paginas si tenemos un
backup completo sin la/s pagina/s dañada/s anterior:
Imaginemos que tenmos una bd: BD_pag_error PAGE con pagina
dañana:
Msg 8909, Level
16, State 1, Line 1
Table
error: Object ID 0, index ID -1, partition ID 0, alloc unit ID 0 (type
Unknown), page ID (1:158) contains an incorrect page ID in its page header. The
PageId in the page header = (0:0).
CHECKDB found 0
allocation errors and 1 consistency errors not associated with any single
object.
Msg 8928, Level
16, State 1, Line 1
Object ID
2073058421, index ID 1, partition ID 72057594038386688, alloc unit ID
72057594042384384 (type In-row data): Page (1:158) could not be processed. See
other errors for details.
CHECKDB found 0
allocation errors and 1 consistency errors in table 'sales' (object ID
2073058421).
CHECKDB found 0
allocation errors and 2 consistency errors in database 'dbccpagetest'.
repair_allow_data_loss
is the minimum repair level for the errors found by DBCC CHECKDB (BD_pag_error).
DBCC TRACEON (3604);
GO
DBCC PAGE (BD_pag_error PAGE, 1, 158, 3);
GO
DBCC
execution completed. If DBCC printed error messages, contact your system
administrator.
PAGE: (0:0)
BUFFER:
BUF @0x02C0632C
bpage =
0x04C12000 bhash = 0x00000000 bpageno = (1:158)
bdbid = 9
breferences = 0 bUse1 = 37241
bstat = 0xc00009
blog = 0x89898989 bnext = 0x00000000
PAGE HEADER:
Page @0x04C12000
m_pageId = (0:0)
m_headerVersion = 0 m_type = 0
m_typeFlagBits =
0x0 m_level = 0 m_flagBits = 0x200
m_objId
(AllocUnitId.idObj) = 0 m_indexId (AllocUnitId.idInd) = 0 Metadata: AllocUnitId
= 0
Metadata:
PartitionId = 0 Metadata: IndexId = -1 Metadata: ObjectId = 0
m_prevPage =
(0:0) m_nextPage = (0:0) pminlen = 0
m_slotCnt = 0
m_freeCnt = 0 m_freeData = 0
m_reservedCnt = 0
m_lsn = (0:0:0) m_xactReserved = 0
m_xdesId = (0:0)
m_ghostRecCnt = 0 m_tornBits = 16777216
Allocation Status
GAM (1:2) =
ALLOCATED SGAM (1:3) = NOT ALLOCATED
PFS (1:1) = 0x60
MIXED_EXT ALLOCATED 0_PCT_FULL DIFF (1:6) = NOT CHANGED
ML (1:7) = NOT
MIN_LOGGED
Msg 2514,
Level 16, State 5, Line 2
DBCC PAGE
error: Invalid page type - dump style 3 not possible.
Para solucionarlo
usamos el backup completo y sin la pagina dañada anterior:
USE master;
GO
RESTORE DATABASE
BD_pag_error PAGE = '1:158'
FROM DISK
= 'C:\ackup_anterior_sin_error.bak';
GO
Processed 1 pages
for database ‘BD_pag_error’, file '
BD_pag_error ' on file 1.
The roll
forward start point is now at log sequence number (LSN) 32000000047000001.
Additional roll forward past LSN 33000000001700001 is required to complete the
restore sequence.
RESTORE
DATABASE ... FILE=<name> successfully processed 1 pages in 0.176 seconds
(0.046 MB/sec).
--
Need to complete roll forward. Backup the log tail...
BACKUP LOG BD_pag_error TO
DISK = 'C:\BD_pag_error_log.bak' WITH
INIT;
GO
--
... and restore it again.
RESTORE LOG
BD_pag_error FROM DISK
= 'C:\BD_pag_error _log.bak';
GO
Processed 5 pages
for database ' BD_pag_error ', file ' BD_pag_error _log' on file 1.
BACKUP LOG
successfully processed 5 pages in 0.146 seconds (0.248 MB/sec).
Processed 0 pages
for database ' BD_pag_error ', file ' BD_pag_error ' on file 1.
RESTORE LOG
successfully processed 0 pages in 0.004 seconds (0.000 MB/sec).
And
now we should have a clean database:
DBCC CHECKDB (dbccpagetest) WITH NO_INFOMSGS;
GO
Command(s)
completed successfully.
Fuentes:
Microsoft, MSDN
Apunte y recopilación por Norman M. Pardell
Puedes consultarme, si deseas cualquier aclaración, pregunta o sugerencia en: Contacto, contestaré tan pronto como me sea posible.
