0

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

  1. Could you please elaborate regarding the reason for this?
  2. 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: RESTORE HEADERONLY 1 RESTORE HEADERONLY 2 RESTORE HEADERONLY 3 RESTORE HEADERONLY 4 RESTORE HEADERONLY 5

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;
3
  • What is the output of RESTORE HEADERONLY for the two log backup files? – HandyD Mar 17 '20 at 4:59
  • I have just made a change to my script: Created the filegroup CCC_FILES2 at the beginning as well as the table MyFileAttachment2. They are already in the first (full) backup. Then after performing part 1 of the piecemeal restore I did some inserts, took another log backup and ...the same error message again! Seems it doesn't link to the file group creation after the restore but to something else. – Martin Guth Mar 17 '20 at 9:23
  • Turns out that i forgot a very basic restriction: Piecemeal Restore requires Enterprise Edition. I gonna test on an Enterprise Edition instance and will be back with results. See dba.stackexchange.com/questions/262102/… for more detail – Martin Guth Mar 17 '20 at 12:57
0

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.