0

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 */ 
2

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

2
  • Thanks Tibor. I didn't think about it that way and neither found it documented in that form. However it makes sense and I'll just wait for my Enterprise Edition license key. – Martin Guth Mar 17 '20 at 12:38
  • Jes Borland says it herself...at minut 39:58 of the video...just forgot about it:youtube.com/watch?v=iSUGX3rdirY – Martin Guth Mar 17 '20 at 12:41

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.