SQL Server - Como as páginas de dados são armazenadas ao usar um índice clusterizado

Question

Matthias L

Asked: 2023-02-11 08:41:22 +0800 CST2023-02-11 08:41:22 +0800 CST 2023-02-11 08:41:22 +0800 CST

Durante uma restauração fragmentada online, é possível restaurar para um ponto no tempo?

772

Eu tenho um banco de dados (modelo de recuperação completa) com vários grupos de arquivos, cada um com backups completos e diferentes. Sou capaz de replicar uma restauração fragmentada online (SQl Server 2019 Enterprise Edition), conforme descrito aqui: Exemplo: restauração fragmentada do banco de dados (modelo de recuperação completa)

No entanto, quando adiciono uma cláusula STOPAT ao restaurar o último backup de log, isso funciona apenas para o grupo de arquivos primário. Para todos os grupos de arquivos a seguir, recebo esta mensagem:

Msg 4342, Nível 16, Estado 1, Linha 161 A recuperação pontual não é possível, a menos que o grupo de arquivos primário faça parte da sequência de restauração. Omita a cláusula pontual ou restaure o grupo de arquivos primário.

Quando tento restaurar o log sem a cláusula STOPAT, recebo isto:

O backup fornecido não está no mesmo caminho de recuperação do banco de dados e não é elegível para uso em uma restauração de arquivo online.

Não consegui encontrar nenhuma restrição sobre recuperação pontual durante restaurações fragmentadas online, exceto esta parte (encontrada em Restaurações fragmentadas (SQL Server) ):

Se uma sequência de restauração parcial excluir qualquer grupo de arquivos FILESTREAM, a restauração pontual não será suportada. Você pode forçar a continuação da sequência de restauração. No entanto, os grupos de arquivos FILESTREAM que são omitidos de sua instrução RESTORE nunca podem ser restaurados. Para forçar uma restauração pontual, especifique a opção CONTINUE_AFTER_ERROR juntamente com a opção STOPAT, STOPATMARK ou STOPBEFOREMARK, que você também deve especificar em suas instruções RESTORE LOG subsequentes. Se você especificar CONTINUE_AFTER_ERROR, a sequência de restauração parcial será bem-sucedida e o grupo de arquivos FILESTREAM se tornará irrecuperável.

Como o banco de dados não contém nenhum grupo de arquivos de fluxo de arquivos, isso não se aplica. Alguém sabe se a recuperação pontual é possível no meu cenário?

Obrigado

Editar 3: Obrigado a Paul White pela solução: faça um backup de log após restaurar o grupo de arquivos primário para um ponto no tempo e use-o para avançar os grupos de arquivos restantes:

----------------------------------------------------------------------
--  ONLINE PIECEMEAL RESTORE WITH POINT-IN-TIME RECOVERY
------------------------------------------------------------------------

---------------------------------
-- CREATE DB
-- log, 2 filegroups (primary + A) and one table each
---------------------------------
USE [master]
GO

CREATE DATABASE [RestoreTest]
ON PRIMARY(
    NAME = 'PRIMARY',
    FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\DATA\RestoreTest.mdf'
),
FILEGROUP A(
    NAME = 'RestoreTest_A',
    FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\DATA\RestoreTest_A.ndf'
)
LOG ON(
    NAME = 'RestoreTest_log',
    FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\DATA\RestoreTest_Log.ldf'
)
GO

USE [RestoreTest]
GO

CREATE TABLE [Table1](
    [X] INT
)
ON [PRIMARY];

CREATE TABLE [Table2](
    [X] INT
)
ON [A];
GO


---------------------------------
-- Backups
---------------------------------

-- full backups of each filegorup
BACKUP DATABASE [RestoreTest]
    FILEGROUP = 'PRIMARY'
    TO DISK = 'C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\Backup\RestoreTest_Full.bak'
    WITH INIT

BACKUP DATABASE [RestoreTest]
    FILEGROUP = 'A'
    TO DISK = 'C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\Backup\RestoreTest_A_Full.bak'
    WITH INIT
GO

-- store current time for later point-in-time recovery
WAITFOR DELAY '00:00:01'
DECLARE @now DATETIME = (SELECT GETDATE())
EXEC sp_set_session_context 'stopat', @now; 
WAITFOR DELAY '00:00:01'

--insert some data
INSERT INTO [Table1]
VALUES (1)

