主页 / dba / 问题 / 262102
Accepted
Martin Guth
Asked: 2020-03-18 03:11:54 +0800 CST

SQL Server：Standard Edition 2017/2019 上的分段还原？

  • 772

我目前正在摆弄零碎的恢复。我有一些来自 Jes Borland 的演示代码，来自她的精彩演讲“使用高级恢复方法最小化数据丢失”（如果您有兴趣，请查看https://www.youtube.com/watch?v=iSUGX3rdirY ）。

我运行了她演示代码的摘录。由于非常奇怪的原因，我收到一条错误消息

消息 3159，级别 16，状态 1，第 269 行 数据库“OrganizeMyLego”的日志尾部尚未备份。如果日志包含您不想丢失的工作，请使用 BACKUP LOG WITH NORECOVERY 备份日志。使用 RESTORE 语句的 WITH REPLACE 或 WITH STOPAT 子句仅覆盖日志的内容。

使用 Filegroup LargeLego 的第二个 RESTORE 命令。

我在不同的环境中运行代码，并注意到在 SQL 2017 开发人员版和 SQL 2014 企业版上运行良好，而在 SQL 2019 标准版和 SQL 2017 标准版上我收到此错误消息。

我是否错过了有关标准版中零碎恢复的支持？

/* 
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 */
sql-server restore

1 个回答

  1. Best Answer

    零碎恢复是在线恢复的一种用法。而在线恢复需要企业版。参考：https ://learn.microsoft.com/en-us/sql/sql-server/editions-and-components-of-sql-server-version-15?view=sql-server-ver15

    • 2

相关问题