Una de las funcionalidades interesantes de SQL Server 2005 o superior es la posibilidad de realizar restauraciones de base de datos parciales. Además de permitirnos tener disponibilidad parcial de la base de datos mientras la recuperamos, también es especialmente útil esta funcionalidad cuando disponemos de filegroups históricos en modo solo lectura. Cuando tenemos filegroups de solo lectura realizaremos los backups del filegroup primario y del resto de filegroups de lectura/escritura con la frecuencia habitual mientras que podremos mantener un mismo backup del filegroup de solo lectura para siempre. Esto nos puede ahorrar mucho espacio y tiempo en los backups mientras que mantenemos la información accesible en modo solo lectura.
Vamos a crear una base de datos que tendrá un filegroup primario de lectura/escritura y un filegroup de solo lectura:
CREATE DATABASE TEST_FG
GO
ALTER DATABASE TEST_FG ADD FILEGROUP FG_READONLY
GO
ALTER DATABASE TEST_FG
ADD FILE ( NAME = N’FG_READONLY’,
FILENAME = N’C:PROGRAM FILESMICROSOFT SQL SERVERMSSQL11.MSSQLSERVERMSSQLDATAFG_READONLY.MDF’ , SIZE = 512KB , FILEGROWTH = 1024KB )
TO FILEGROUP FG_READONLY
GO
Una vez creada la base de datos y añadido el filegroup que será de solo lectura insertaremos algunos datos en una tabla creada en dicho filegroup. Típicamente utilizaremos particiones de solo lectura dentro de una tabla particionada cuyas particiones “activas” estarán en filegroups de lectura/escritura:
USE TEST_FG
GO
CREATE TABLE T_READONLY (I INT) ON FG_READONLY
GO
INSERT INTO T_READONLY VALUES (1)
GO
Una vez tenemos el dato insertado, pasaremos el filegroup a solo lectura:
USE MASTER
GO
ALTER DATABASE TEST_FG SET SINGLE_USER WITH ROLLBACK IMMEDIATE
GO
ALTER DATABASE TEST_FG MODIFY FILEGROUP FG_READONLY READ_ONLY
GO
ALTER DATABASE TEST_FG SET MULTI_USER
A continuación realizaremos un backup del filegroup primario y acto seguido del filegroup de solo lectura:
BACKUP DATABASE [TEST_FG] FILEGROUP = N’PRIMARY’ TO DISK =
N’C:PROGRAM FILESMICROSOFT SQL SERVERMSSQL11.MSSQLSERVERMSSQLBACKUPTEST_FG.BAK’
GO
BACKUP DATABASE [TEST_FG] FILEGROUP = N’FG_READONLY’ TO DISK =
N’C:PROGRAM FILESMICROSOFT SQL SERVERMSSQL11.MSSQLSERVERMSSQLBACKUPFG_READONLY.BAK’
GO
En este punto no tendríamos ningún problema para realizar una restauración parcial de ambos ficheros. Ahora imaginemos ahora que alguien necesitara modificar la tabla o partición y la pasara a lectura/escritura. Una vez cambiado el estado modificará la tabla, volverá a dejar el filegroup de nuevo en solo lectura y sobrescribirá el backup anterior:
USE MASTER
GO
ALTER DATABASE TEST_FG SET SINGLE_USER WITH ROLLBACK IMMEDIATE
GO
ALTER DATABASE TEST_FG MODIFY FILEGROUP FG_READONLY READ_WRITE
GO
ALTER DATABASE TEST_FG SET MULTI_USER
GO
USE TEST_FG
GO
INSERT INTO T_READONLY VALUES (2)
GO
ALTER DATABASE TEST_FG SET SINGLE_USER WITH ROLLBACK IMMEDIATE
GO
ALTER DATABASE TEST_FG MODIFY FILEGROUP FG_READONLY READ_ONLY
GO
ALTER DATABASE TEST_FG SET MULTI_USER
GO
BACKUP DATABASE [TEST_FG] FILEGROUP = N’FG_READONLY’ TO DISK =
N’C:PROGRAM FILESMICROSOFT SQL SERVERMSSQL11.MSSQLSERVERMSSQLBACKUPFG_READONLY.BAK’ WITH FORMAT,INIT
En este momento, para poder hacer una restauración consistente, necesitaríamos también contar con un backup del filegroup primario. Desgraciadamente antes de poder realizar dicho backup, ocurre un problema grave con los ficheros de datos y del log y nos encontramos con la necesidad de restaurar la base de datos a partir de los backups disponibles.
Comenzaríamos realizando un restore parcial del filegroup primario (importante el uso de la cláusula PARTIAL):
RESTORE DATABASE [test_FG2] FILE = N’test_FG’
FROM DISK = N’C:Program FilesMicrosoft SQL ServerMSSQL11.MSSQLSERVERMSSQLBackuptest_FG.bak’ WITH FILE = 1,
MOVE N’test_FG’ TO N’C:Program FilesMicrosoft SQL ServerMSSQL11.MSSQLSERVERMSSQLDATAtest_FG2.mdf’,
MOVE N’test_FG_log’ TO N’C:Program FilesMicrosoft SQL ServerMSSQL11.MSSQLSERVERMSSQLDATAtest_FG2.ldf’, NOUNLOAD, STATS = 10
, PARTIAL
GO
A continuación intentaremos restaurar el backup del filegroup de solo lectura:
RESTORE DATABASE [test_FG2] FILE = N’FG_READONLY’
FROM DISK = N’C:Program FilesMicrosoft SQL ServerMSSQL11.MSSQLSERVERMSSQLBackupFG_READONLY.bak’ WITH FILE = 1,
MOVE N’FG_READONLY’ TO N’C:Program FilesMicrosoft SQL ServerMSSQL11.MSSQLSERVERMSSQLDATAFG_READONLY2.mdf’,
NOUNLOAD, STATS = 10
GO
Msg 3116, Level 16, State 2, Line 1
The supplied backup is not on the same recovery path as the database, and is ineligible for use for an online file restore.
Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally
Al realizar esta operación nos aparece un error indicando que el backup no es el apropiado y no podemos utilizarlo para realizar la restauración. Para intentar solucionar este problema, ponemos la base de datos en modo emergencia y con ello aparentemente conseguimos restaurar el filegroup:
ALTER DATABASE [test_FG2] SET EMERGENCY;
RESTORE DATABASE [test_FG2] FILE = N’FG_READONLY’
FROM DISK = N’C:Program FilesMicrosoft SQL ServerMSSQL11.MSSQLSERVERMSSQLBackupFG_READONLY.bak’ WITH FILE = 1,
MOVE N’FG_READONLY’ TO N’C:Program FilesMicrosoft SQL ServerMSSQL11.MSSQLSERVERMSSQLDATAFG_READONLY2.mdf’,
NOUNLOAD, STATS = 10, REPLACE
GO
100 percent processed.
Processed 16 pages for database ‘test_FG2’, file ‘FG_READONLY’ on file 1.
Processed 1 pages for database ‘test_FG2’, file ‘test_FG_log’ on file 1.
The database can not be brought online because file ‘test_FG’ is currently restored to LSN 36000000038200001 but must be restored to LSN 36000000038200001.
The roll forward start point is now at log sequence number (LSN) 36000000038200001. Additional roll forward past LSN 36000000038200001 is required to complete the restore sequence.
This RESTORE statement successfully performed some actions, but the database could not be brought online because one or more RESTORE steps are needed. Previous messages indicate reasons why recovery cannot occur at this point.
RESTORE DATABASE … FILE=<name> successfully processed 17 pages in 0.033 seconds (3.832 MB/sec).
Desgraciadamente si nos fijamos bien el comando ha restaurado los datos del filegroup pero nos indica que la base de datos no ha podido ser “avanzada” lo suficiente para estar online. Se necesita de los log de transacciones para poder avanzar la base de datos hasta un punto en el tiempo donde ambos filegroups puedan ser consistentes entre si. Como no contamos con el backup del log de transacciones, la base de datos quedará en estado Restoring y no será accesible:
Llegados a este punto no tendremos otra opción que optar por reconstruir el log de transacciones de forma que podamos “ignorar” la falta de coherencia entre los ficheros. Para ello crearemos una nueva base de datos copiando los ficheros y utilizando la opción FOR ATTACH_REBUILD_LOG:
CREATE DATABASE [test_fg3]
ON PRIMARY ( NAME = N’test_fg3′, FILENAME = N’C:Program FilesMicrosoft SQL ServerMSSQL11.MSSQLSERVERMSSQLDATAtest_fg3.mdf’),
( NAME = N’test_fg3′, FILENAME = N’C:Program FilesMicrosoft SQL ServerMSSQL11.MSSQLSERVERMSSQLDATAfg_readonly3.mdf’)
FOR ATTACH_REBUILD_LOG
En conclusión deberemos tener algo de cuidado cuando tengamos bases de datos con varios filegroups y deseemos realizar backups y restores parciales. En los casos de filegroups de solo lectura que pasen a lectura/escritura temporalmente y vuelvan a solo lectura es importante que se realice inmediatamente otro backup del primario para que la información se pueda recuperar de forma consistente (o al menos disponer de los backups del log de transacciones de dicho periodo).
La restauración de distintos filegroups de lectura/escritura siempre deberá sincronizarse con la ayuda de los backups del log de transacciones y en el caso de no disponer de ellos tendremos que recurrir a procedimientos de emergencia que no nos garantizarán la coherencia de los datos restaurados. Será muy conveniente en estos casos realizar un chequeo de base de datos inmediatamente tras la restauración para poder detectar y corregir incoherencias como por ejemplo claves ajenas que apunten a filas no existentes.
