I have a SQL 2019 Standard instance and am currently fiddling around with piecemeal restore in order to be able to restore a database more quickly.
What do I try to achieve?
The database has multiple filegroups. One of them is particularly troublesome regarding the RESTORE-Process. It is a filestream filegroup which should store at some point 4 TB file attachment data. My goal is to be able to restore the main database (whithout those filestream filegroup) quickly, add a workaround for adding new files and then restore the backupped filestream data later while the system is already back up and running.
I have a hunch that probably that workaround I try is something which isn't supposed to work.
However let's go through the process first
The setup
The database originally consists of the following filegroups:
- PRIMARY: has no user tables in it….just for Query Store and SQL Server metadata
- CCC_APPLICATION: default filegroup…hosts most tables of the application
- CCC_AUDIT: filegroup for hosting of audit tables (think of it like history tables/SCD 2 tables)
- CCC_FILES: FILESTREAM filegroup for files
The system is in a pre going-live state at the moment. However it is expected for the filegroup CCC_FILES to grow up to 4 TB (technically correct: The separate drive for this filestream filegroup filling up to that capacity) while the rest of the database should stay well below 1 TB.
Please find at the bottom a repro script for it.
I think it is probably best to execute it yourself and take a look. Interesting is row 204. After having undergone phase 1 of the piecemeal restore I add a new filegroup in order to be able to add new fileattachment. This is crucial for the app to start working again.
I add some more rows to my second fileattachment table and take another log backup. Then I try to restore the original filestream filegroup and finish the piecemeal restore and fail with the following error message
The roll forward start point is now at log sequence number (LSN) 36000000173800001. Additional roll forward past LSN 36000000183500001 is required to complete the restore sequence.
Msg 3013, Level 16, State 1, Line 283
RESTORE DATABASE is terminating abnormally
My Questions
- Could you please elaborate regarding the reason for this?
- Could you think of another approach of performing this type of trickery? Would creating the different filegroup and table in advance help for example?
Here's my simplified repro script:
/**********
Create DB
**********/
CREATE DATABASE [myTestDB] ON PRIMARY
(
NAME = N'PRIMARY_1',
FILENAME = N'X:\DB_Data\myTestDB_1.mdf',
SIZE = 100 MB,
MAXSIZE = UNLIMITED,
FILEGROWTH = 100 MB
),
(
NAME = N'PRIMARY_2',
FILENAME = N'X:\DB_Data\myTestDB_2.mdf',
SIZE = 100 MB,
MAXSIZE = UNLIMITED,
FILEGROWTH = 100 MB
), FILEGROUP [CCC_APPLICATION] DEFAULT(
NAME = N'CCC_APPLICATION_1',
FILENAME = N'X:\DB_Data\myTestDB_APPLICATION_1.ndf',
SIZE = 100 MB,
MAXSIZE = UNLIMITED,
FILEGROWTH = 100 MB),
(
NAME = N'CCC_APPLICATION_2',
FILENAME = N'X:\DB_Data\myTestDB_APPLICATION_2.ndf',
SIZE = 100 MB,
MAXSIZE = UNLIMITED,
FILEGROWTH = 100 MB
), FILEGROUP [CCC_FILES] CONTAINS FILESTREAM DEFAULT(
NAME = N'CCC_FILES',
FILENAME = N'Y:\DB_Data\myTestDB_FILES',
MAXSIZE = UNLIMITED) LOG ON
(
NAME = N'CCC_log',
FILENAME = N'L:\DB_TLogs\myTestDB_log.ldf',
SIZE = 100 MB,
MAXSIZE = 2048 GB,
FILEGROWTH = 100 MB
);
GO
ALTER DATABASE [myTestDB] MODIFY FILEGROUP [CCC_APPLICATION] AUTOGROW_ALL_FILES
GO
ALTER DATABASE [myTestDB] MODIFY FILEGROUP [PRIMARY] AUTOGROW_ALL_FILES
GO
ALTER DATABASE [myTestDB] SET RECOVERY FULL
GO
USE [myTestDB]
/***************************
Create and Populate Tables
***************************/
CREATE TABLE dbo.test
(
i INT IDENTITY(1, 1),
a VARCHAR(20) DEFAULT 'Welcome!',
CONSTRAINT test_pk PRIMARY KEY CLUSTERED(i ASC)
);
GO
INSERT INTO dbo.test
DEFAULT VALUES;
GO 100
SELECT
*
FROM
dbo.test;
GO
/**************************************
Create the Table for File Attachments
**************************************/
CREATE SEQUENCE [dbo].[seq_fdcId]
AS BIGINT
START WITH 1
INCREMENT BY 1
CACHE 100;
CREATE TABLE [dbo].[MyFileAttachments1]
(
[id] [BIGINT] NOT NULL,
[filestreamId] [UNIQUEIDENTIFIER] ROWGUIDCOL NOT NULL,
[content] [VARBINARY](MAX) FILESTREAM NULL,
CONSTRAINT [PK_MyFileAttachments1] PRIMARY KEY CLUSTERED([id] ASC) ON [CCC_APPLICATION] FILESTREAM_ON [CCC_FILES],
CONSTRAINT [IX_U_MyFileAttachments1_filestream] UNIQUE NONCLUSTERED([filestreamId] ASC) ON [CCC_APPLICATION]
)
ON [CCC_APPLICATION] FILESTREAM_ON [CCC_FILES];
ALTER TABLE [dbo].[MyFileAttachments1]
ADD CONSTRAINT [DF_MyFileAttachments1_filestream] DEFAULT(NEWSEQUENTIALID()) FOR [filestreamId];
ALTER TABLE [dbo].[MyFileAttachments1]
ADD CONSTRAINT [DF_MyFileAttachments1_id] DEFAULT(NEXT VALUE FOR [dbo].[seq_fdcId]) FOR [id];
GO
/***********************************************************
Create a View on top of it for access from the application
***********************************************************/
CREATE VIEW dbo.MyFileAttachment
AS
SELECT
id,
filestreamId,
content
FROM
dbo.MyFileAttachments1;
GO
/**********************************
Insert Sample Data for Attachments
**********************************/
INSERT INTO dbo.MyFileAttachment
(
content
)
SELECT
CAST(CONVERT(VARCHAR(MAX), CRYPT_GEN_RANDOM(3), 2) AS VARBINARY(6)) /* produce some random hex like stuff */
GO 100
SELECT
*
FROM
dbo.MyFileAttachment;
/**********************
Perform a full backup
**********************/
BACKUP DATABASE myTestDB TO DISK = N'B:\DB_Backups\myTestDB_FULL_20200316_165103.bak' WITH INIT, FORMAT, CHECKSUM, COMPRESSION;
GO
/*****************
Change something
*****************/
UPDATE dbo.test
SET
a = 'A new message'
WHERE
i % 2 = 0;
INSERT INTO dbo.MyFileAttachment
(
content
)
SELECT
CAST(CONVERT(VARCHAR(MAX), CRYPT_GEN_RANDOM(3), 2) AS VARBINARY(6)); /***********************************
produce some random hex like stuff
***********************************/
GO 10
/******************************
Perform a differential backup
******************************/
BACKUP DATABASE myTestDB TO DISK = N'B:\DB_Backups\myTestDB_DIFF_20200316_165704.bak' WITH INIT, CHECKSUM, COMPRESSION, DIFFERENTIAL;
/************************
Change something again
************************/
UPDATE dbo.test
SET
a = 'A newer message'
WHERE
i % 2 = 0;
INSERT INTO dbo.MyFileAttachment
(
content
)
SELECT
CAST(CONVERT(VARCHAR(MAX), CRYPT_GEN_RANDOM(3), 2) AS VARBINARY(6)); /* produce some random hex like stuff */
GO 10
/*********************
Perform a log backup
*********************/
BACKUP LOG myTestDB TO DISK = N'B:\DB_Backups\myTestDB_LOG_20200316_171802.bak' WITH INIT, CHECKSUM, COMPRESSION;
/****************************************
Disaster Strikes: Up to piecemeal restore
****************************************/
ALTER DATABASE [myTestDB] SET OFFLINE WITH ROLLBACK IMMEDIATE;
RESTORE DATABASE [myTestDB] FILEGROUP = 'CCC_APPLICATION', FILEGROUP = 'PRIMARY' FROM DISK = 'B:\DB_Backups\myTestDB_FULL_20200316_165103.bak' WITH REPLACE, NORECOVERY, PARTIAL;
RESTORE DATABASE [myTestDB] FROM DISK = N'B:\DB_Backups\myTestDB_DIFF_20200316_165704.bak' WITH NORECOVERY;
RESTORE LOG [myTestDB] FROM DISK = N'B:\DB_Backups\myTestDB_LOG_20200316_171802.bak' WITH NORECOVERY;
RESTORE DATABASE [myTestDB] WITH RECOVERY;
/*******************************************************
Examine Filestatus --> CCC_FILES is "RECOVERY_PENDING"
*******************************************************/
USE [myTestDB];
GO
SELECT
*
FROM
sys.database_files;
/********************************************************************************************
Workaround for new files to be successfully processed even if the filegroup is still offline
a) Create a new filestream filegroup
b) Create a new table for storing file attachments
c) Alter the view to display only content from the new accessible table for the application
********************************************************************************************/
ALTER DATABASE [myTestDB] ADD FILEGROUP [CCC_FILES2] CONTAINS FILESTREAM;
ALTER DATABASE [myTestDB] ADD FILE(NAME = 'CCC_FILES2', FILENAME = 'Y:\DB_Data\myTestDB_FILES2') TO FILEGROUP [CCC_FILES2];
CREATE TABLE [dbo].MyFileAttachments2
(
[id] [BIGINT] NOT NULL,
[filestreamId] [UNIQUEIDENTIFIER] ROWGUIDCOL NOT NULL,
[content] [VARBINARY](MAX) FILESTREAM NULL,
CONSTRAINT [PK_FileDataContent2] PRIMARY KEY CLUSTERED([id] ASC) ON [CCC_APPLICATION] FILESTREAM_ON [CCC_FILES],
CONSTRAINT [IX_U_FileDataContent2_filestream] UNIQUE NONCLUSTERED([filestreamId] ASC) ON [CCC_APPLICATION]
)
ON [CCC_APPLICATION] FILESTREAM_ON [CCC_FILES2];
ALTER TABLE [dbo].MyFileAttachments2
ADD CONSTRAINT [DF_FileDataContent2_filestream] DEFAULT(NEWSEQUENTIALID()) FOR [filestreamId];
ALTER TABLE [dbo].MyFileAttachments2
ADD CONSTRAINT [DF_FileDataContent2_id] DEFAULT(NEXT VALUE FOR [dbo].[seq_fdcId]) FOR [id];
GO
ALTER VIEW [dbo].MyFileAttachment
AS
SELECT
id,
filestreamId,
content
FROM
dbo.MyFileAttachments2;
GO
/*******************
Insert some values
*******************/
INSERT INTO dbo.MyFileAttachment
(
content
)
VALUES
(
0x0AEF
);
INSERT INTO dbo.MyFileAttachments2
(
content
)
VALUES
(
0x0ABF
);
/*********************
Perform another log backup
*********************/
BACKUP LOG myTestDB TO DISK = N'B:\DB_Backups\myTestDB_LOG_20200316_173105.bak' WITH INIT, CHECKSUM, COMPRESSION;
/* Try to finish the piecemeal restore by restoring filegroup CCC_FILES */
ALTER DATABASE [myTestDB] SET OFFLINE WITH ROLLBACK IMMEDIATE; /* set DB offline for RESTORE to succeed in standard edition */
RESTORE DATABASE [myTestDB] FILEGROUP = 'CCC_FILES'
FROM DISK = 'B:\DB_Backups\myTestDB_FULL_20200316_165103.bak'
WITH REPLACE, NORECOVERY;
RESTORE DATABASE [myTestDB] FROM DISK = N'B:\DB_Backups\myTestDB_DIFF_20200316_165704.bak' WITH NORECOVERY;
RESTORE LOG [myTestDB] FROM DISK = N'B:\DB_Backups\myTestDB_LOG_20200316_171802.bak' WITH NORECOVERY;
RESTORE LOG [myTestDB] FROM DISK = N'B:\DB_Backups\myTestDB_LOG_20200316_173105.bak' WITH NORECOVERY;
RESTORE DATABASE [myTestDB] WITH RECOVERY;
/* Error Message
The roll forward start point is now at log sequence number (LSN) 36000000173800001. Additional roll forward past LSN 36000000183500001 is required to complete the restore sequence.
Msg 3013, Level 16, State 1, Line 283
RESTORE DATABASE is terminating abnormally
*/
/* Diagnose via Backup History */
SELECT
mf.physical_device_name AS fullpath,
s.first_lsn,
s.last_lsn
FROM
msdb.dbo.backupset s
INNER JOIN msdb.dbo.backupmediaset ms ON s.media_set_id = ms.media_set_id
INNER JOIN msdb.dbo.backupmediafamily mf ON ms.media_set_id = mf.media_set_id
WHERE
s.database_name = 'myTestDB'
ORDER BY
s.first_lsn
/*
fullpath first_lsn last_lsn
...myTestDB_FULL_20200316_165103.bak 36000000109800001 36000000110100001
...myTestDB_LOG_20200316_171802.bak 36000000109800001 36000000152100001
...myTestDB_DIFF_20200316_165704.bak 36000000123600001 36000000123900001
...myTestDB_DIFF_20200316_165704.bak 36000000127900001 36000000128200001
...myTestDB_DIFF_20200316_165704.bak 36000000130800001 36000000131100001
...myTestDB_DIFF_20200316_165704.bak 36000000133700001 36000000134000001
...myTestDB_DIFF_20200316_165704.bak 36000000136600001 36000000136900001
...myTestDB_DIFF_20200316_165704.bak 36000000139500001 36000000139800001
...myTestDB_DIFF_20200316_165704.bak 36000000142400001 36000000142700001
...myTestDB_DIFF_20200316_165704.bak 36000000145300001 36000000145600001
...myTestDB_DIFF_20200316_165704.bak 36000000148200001 36000000148500001
...myTestDB_DIFF_20200316_165704.bak 36000000151100001 36000000151400001
...myTestDB_LOG_20200316_173105.bak 36000000152100001 36000000173800001
*/
Output of RESTORE HEADERONLY for the backups:
Script Version 2
Sorry this question get's a bit messy. Here for the record my adjusted script with the creation of the second filestream filegroup right from the beginning.
The error "The roll forward start point….Additional roll forward…is required" persists. Thus I assume that I am doing something else wrong and the creation of the filegroup is not the issue causing the error.
/**********
Create DB
TODO: Versuchen Filegroup vorab leer anzulegen und später umzustellen....sollte doch gehn oder?
**********/
CREATE DATABASE [myTestDB] ON PRIMARY
(
NAME = N'PRIMARY_1',
FILENAME = N'X:\DB_Data\myTestDB_1.mdf',
SIZE = 100 MB,
MAXSIZE = UNLIMITED,
FILEGROWTH = 100 MB
),
(
NAME = N'PRIMARY_2',
FILENAME = N'X:\DB_Data\myTestDB_2.mdf',
SIZE = 100 MB,
MAXSIZE = UNLIMITED,
FILEGROWTH = 100 MB
), FILEGROUP [CCC_APPLICATION] DEFAULT(
NAME = N'CCC_APPLICATION_1',
FILENAME = N'X:\DB_Data\myTestDB_APPLICATION_1.ndf',
SIZE = 100 MB,
MAXSIZE = UNLIMITED,
FILEGROWTH = 100 MB),
(
NAME = N'CCC_APPLICATION_2',
FILENAME = N'X:\DB_Data\myTestDB_APPLICATION_2.ndf',
SIZE = 100 MB,
MAXSIZE = UNLIMITED,
FILEGROWTH = 100 MB
), FILEGROUP [CCC_FILES] CONTAINS FILESTREAM DEFAULT(
NAME = N'CCC_FILES',
FILENAME = N'Y:\DB_Data\myTestDB_FILES',
MAXSIZE = UNLIMITED) ,
FILEGROUP [CCC_FILES2] CONTAINS FILESTREAM (
NAME = N'CCC_FILES2',
FILENAME = N'Y:\DB_Data\myTestDB_FILES2',
MAXSIZE = UNLIMITED)
LOG ON
(
NAME = N'CCC_log',
FILENAME = N'L:\DB_TLogs\myTestDB_log.ldf',
SIZE = 100 MB,
MAXSIZE = 2048 GB,
FILEGROWTH = 100 MB
);
GO
ALTER DATABASE [myTestDB] MODIFY FILEGROUP [CCC_APPLICATION] AUTOGROW_ALL_FILES
GO
ALTER DATABASE [myTestDB] MODIFY FILEGROUP [PRIMARY] AUTOGROW_ALL_FILES
GO
ALTER DATABASE [myTestDB] SET RECOVERY FULL
GO
USE [myTestDB]
/***************************
Create and Populate Tables
***************************/
CREATE TABLE dbo.test
(
i INT IDENTITY(1, 1),
a VARCHAR(20) DEFAULT 'Welcome!',
CONSTRAINT test_pk PRIMARY KEY CLUSTERED(i ASC)
);
GO
INSERT INTO dbo.test
DEFAULT VALUES;
GO 100
SELECT
*
FROM
dbo.test;
GO
/**************************************
Create the Table for File Attachments
**************************************/
CREATE SEQUENCE [dbo].[seq_fdcId]
AS BIGINT
START WITH 1
INCREMENT BY 1
CACHE 100;
CREATE TABLE [dbo].[MyFileAttachments1]
(
[id] [BIGINT] NOT NULL,
[filestreamId] [UNIQUEIDENTIFIER] ROWGUIDCOL NOT NULL,
[content] [VARBINARY](MAX) FILESTREAM NULL,
CONSTRAINT [PK_MyFileAttachments1] PRIMARY KEY CLUSTERED([id] ASC) ON [CCC_APPLICATION] FILESTREAM_ON [CCC_FILES],
CONSTRAINT [IX_U_MyFileAttachments1_filestream] UNIQUE NONCLUSTERED([filestreamId] ASC) ON [CCC_APPLICATION]
)
ON [CCC_APPLICATION] FILESTREAM_ON [CCC_FILES];
ALTER TABLE [dbo].[MyFileAttachments1]
ADD CONSTRAINT [DF_MyFileAttachments1_filestream] DEFAULT(NEWSEQUENTIALID()) FOR [filestreamId];
ALTER TABLE [dbo].[MyFileAttachments1]
ADD CONSTRAINT [DF_MyFileAttachments1_id] DEFAULT(NEXT VALUE FOR [dbo].[seq_fdcId]) FOR [id];
GO
/* CREATE backuptable for fileattachments */
CREATE TABLE [dbo].MyFileAttachments2
(
[id] [BIGINT] NOT NULL,
[filestreamId] [UNIQUEIDENTIFIER] ROWGUIDCOL NOT NULL,
[content] [VARBINARY](MAX) FILESTREAM NULL,
CONSTRAINT [PK_FileDataContent2] PRIMARY KEY CLUSTERED([id] ASC) ON [CCC_APPLICATION] FILESTREAM_ON [CCC_FILES],
CONSTRAINT [IX_U_FileDataContent2_filestream] UNIQUE NONCLUSTERED([filestreamId] ASC) ON [CCC_APPLICATION]
)
ON [CCC_APPLICATION] FILESTREAM_ON [CCC_FILES2];
ALTER TABLE [dbo].MyFileAttachments2
ADD CONSTRAINT [DF_FileDataContent2_filestream] DEFAULT(NEWSEQUENTIALID()) FOR [filestreamId];
ALTER TABLE [dbo].MyFileAttachments2
ADD CONSTRAINT [DF_FileDataContent2_id] DEFAULT(NEXT VALUE FOR [dbo].[seq_fdcId]) FOR [id];
GO
/***********************************************************
Create a View on top of it for access from the application
***********************************************************/
CREATE VIEW dbo.MyFileAttachment
AS
SELECT
id,
filestreamId,
content
FROM
dbo.MyFileAttachments1;
GO
/**********************************
Insert Sample Data for Attachments
**********************************/
INSERT INTO dbo.MyFileAttachment
(
content
)
SELECT
CAST(CONVERT(VARCHAR(MAX), CRYPT_GEN_RANDOM(3), 2) AS VARBINARY(6)) /* produce some random hex like stuff */
GO 100
SELECT
*
FROM
dbo.MyFileAttachment;
/**********************
Perform a full backup
**********************/
BACKUP DATABASE myTestDB TO DISK = N'B:\DB_Backups\myTestDB_FULL_20200316_165103.bak' WITH INIT, FORMAT, CHECKSUM, COMPRESSION;
GO
/*****************
Change something
*****************/
UPDATE dbo.test
SET
a = 'A new message'
WHERE
i % 2 = 0;
INSERT INTO dbo.MyFileAttachment
(
content
)
SELECT
CAST(CONVERT(VARCHAR(MAX), CRYPT_GEN_RANDOM(3), 2) AS VARBINARY(6)); /***********************************
produce some random hex like stuff
***********************************/
GO 10
/******************************
Perform a differential backup
******************************/
BACKUP DATABASE myTestDB TO DISK = N'B:\DB_Backups\myTestDB_DIFF_20200316_165704.bak' WITH INIT, CHECKSUM, COMPRESSION, DIFFERENTIAL;
/************************
Change something again
************************/
UPDATE dbo.test
SET
a = 'A newer message'
WHERE
i % 2 = 0;
GO
INSERT INTO dbo.MyFileAttachment
(
content
)
SELECT
CAST(CONVERT(VARCHAR(MAX), CRYPT_GEN_RANDOM(3), 2) AS VARBINARY(6)); /* produce some random hex like stuff */
GO 10
/*********************
Perform a log backup
*********************/
BACKUP LOG myTestDB TO DISK = N'B:\DB_Backups\myTestDB_LOG_20200316_171802.bak' WITH INIT, CHECKSUM, COMPRESSION;
/****************************************
Disaster Strikes: Up to piecemeal restore
****************************************/
ALTER DATABASE [myTestDB] SET OFFLINE WITH ROLLBACK IMMEDIATE;
RESTORE DATABASE [myTestDB] FILEGROUP = 'CCC_APPLICATION', FILEGROUP = 'PRIMARY', FILEGROUP = 'CCC_FILES2' FROM DISK = 'B:\DB_Backups\myTestDB_FULL_20200316_165103.bak' WITH REPLACE, NORECOVERY, PARTIAL;
RESTORE DATABASE [myTestDB] FROM DISK = N'B:\DB_Backups\myTestDB_DIFF_20200316_165704.bak' WITH NORECOVERY;
RESTORE LOG [myTestDB] FROM DISK = N'B:\DB_Backups\myTestDB_LOG_20200316_171802.bak' WITH NORECOVERY;
RESTORE DATABASE [myTestDB] WITH RECOVERY;
/*******************************************************
Examine Filestatus --> CCC_FILES is "RECOVERY_PENDING"
*******************************************************/
USE [myTestDB];
GO
SELECT
*
FROM
sys.database_files;
/********************************************************************************************
Workaround for new files to be successfully processed even if the filegroup is still offline
a) Create a new filestream filegroup
b) Create a new table for storing file attachments
c) Alter the view to display only content from the new accessible table for the application
********************************************************************************************/
--ALTER DATABASE [myTestDB] ADD FILEGROUP [CCC_FILES2] CONTAINS FILESTREAM;
--ALTER DATABASE [myTestDB] ADD FILE(NAME = 'CCC_FILES2', FILENAME = 'Y:\DB_Data\myTestDB_FILES2') TO FILEGROUP [CCC_FILES2];
/* wurde hochkopiert
CREATE TABLE [dbo].MyFileAttachments2
(
[id] [BIGINT] NOT NULL,
[filestreamId] [UNIQUEIDENTIFIER] ROWGUIDCOL NOT NULL,
[content] [VARBINARY](MAX) FILESTREAM NULL,
CONSTRAINT [PK_FileDataContent2] PRIMARY KEY CLUSTERED([id] ASC) ON [CCC_APPLICATION] FILESTREAM_ON [CCC_FILES],
CONSTRAINT [IX_U_FileDataContent2_filestream] UNIQUE NONCLUSTERED([filestreamId] ASC) ON [CCC_APPLICATION]
)
ON [CCC_APPLICATION] FILESTREAM_ON [CCC_FILES2];
ALTER TABLE [dbo].MyFileAttachments2
ADD CONSTRAINT [DF_FileDataContent2_filestream] DEFAULT(NEWSEQUENTIALID()) FOR [filestreamId];
ALTER TABLE [dbo].MyFileAttachments2
ADD CONSTRAINT [DF_FileDataContent2_id] DEFAULT(NEXT VALUE FOR [dbo].[seq_fdcId]) FOR [id];
GO
*/
ALTER VIEW [dbo].MyFileAttachment
AS
SELECT
id,
filestreamId,
content
FROM
dbo.MyFileAttachments2;
GO
/*******************
Insert some values
*******************/
INSERT INTO dbo.MyFileAttachment
(
content
)
VALUES
(
0x0AEF
);
INSERT INTO dbo.MyFileAttachments2
(
content
)
VALUES
(
0x0ABF
);
/*********************
Perform another log backup
*********************/
BACKUP LOG myTestDB TO DISK = N'B:\DB_Backups\myTestDB_LOG_20200316_173105.bak' WITH INIT, CHECKSUM, COMPRESSION;
/* Try to finish the piecemeal restore by restoring filegroup CCC_FILES */
ALTER DATABASE [myTestDB] SET OFFLINE WITH ROLLBACK IMMEDIATE; /* set DB offline for RESTORE to succeed in standard edition */
RESTORE DATABASE [myTestDB] FILEGROUP = 'CCC_FILES'
FROM DISK = 'B:\DB_Backups\myTestDB_FULL_20200316_165103.bak'
WITH REPLACE, NORECOVERY;
RESTORE DATABASE [myTestDB] FROM DISK = N'B:\DB_Backups\myTestDB_DIFF_20200316_165704.bak' WITH NORECOVERY;
RESTORE LOG [myTestDB] FROM DISK = N'B:\DB_Backups\myTestDB_LOG_20200316_171802.bak' WITH NORECOVERY;
RESTORE LOG [myTestDB] FROM DISK = N'B:\DB_Backups\myTestDB_LOG_20200316_173105.bak' WITH NORECOVERY;
RESTORE DATABASE [myTestDB] WITH RECOVERY;
/* Error Message
The roll forward start point is now at log sequence number (LSN) 36000000173800001. Additional roll forward past LSN 36000000183500001 is required to complete the restore sequence.
Msg 3013, Level 16, State 1, Line 283
RESTORE DATABASE is terminating abnormally
*/
Script Version 3
Well, I try not to duplicate Version 2 here.
Take Version 2, execute it up to line 217 (first piecemeal restore statement). Then issue the second piecemeal restore as listed below.
No further change of the database between the two piecmeal restores AND no further log backup taken. However the infamous Msg 4303 "Additional roll forwad required" still pops up. I am completely stuck as of now. I eliminated the "SET OFFLINE" command as well but the error persists.
ALTER DATABASE [myTestDB] SET OFFLINE WITH ROLLBACK IMMEDIATE; /* set DB offline for RESTORE to succeed in standard edition */
RESTORE DATABASE [myTestDB] FILEGROUP = 'CCC_FILES'
FROM DISK = 'B:\DB_Backups\myTestDB_FULL_20200316_165103.bak'
WITH REPLACE, NORECOVERY;
RESTORE DATABASE [myTestDB] FROM DISK = N'B:\DB_Backups\myTestDB_DIFF_20200316_165704.bak' WITH NORECOVERY;
RESTORE LOG [myTestDB] FROM DISK = N'B:\DB_Backups\myTestDB_LOG_20200316_171802.bak' WITH NORECOVERY;
RESTORE DATABASE [myTestDB] WITH RECOVERY;
Best Answer
The issue was quite simple: I forgot that you can't perform a Piecemeal Restore if you are not on Enterprise Edition. My stupid self trying to do that led to that odd error messages. Going back to learning Piecemeal Restore from Jes Borland watching and the answer from @Tibor Karazi of my follow-up-question at SQL Server:Piecemeal Restore on Standard Edition 2017/2019? shed finally some light on it. End of story is that I am now living happily ever after with Enterprise Edition as the project budget allows it. I also chose to create the second filestream filegroup in advance...then there is less to do if I would really have to perform a piecemeal restore in production.