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

March 17, 2020

我目前正在擺弄零碎的恢復。我有一些來自 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 */

零碎恢復是線上恢復的一種用法。而線上恢復需要企業版。參考：https ://docs.microsoft.com/en-us/sql/sql-server/editions-and-components-of-sql-server-version-15?view=sql-server-ver15

引用自：https://dba.stackexchange.com/questions/262102

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

相關問答

將數據庫從 SQL Server 2019 複製到 2017

設備上的媒體系列為只讀文件組還原而形成錯誤

SQL Server 上的備份和還原（遷移）中缺少表

AlwaysOn 可用性組和數據庫計數限制

是否可以在 SQL Server Windows 上還原 SQL Server Linux 備份

為什麼對我的數據庫進行完整備份還原時出現錯誤“RESTORE 只能在還原完整備份時創建數據庫…”？