I am currently fiddling with piecemeal restore.
I have some demo code from Jes Borland from her great presentation "Minimize Data Loss with Advanced Restore Methods" (take a look at https://www.youtube.com/watch?v=iSUGX3rdirY if you are interested).
I run an excerpt of her demo code. And for very odd reasons I get an error message
Msg 3159, Level 16, State 1, Line 269 The tail of the log for the
database "OrganizeMyLego" has not been backed up. Use BACKUP LOG WITH
NORECOVERY to backup the log if it contains work you do not want to
lose. Use the WITH REPLACE or WITH STOPAT clause of the RESTORE
statement to just overwrite the contents of the log.
with the second RESTORE Command for Filegroup LargeLego.
I ran the code on different environments and noticed that on SQL 2017 Developer Edition and SQL 2014 Enterprise Edition it runs fine while on SQL 2019 Standard as well as on SQL 2017 Standard I get this error message.
Did I miss something along the road regarding the support of piecemeal restores in Standard Edition?
/*
Understanding Restore Methods to Ensure Database Recovery
Copyright Jes Borland, 2015
*/
/* Create ClientA and ClientB databases
--Database A
CREATE DATABASE ClientA;
GO
USE ClientA;
GO
CREATE TABLE Products
(ProductID INT IDENTITY PRIMARY KEY,
ProductName VARCHAR(100),
Price FLOAT);
INSERT INTO Products
VALUES ('Thingamajig', 1.00), ('Gizmo', 2.00), ('Widget', 3.00);
CREATE TABLE OrdersClientA
(OrderID INT IDENTITY PRIMARY KEY,
OrderDate DATETIME2,
ClientID INT,
ProductID INT FOREIGN KEY REFERENCES Products(ProductID),
Quantity INT);
INSERT INTO OrdersClientA
VALUES ('1/15/2014', 75, 2, 10), ('2/1/2014', 89, 1, 100), ('2/7/2014', 65, 2, 5);
--Database B
CREATE DATABASE ClientB;
GO
USE ClientB;
GO
CREATE TABLE Products
(ProductID INT IDENTITY PRIMARY KEY,
ProductName VARCHAR(100),
Price FLOAT);
INSERT INTO Products
VALUES ('Thingamajig', 1.00), ('Gizmo', 2.00), ('Widget', 3.00);
CREATE TABLE OrdersClientB
(OrderID INT IDENTITY PRIMARY KEY,
OrderDate DATETIME2,
ClientID INT,
ProductID INT FOREIGN KEY REFERENCES Products(ProductID),
Quantity INT);
INSERT INTO OrdersClientB
VALUES ('1/28/2014', 87, 3, 25), ('2/4/2014', 87, 1, 10), ('3/9/2014', 23, 2, 16);
/* Full backups */
BACKUP DATABASE ClientA TO DISK = 'B:\DB_Backups\ClientA_Full.bak';
BACKUP DATABASE ClientB TO DISK = 'B:\DB_Backups\ClientB_Full.bak';
*/
/*Create OrganizeMyLego with multiple filegroups and files */
CREATE DATABASE OrganizeMyLego
ON PRIMARY
(NAME = OrganizeMyLego_data,
FILENAME = 'X:\DB_Data\OrganizeMyLego_data.mdf'),
FILEGROUP LargeLego
(NAME = LargeLego_1,
FILENAME = 'X:\DB_Data\LargeLego_1.mdf')
LOG ON
(NAME = OrganizeMyLego_log,
FILENAME = 'X:\DB_Data\OrganizeMyLego_log.ldf');
ALTER DATABASE OrganizeMyLego
ADD FILE
(NAME = LargeLego_2,
FILENAME = 'X:\DB_Data\LargeLego_2.mdf')
TO FILEGROUP LargeLego;
ALTER DATABASE OrganizeMyLego
ADD FILEGROUP MediumLego;
ALTER DATABASE OrganizeMyLego
ADD FILE
(NAME = MediumLego_1,
FILENAME = 'X:\DB_Data\MediumLego_1.mdf'),
(NAME = MediumLego_2,
FILENAME = 'X:\DB_Data\MediumLego_2.mdf')
TO FILEGROUP MediumLego;
ALTER DATABASE OrganizeMyLego
ADD FILEGROUP SmallLego;
ALTER DATABASE OrganizeMyLego
ADD FILE
(NAME = SmallLego_1,
FILENAME = 'X:\DB_Data\SmallLego_1.mdf'),
(NAME = SmallLego_2,
FILENAME = 'X:\DB_Data\SmallLego_2.mdf')
TO FILEGROUP SmallLego;
ALTER DATABASE OrganizeMyLego
ADD FILEGROUP ArchiveLego;
ALTER DATABASE OrganizeMyLego
ADD FILE
( NAME = LegoArchive_1,
FILENAME = 'X:\DB_Data\LegoArchive_1.mdf')
TO FILEGROUP ArchiveLego;
/* Populate the database. */
/* Archive table*/
USE OrganizeMyLego;
GO
CREATE TABLE LegoSold
(ID INT IDENTITY,
DateSold DATETIME2,
SizeOfLego VARCHAR(25),
Color VARCHAR(25),
Quantity INT,
Price MONEY)
ON ArchiveLego;
INSERT INTO LegoSold
VALUES ('2014/02/17', '32x32', 'Green', 10, 5.00);
/* Large Lego */
CREATE TABLE Large32x32
(ID INT IDENTITY,
Color VARCHAR(25),
Quantity INT)
ON LargeLego;
CREATE NONCLUSTERED INDEX IX_Large32x32_Color ON dbo.Large32x32(Color)
ON MediumLego;
INSERT INTO Large32x32
VALUES ('Green', 28),
('Gray', 5),
('Orange', 7),
('Star Wars', 1);
/* Medium Lego */
CREATE TABLE Medium8x2
(ID INT IDENTITY,
Color VARCHAR(25),
Quantity INT)
ON MediumLego;
INSERT INTO Medium8x2
VALUES ('Blue', 137),
('Red', 222),
('White', 143);
/* Small Lego */
CREATE TABLE Small4x2
(ID INT IDENTITY,
Color VARCHAR(25),
Quantity INT)
ON SmallLego;
INSERT INTO Small4x2
VALUES ('Black', 98),
('Red', 178),
('Yellow', 166);
/* Full backup */
BACKUP DATABASE OrganizeMyLego TO DISK = 'B:\DB_Backups\OrganizeMyLego_Full.bak'
WITH NOINIT, COMPRESSION;
/* Restore copy of AdventureWorks 2014 for corruption */
/* RESTORE DATABASE CorruptMe
FROM DISK=N'D:\Software Downloads\Adventure Works 2014 Full Database Backup\AdventureWorks2014.bak'
WITH MOVE 'AdventureWorks2014_Data' to 'X:\DB_Data\CorruptMe_Data.mdf',
MOVE 'AdventureWorks2014_Log' to 'X:\DB_Data\CorruptMe_log.ldf';*/
ALTER DATABASE [OrganizeMyLego] SET RECOVERY FULL;
-----------------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------------------
/* Piecemeal restore */
/* Check setup */
/* Filegroups and files */
USE OrganizeMyLego;
GO
SELECT FG.name as FilegroupName,
DF.name as [FileName]
FROM sys.database_files DF
INNER JOIN sys.filegroups FG ON FG.data_space_id = DF.data_space_id;
/* Objects on filegroups */
USE OrganizeMyLego;
GO
SELECT FG.name AS FilegroupName,
OBJ.name AS ObjectName,
OBJ.type_desc AS ObjectType,
PA.index_id AS IndexID
FROM sys.filegroups FG
INNER JOIN sys.allocation_units AU ON AU.data_space_id = FG.data_space_id
INNER JOIN sys.partitions PA ON PA.partition_id = AU.container_id
INNER JOIN sys.objects OBJ ON OBJ.object_id = PA.object_id
WHERE OBJ.type_desc NOT IN ('SYSTEM_TABLE', 'INTERNAL_TABLE')
ORDER BY FG.name;
/* DB must be (FULL recovery) OR (SIMPLE recovery AND read-only) */
ALTER DATABASE OrganizeMyLego SET RECOVERY FULL;
/* Mark ArchiveLego read-only */
ALTER DATABASE OrganizeMyLego SET RESTRICTED_USER WITH ROLLBACK IMMEDIATE;
ALTER DATABASE OrganizeMyLego MODIFY FILEGROUP ArchiveLego READONLY;
ALTER DATABASE OrganizeMyLego SET MULTI_USER;
/* Full backup. */
BACKUP DATABASE OrganizeMyLego TO DISK = N'B:\DB_Backups\OrganizeMylego_Full.bak'
WITH INIT;
/* Add record. */
USE OrganizeMyLego;
INSERT INTO dbo.Large32x32
VALUES ('Blue', 2);
/* Transaction log backup. */
USE master;
GO
BACKUP LOG OrganizeMyLego TO DISK = N'B:\DB_Backups\OrganizeMyLego_Log1.trn'
WITH INIT ;
/* Add record. */
USE OrganizeMyLego;
INSERT INTO dbo.Medium8x2
VALUES ('Orange', 45);
--Back up tail of the log.
USE master;
BACKUP LOG OrganizeMyLego TO DISK = N'B:\DB_Backups\OrganizeMyLego_LogTail.trn'
WITH
--NORECOVERY,
INIT,
NO_TRUNCATE;
/* DISASTER! */
USE master;
DROP DATABASE OrganizeMyLego;
/* This is a "partial-restore sequence".
First restore must include PRIMARY filegroup.
Must include WITH PARTIAL.
All restores must include logs if in FULL. */
RESTORE DATABASE OrganizeMyLego
FILEGROUP = 'Primary'
FROM DISK = N'B:\DB_Backups\OrganizeMylego_Full.bak'
WITH PARTIAL,
NORECOVERY;
RESTORE LOG OrganizeMyLego
FROM DISK = N'B:\DB_Backups\OrganizeMyLego_Log1.trn'
WITH NORECOVERY;
RESTORE LOG OrganizeMyLego
FROM DISK = N'B:\DB_Backups\OrganizeMyLego_LogTail.trn'
WITH NORECOVERY;
RESTORE DATABASE OrganizeMyLego
WITH RECOVERY;
/* Check file state. */
SELECT [name], [state_desc]
FROM OrganizeMyLego.sys.database_files;
/* Try to select from the Large32x32 table. */
USE OrganizeMyLego;
GO
SELECT Color, Quantity
FROM dbo.Large32x32;
/* Restore LargeLego
NOTE: no "PARTIAL" here.
"Filegroup-restore sequence". */
USE master;
RESTORE DATABASE OrganizeMyLego
FILEGROUP = 'LargeLego'
FROM DISK = N'B:\DB_Backups\OrganizeMylego_Full.bak'
WITH NORECOVERY;
RESTORE LOG OrganizeMyLego
FROM DISK = N'B:\DB_Backups\OrganizeMyLego_Log1.trn'
WITH NORECOVERY;
RESTORE LOG OrganizeMyLego
FROM DISK = N'B:\DB_Backups\OrganizeMyLego_LogTail.trn'
WITH NORECOVERY;
RESTORE DATABASE OrganizeMyLego
WITH RECOVERY;
/* Fehlermeldung:
Msg 3159, Level 16, State 1, Line 269
The tail of the log for the database "OrganizeMyLego" has not been backed up. Use BACKUP LOG WITH NORECOVERY to backup the log if it contains work you do not want to lose. Use the WITH REPLACE or WITH STOPAT clause of the RESTORE statement to just overwrite the contents of the log.
Msg 3013, Level 16, State 1, Line 269
RESTORE DATABASE is terminating abnormally.
Problem von Standard Edition ?
*/
/* Try to select from the Large32x32 table again. */
USE OrganizeMyLego;
GO
SELECT Color, Quantity
FROM dbo.Large32x32;
/* Try to insert into Large32x32 table
Has both clustered and non-clustered indexes. */
INSERT INTO dbo.Large32x32
VALUES ('White', 2);
/* Try to select from Medium8x2 */
SELECT Color, Quantity
FROM dbo.Medium8x2;
/* Restore MediumLego. */
USE master;
RESTORE DATABASE OrganizeMyLego
FILEGROUP = 'MediumLego'
FROM DISK = N'B:\DB_Backups\OrganizeMylego_Full.bak'
WITH NORECOVERY;
RESTORE LOG OrganizeMyLego
FROM DISK = N'B:\DB_Backups\OrganizeMyLego_Log1.trn'
WITH NORECOVERY;
RESTORE LOG OrganizeMyLego
FROM DISK = N'B:\DB_Backups\OrganizeMyLego_LogTail.trn'
WITH NORECOVERY;
RESTORE DATABASE OrganizeMyLego
WITH RECOVERY;
/* Try to insert into Large32x32
Has both clustered and non-clustered indexes. */
USE OrganizeMyLego;
GO
INSERT INTO dbo.Large32x32
VALUES ('White', 2);
/* Try to select from Medium8x2 */
SELECT Color, Quantity
FROM dbo.Medium8x2;
/* Try to insert into Medium 8x2 */
INSERT INTO Medium8x2
VALUES ('Yellow', 55);
/* Try to select from LegoSold */
SELECT ID, DateSold, Quantity
FROM dbo.LegoSold;
/* Restore ArchiveLego.
Because this is a read-only filegroup, no log restores necessary. */
USE master;
RESTORE DATABASE OrganizeMyLego
FILEGROUP = 'ArchiveLego'
FROM DISK = N'B:\DB_Backups\OrganizeMylego_Full.bak'
WITH NORECOVERY;
RESTORE DATABASE OrganizeMyLego
WITH RECOVERY;
/* Try to select from LegoSold */
USE OrganizeMyLego;
GO
SELECT ID, DateSold, Quantity
FROM dbo.LegoSold;
/* Check file state. */
SELECT [name], [state_desc]
FROM OrganizeMyLego.sys.database_files;
USE master;
RESTORE DATABASE OrganizeMyLego
FILEGROUP = 'SmallLego'
FROM DISK = N'B:\DB_Backups\OrganizeMylego_Full.bak'
WITH NORECOVERY;
RESTORE LOG OrganizeMyLego
FROM DISK = N'B:\DB_Backups\OrganizeMyLego_Log1.trn'
WITH NORECOVERY;
RESTORE LOG OrganizeMyLego
FROM DISK = N'B:\DB_Backups\OrganizeMyLego_LogTail.trn'
WITH NORECOVERY;
RESTORE DATABASE OrganizeMyLego
WITH RECOVERY;
/* Check file state. */
SELECT [name], [state_desc]
FROM OrganizeMyLego.sys.database_files;
-------------------------------------------------------------------------
/*
Clean up
Drop databases
*/
USE master;
GO
DROP DATABASE ClientA;
DROP DATABASE ClientB;
DROP DATABASE OrganizeMyLego;
DROP DATABASE CorruptMe;
/* Delete backups from D:\SQLBackups\Advanced Restores */
Best Answer
Piecemeal restore is a usage of online restore. And online restore requires Enterprise Edition. Reference: https://docs.microsoft.com/en-us/sql/sql-server/editions-and-components-of-sql-server-version-15?view=sql-server-ver15