INSERT INTO [Table2]
VALUES (1)
GO

-- then take log backup
BACKUP LOG [RestoreTest]
    TO DISK= 'C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\Backup\RestoreTest_Log.bak'
    WITH INIT
GO


---------------------------------
-- RESTORE (point-in-time)
---------------------------------
    
-- drop database
USE [master]
DROP DATABASE [RestoreTest]
GO

-- restore primary filegorup
DECLARE @stopat DATETIME = (SELECT CAST(SESSION_CONTEXT(N'stopat') AS DATETIME))

RESTORE DATABASE [RestoreTest]
    FILEGROUP = 'PRIMARY'
    FROM DISK = 'C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\Backup\RestoreTest_Full.bak'
    WITH PARTIAL, NORECOVERY

RESTORE LOG [RestoreTest]
    FILEGROUP = 'PRIMARY'
    FROM DISK = 'C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\Backup\RestoreTest_Log.bak'
    WITH RECOVERY, STOPAT = @stopat
GO

BACKUP LOG [RestoreTest]
    TO DISK= 'C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\Backup\RestoreTest_Redo_Log.bak'
    WITH INIT
GO

-- test
SELECT [name], [state_desc]
FROM sys.master_files
WHERE [database_id] = DB_ID('RestoreTest')
GO

-- restore filegorup A
RESTORE DATABASE [RestoreTest]
    FILEGROUP = 'A'
    FROM DISK = 'C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\Backup\RestoreTest_A_Full.bak'
    WITH NORECOVERY
GO

RESTORE LOG [RestoreTest]
    FROM DISK = 'C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\Backup\RestoreTest_Redo_Log.bak'
    WITH RECOVERY

-- test
SELECT [name], [state_desc]
FROM sys.master_files
WHERE [database_id] = DB_ID('RestoreTest')
GO

Editar 1: aqui está um exemplo mínimo para brincar (não está funcionando):

------------------------------------------------------------------------
--  ONLINE PIECEMEAL RESTORE WITH POINT-IN-TIME RECOVERY
------------------------------------------------------------------------

---------------------------------
-- CREATE DB
-- log, 2 filegroups (primary + A) and one table each
---------------------------------
USE [master]
GO

CREATE DATABASE [RestoreTest]
ON PRIMARY(
    NAME = 'PRIMARY',
    FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\DATA\RestoreTest.mdf'
),
FILEGROUP A(
    NAME = 'RestoreTest_A',
    FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\DATA\RestoreTest_A.ndf'
)
LOG ON(
    NAME = 'RestoreTest_log',
    FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\DATA\RestoreTest_Log.ldf'
)
GO

USE [RestoreTest]
GO

CREATE TABLE [Table1](
    [X] INT
)
ON [PRIMARY];

CREATE TABLE [Table2](
    [X] INT
)
ON [A];
GO


---------------------------------
-- Backups
---------------------------------

-- full backups of each filegroup
BACKUP DATABASE [RestoreTest]
    FILEGROUP = 'PRIMARY'
    TO DISK = 'C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\Backup\RestoreTest_Full.bak'
    WITH INIT

BACKUP DATABASE [RestoreTest]
    FILEGROUP = 'A'
    TO DISK = 'C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\Backup\RestoreTest_A_Full.bak'
    WITH INIT
GO

-- store current time for later point-in-time recovery
WAITFOR DELAY '00:00:01'
DECLARE @now DATETIME = (SELECT GETDATE())
EXEC sp_set_session_context 'stopat', @now; 
WAITFOR DELAY '00:00:01'

--insert some data
INSERT INTO [Table1]
VALUES (1)

INSERT INTO [Table2]
VALUES (1)
GO

-- then take log backup
BACKUP LOG [RestoreTest]
    TO DISK= 'C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\Backup\RestoreTest_Log.bak'
    WITH INIT
GO


---------------------------------
-- RESTORE (point-in-time)
---------------------------------

-- drop database
USE [master]
DROP DATABASE [RestoreTest]
GO

-- restore primary filegroup
DECLARE @stopat DATETIME = (SELECT CAST(SESSION_CONTEXT(N'stopat') AS DATETIME))

RESTORE DATABASE [RestoreTest]
    FILEGROUP = 'PRIMARY'
    FROM DISK = 'C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\Backup\RestoreTest_Full.bak'
    WITH PARTIAL, NORECOVERY

RESTORE LOG [RestoreTest]
    FILEGROUP = 'PRIMARY'
    FROM DISK = 'C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\Backup\RestoreTest_Log.bak'
    WITH RECOVERY, STOPAT = @stopat
GO

-- test
SELECT [name], [state_desc]
FROM sys.master_files
WHERE [database_id] = DB_ID('RestoreTest')
GO

-- restore filegroup A (with STOPAT)
DECLARE @stopat DATETIME = (SELECT CAST(SESSION_CONTEXT(N'stopat') AS DATETIME))

RESTORE DATABASE [RestoreTest]
    FILEGROUP = 'A'
    FROM DISK = 'C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\Backup\RestoreTest_A_Full.bak'
    WITH NORECOVERY

RESTORE LOG [RestoreTest]
    FILEGROUP = 'A'
    FROM DISK = 'C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\Backup\RestoreTest_Log.bak'
    WITH RECOVERY, STOPAT = @stopat

-- test
SELECT [name], [state_desc]
FROM sys.master_files
WHERE [database_id] = DB_ID('RestoreTest')
GO


-- try again, restore filegroup A (without STOPAT)
RESTORE DATABASE [RestoreTest]
    FILEGROUP = 'A'
    FROM DISK = 'C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\Backup\RestoreTest_A_Full.bak'
    WITH NORECOVERY

RESTORE LOG [RestoreTest]
    FILEGROUP = 'A'
    FROM DISK = 'C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\Backup\RestoreTest_Log.bak'
    WITH RECOVERY

-- test
SELECT [name], [state_desc]
FROM sys.master_files
WHERE [database_id] = DB_ID('RestoreTest')
GO

USE [master]
DROP DATABASE [RestoreTest]
GO

Saída:

Processed 360 pages for database 'RestoreTest', file 'PRIMARY' on file 1.
Processed 7 pages for database 'RestoreTest', file 'RestoreTest_log' on file 1.
BACKUP DATABASE...FILE=<name> successfully processed 367 pages in 0.021 seconds (136.346 MB/sec).
Processed 8 pages for database 'RestoreTest', file 'RestoreTest_A' on file 1.
Processed 3 pages for database 'RestoreTest', file 'RestoreTest_log' on file 1.
BACKUP DATABASE...FILE=<name> successfully processed 11 pages in 0.011 seconds (7.457 MB/sec).

(1 row affected)

(1 row affected)
Processed 14 pages for database 'RestoreTest', file 'RestoreTest_log' on file 1.
BACKUP LOG successfully processed 14 pages in 0.005 seconds (21.093 MB/sec).
Processed 360 pages for database 'RestoreTest', file 'PRIMARY' on file 1.
Processed 7 pages for database 'RestoreTest', file 'RestoreTest_log' on file 1.
RESTORE DATABASE ... FILE=<name> successfully processed 367 pages in 0.020 seconds (143.164 MB/sec).
Processed 0 pages for database 'RestoreTest', file 'PRIMARY' on file 1.
Processed 14 pages for database 'RestoreTest', file 'RestoreTest_log' on file 1.
RESTORE LOG successfully processed 14 pages in 0.006 seconds (17.578 MB/sec).

(3 rows affected)
Processed 8 pages for database 'RestoreTest', file 'RestoreTest_A' on file 1.
RESTORE DATABASE ... FILE=<name> successfully processed 8 pages in 0.006 seconds (10.416 MB/sec).
Msg 4342, Level 16, State 1, Line 116
Point-in-time recovery is not possible unless the primary filegroup is part of the restore sequence. Omit the point-in-time clause or restore the primary filegroup.
Msg 3013, Level 16, State 1, Line 116
RESTORE LOG is terminating abnormally.

(3 rows affected)
Processed 8 pages for database 'RestoreTest', file 'RestoreTest_A' on file 1.
RESTORE DATABASE ... FILE=<name> successfully processed 8 pages in 0.005 seconds (12.500 MB/sec).
Msg 3116, Level 16, State 1, Line 134
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 134
RESTORE LOG is terminating abnormally.

(3 rows affected)

2 respostas

Voted

Paul White · Answer 1 · 2023-02-14T05:33:33+08:00

Once the primary filegroup is restored to a point-in-time and brought online, further piecemeal restores will recover to the same point. You don't need to specify STOPAT for those restores. It's a shame the error messages misled you.

A database is always brought to the same consistency point, regardless of the specific recovery process (piecemeal, online, whatever).

To recover a single filegroup after the primary is online in your scenario, first restore the filegroup from any suitable backup:

RESTORE DATABASE [RestoreTest]
    FILEGROUP = 'A'
    FROM DISK = '<source>';

You'll get an informational message like:

Processed 32 pages for database 'RestoreTest', file 'RestoreTest_A' on file 1.
The roll forward start point is now at log sequence number (LSN) xxx. 
Additional roll forward past LSN yyy is required to complete the restore sequence.
RESTORE DATABASE ... FILE=<name> successfully processed 32 pages in 0.012 seconds.

SQL Server can't roll forward from active log, so you need to back it up:

BACKUP LOG [RestoreTest]
    TO DISK= '<path>\Log2.bak'
    WITH INIT;

Now use that log to roll the database forward (RESTORE DATABASE works too):

RESTORE LOG RestoreTest
FROM DISK = '<path>\Log2.bak';

The target filegroup is now online and ready for use. Other filegroups are unaffected.

Repeat this process for any other RECOVERY_PENDING filegroups.

In general, you might need log from the original database and the restoring copy to roll forward. It's best to take a tail of the log backup to ensure a complete chain.

You're complicating the process a little by using a full file backup (without covering log) instead of starting with a normal full database backup containing all filegroups and enough log to recover. It's best to start with a single full backup even if you intend to use full file backups later on.

Under the full recovery model, a complete set of full file backups, together with enough log backups to span all the file backups, is the equivalent of a full database backup.

In any case, use the simpler full database backup as a base until you have your online piecemeal restore working as you want, then add the full file backup complexity if you need to.

David Browne - Microsoft · Answer 2 · 2023-02-12T08:37:34+08:00

Hum. Também não consigo obter uma restauração pontual de um grupo de arquivos secundário funcionando após uma restauração parcial (ou seja, executando RECOVERY apenas no grupo de arquivos primário).

Isso, claro, funciona. Mas o fg primário não fica online até o final da sequência de restauração. Para a maioria dos cenários práticos, isso provavelmente é bom, mas restaurações parciais pontuais são, de acordo com os documentos, possíveis.

USE [master]
DROP DATABASE [RestoreTest]
GO

-- restore filegorup A (with STOPAT)
DECLARE @stopat DATETIME = (SELECT CAST(SESSION_CONTEXT(N'stopat') AS DATETIME))


RESTORE DATABASE [RestoreTest]
    FILEGROUP = 'PRIMARY'
    FROM DISK = 'C:\Program Files\Microsoft SQL Server\MSSQL16.MSSQLSERVER\MSSQL\Backup\RestoreTest_Full.bak'
    WITH NORECOVERY

RESTORE DATABASE [RestoreTest]
    FILEGROUP = 'A'
    FROM DISK = 'C:\Program Files\Microsoft SQL Server\MSSQL16.MSSQLSERVER\MSSQL\Backup\RestoreTest_A_Full.bak'
    WITH NORECOVERY

RESTORE LOG [RestoreTest]
    FROM DISK = 'C:\Program Files\Microsoft SQL Server\MSSQL16.MSSQLSERVER\MSSQL\Backup\RestoreTest_Log.bak'
    WITH RECOVERY, STOPAT = @stopat

Durante uma restauração fragmentada online, é possível restaurar para um ponto no tempo?

conectar ao servidor PostgreSQL: FATAL: nenhuma entrada pg_hba.conf para o host

Como fazer a saída do sqlplus aparecer em uma linha?

Selecione qual tem data máxima ou data mais recente

Como faço para listar todos os esquemas no PostgreSQL?

Listar todas as colunas de uma tabela especificada

Como usar o sqlplus para se conectar a um banco de dados Oracle localizado em outro host sem modificar meu próprio tnsnames.ora

Como você mysqldump tabela (s) específica (s)?

Listar os privilégios do banco de dados usando o psql

Como inserir valores em uma tabela de uma consulta de seleção no PostgreSQL?

Como faço para listar todos os bancos de dados e tabelas usando o psql?

Durante uma restauração fragmentada online, é possível restaurar para um ponto no tempo?

2 respostas

relate perguntas