备份和恢复SQL Server数据库文件组

Question

备份和恢复SQL Server数据库文件组

sql-serversql-server-2008-r2sql-server-2012backuprestore

13

我使用SQL Server并且有一个大型数据库，由多个文件组按日期进行分区。该数据库的文件组是PRIMARY、FG2010、FG2011、FG2012、FG2013和FG2014，其中FG2010、FG2011、FG2012和FG2013是只读的。

现在的备份方案是：

每个星期五凌晨2点进行一次完全备份

除了星期五，每天凌晨2点进行差异备份

我想要更改这个方案为：

一次性获取数据库的完全备份

每个星期五凌晨2点获取PRIMARY和FG2014的完全备份

除了星期五，每天凌晨2点获取PRIMARY和FG2014的差异备份

问题1：我可以使用这个方案吗？

我也有一个恢复计划。每天我都会自动将备份文件复制到另一台服务器上，并进行恢复以进行恢复测试计划，还可以将已恢复的数据库用于开发人员和测试人员。

我想要以下恢复计划方案：

恢复数据库的完全备份。

恢复PRIMARY和FG2014的最后一个完全备份。

恢复PRIMARY和FG2014的最后一个差异备份。

问题2：我可以使用这个方案来进行恢复吗？

问题3：我可以有更好的备份和恢复方案吗？

请使用TSQL查询回答我的问题。

- mehdi lotfi

我认为你可以避免每次备份和恢复只读文件。 - giammin

数据库的恢复模式是什么？我没有看到有关事务日志备份的提及，因此假设使用的是简单模式，但如果能确认一下，将不胜感激，因为恢复模式确定了可用于恢复的方法。 - John Sansom

2个回答

2

A1: 是的，您可以有这种情况，但是备份策略不能提供一种持续的方式来恢复FG2010到2013，如果需要的话，因为只存在一次性的完整数据库备份。 A2: 是的，可以通过一些调整实现。您希望实现的是所谓的分阶段还原，在方法方面所需的大部分指令可以在书籍在线示例中找到：只还原某些文件组的分阶段还原。

关键区别在于，您将在先前恢复的完整数据库备份上进行还原，而不是在经历特定文件组问题的活动数据库上进行还原。

您应该能够通过使用上述参考资料迅速创建测试用例/概念证明来满足您的需求。如果遇到困难，请将您的尝试/代码添加到问题中，这里的社区成员会提供指导。通过您的努力和社区的指导，我相信您可以回答自己的问题。授人以鱼不如授人以渔......

- John Sansom

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- dbafromthecold · Accepted Answer

问题1：是的，可以这样做。您还需要定期进行事务日志备份。

问题2：是的，这也适用于恢复策略，同样需要进行事务日志备份。

问题3：由于您正在使用备份创建开发数据库，我建议您继续使用这种方法。虽然有一些选项，比如日志传送，但我不会将它们实施到从生产环境中更新开发数据库的过程中。

我编写了一些演示脚本，您可以使用它们在本地实例上运行所提出的场景。

这些脚本将会：

创建一个测试数据库，其中包含多个文件组，其中一些是只读文件组。
从测试数据库备份中创建开发数据库。
对测试数据库进行文件组备份，并将其还原到开发数据库中。

请查看并运行这些脚本。如果您有任何问题，请告诉我。

在使用这些脚本之前，请确保您的计算机上具有以下文件路径：

C:\SQLServer\Data
C:\SQLServer\Logs
C:\SQLServer\Backups

我使用的SQL Server版本是2012 SP2 CU2开发人员版。

首先创建测试数据库：

CREATE DATABASE [FGRestoreTEST]
 ON  PRIMARY 
( NAME = N'FGRestoreTEST', FILENAME = N'C:\SQLServer\Data\FGRestoreTEST.mdf' , SIZE = 5120KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB ), 
 FILEGROUP [FG2010] 
( NAME = N'FG2010', FILENAME = N'C:\SQLServer\Data\FG2010.ndf' , SIZE = 5120KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB ), 
 FILEGROUP [FG2011] 
( NAME = N'FG2011', FILENAME = N'C:\SQLServer\Data\FG2011.ndf' , SIZE = 5120KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB ), 
 FILEGROUP [FG2012] 
( NAME = N'FG2012', FILENAME = N'C:\SQLServer\Data\FG2012.ndf' , SIZE = 5120KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB ), 
 FILEGROUP [FG2013] 
( NAME = N'FG2013', FILENAME = N'C:\SQLServer\Data\FG2013.ndf' , SIZE = 5120KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB ), 
 FILEGROUP [FG2014] 
( NAME = N'FG2014', FILENAME = N'C:\SQLServer\Data\FG2014.ndf' , SIZE = 5120KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB )
 LOG ON 
( NAME = N'FGRestoreTEST_log', FILENAME = N'C:\SQLServer\Logs\FGRestoreTEST_log.ldf' , SIZE = 2048KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)
GO

然后在每个文件组中创建表：

USE [FGRestoreTEST];
GO

CREATE TABLE [PRIMARY_TABLE]
(ID INT,
 NAME CHAR(4)) ON [PRIMARY];

CREATE TABLE [FG2010_TABLE]
(ID INT,
 NAME CHAR(4)) ON [FG2010];

 CREATE TABLE [FG2011_TABLE]
(ID INT,
 NAME CHAR(4)) ON [FG2011];

CREATE TABLE [FG2012_TABLE]
(ID INT,
 NAME CHAR(4)) ON [FG2012];

CREATE TABLE [FG2013_TABLE]
(ID INT,
 NAME CHAR(4)) ON [FG2013];

CREATE TABLE [FG2014_TABLE]
(ID INT,
 NAME CHAR(4)) ON [FG2014];
 GO

将数据（100行）插入每个表中：

INSERT INTO [PRIMARY_TABLE]
SELECT 1, 'TEST'
GO 100

INSERT INTO [FG2010_TABLE]
SELECT 1, 'TEST'
GO 100

INSERT INTO [FG2011_TABLE]
SELECT 1, 'TEST'
GO 100

INSERT INTO [FG2012_TABLE]
SELECT 1, 'TEST'
GO 100

INSERT INTO [FG2013_TABLE]
SELECT 1, 'TEST'
GO 100

INSERT INTO [FG2014_TABLE]
SELECT 1, 'TEST'
GO 100

然后将特定的文件组设置为只读：

ALTER DATABASE [FGRestoreTEST]
MODIFY FILEGROUP [FG2010] READ_ONLY;

ALTER DATABASE [FGRestoreTEST]
MODIFY FILEGROUP [FG2011] READ_ONLY;

ALTER DATABASE [FGRestoreTEST]
MODIFY FILEGROUP [FG2012] READ_ONLY;

ALTER DATABASE [FGRestoreTEST]
MODIFY FILEGROUP [FG2013] READ_ONLY;
GO

进行完整备份：

USE [master];
GO

BACKUP DATABASE [FGRestoreTEST]
TO DISK = N'C:\SQLServer\Backups\FGRestoreTEST.BAK';
GO

然后从完整备份创建一个开发数据库（该数据库将用于还原接下来将进行的文件组备份）：

RESTORE DATABASE [FGRestoreTEST_Dev]
FROM DISK = N'C:\SQLServer\Backups\FGRestoreTEST.BAK' WITH
MOVE 'FGRestoreTEST' TO 'C:\SQLServer\Data\FGRestoreTEST_Dev.mdf',
MOVE 'FG2010' TO 'C:\SQLServer\Data\FG2010_Dev.ndf',
MOVE 'FG2011' TO 'C:\SQLServer\Data\FG2011_Dev.ndf',
MOVE 'FG2012' TO 'C:\SQLServer\Data\FG2012_Dev.ndf',
MOVE 'FG2013' TO 'C:\SQLServer\Data\FG2013_Dev.ndf',
MOVE 'FG2014' TO 'C:\SQLServer\Data\FG2014_Dev.ndf',
MOVE 'FGRestoreTEST_log' TO 'C:\SQLServer\Logs\FGRestoreTEST_Dev_log.ldf',
RECOVERY,STATS=5;
GO

对每个文件组进行备份：

--http://msdn.microsoft.com/en-us/library/ms189906.aspx
BACKUP DATABASE [FGRestoreTEST]
   FILEGROUP = 'PRIMARY'
   TO DISK = 'C:\SQLServer\Backups\FGRestoreTEST_PRIMARY.bak';

BACKUP DATABASE [FGRestoreTEST]
   FILEGROUP = 'FG2010'
   TO DISK = 'C:\SQLServer\Backups\FGRestoreTEST_FG2010.bak';

BACKUP DATABASE [FGRestoreTEST]
   FILEGROUP = 'FG2011'
   TO DISK = 'C:\SQLServer\Backups\FGRestoreTEST_FG2011.bak';

BACKUP DATABASE [FGRestoreTEST]
   FILEGROUP = 'FG2012'
   TO DISK = 'C:\SQLServer\Backups\FGRestoreTEST_FG2012.bak';

BACKUP DATABASE [FGRestoreTEST]
   FILEGROUP = 'FG2013'
   TO DISK = 'C:\SQLServer\Backups\FGRestoreTEST_G2013.bak';

BACKUP DATABASE [FGRestoreTEST]
   FILEGROUP = 'FG2014'
   TO DISK = 'C:\SQLServer\Backups\FGRestoreTEST_FG2014.bak';
GO

现在我们将修改Primary和FG2014文件组中的数据：

USE [FGRestoreTEST];
GO

INSERT INTO [PRIMARY_TABLE]
SELECT 1, 'TEST'
GO 100

TRUNCATE TABLE [FG2014_TABLE];
GO

对文件组进行差异备份：

BACKUP DATABASE [FGRestoreTest]
   FILEGROUP = 'PRIMARY'
   TO DISK = 'C:\SQLServer\Backups\FGRestoreTest_PRIMARYDIFF.bak'
   WITH DIFFERENTIAL;

BACKUP DATABASE [FGRestoreTest]
   FILEGROUP = 'FG2014'
   TO DISK = 'C:\SQLServer\Backups\FGRestoreTest_FG2014DIFF.bak'
   WITH DIFFERENTIAL;
GO

再次修改数据：

USE [FGRestoreTEST];
GO

INSERT INTO [PRIMARY_TABLE]
SELECT 1, 'TEST'
GO 100

INSERT INTO [FG2014_TABLE]
SELECT 1, 'NEW'
GO 300

备份事务日志（在实际环境中，您可能会有多个日志，但出于演示目的，我只取一个）：

-

USE [master];
GO

BACKUP LOG [FGRestoreTEST]
TO DISK = 'C:\SQLServer\Backups\FGRestoreTest_LogBackup.trn';
GO

好的，现在我们可以恢复开发数据库了。首先，我们需要进行一次Tail Log备份，并将数据库置于恢复状态。注意：我们不会使用此备份！

BACKUP LOG [FGRestoreTEST_Dev]
TO DISK = 'C:\SQLServer\Backups\FGRestoreTest_TailLogBackup.trn'
WITH NORECOVERY;
GO

现在我们可以还原读写文件组的完整备份：

--http://msdn.microsoft.com/en-us/library/aa337540.aspx
--Restore primary filegroup
RESTORE DATABASE [FGRestoreTEST_Dev]
   FILEGROUP = 'PRIMARY'
   FROM DISK = 'C:\SQLServer\Backups\FGRestoreTEST_PRIMARY.bak'
   WITH NORECOVERY;
GO

--Restore FG2014 filegroup 
RESTORE DATABASE [FGRestoreTEST_Dev]
   FILEGROUP = 'FG2014'
   FROM DISK = 'C:\SQLServer\Backups\FGRestoreTEST_FG2014.bak'
   WITH NORECOVERY;
GO

接下来是增量备份：

--Restore PRIMARY differential backup
RESTORE DATABASE [FGRestoreTEST_Dev]
   FILEGROUP = 'PRIMARY'
   FROM DISK = 'C:\SQLServer\Backups\FGRestoreTest_PRIMARYDIFF.bak'
   WITH NORECOVERY;
GO

--Restore FG2014 differential backup
RESTORE DATABASE [FGRestoreTEST_Dev]
   FILEGROUP = 'FG2014'
   FROM DISK = 'C:\SQLServer\Backups\FGRestoreTest_FG2014DIFF.bak'
   WITH NORECOVERY;
GO

接下来是事务日志备份：

RESTORE LOG [FGRestoreTEST_Dev]
FROM DISK = 'C:\SQLServer\Backups\FGRestoreTest_LogBackup.trn'
WITH NORECOVERY;
GO

最后，数据库可以恢复：

RESTORE DATABASE [FGRestoreTest_DEV] WITH RECOVERY;
GO

最后一项测试，检查数据：

USE [FGRestoreTEST_Dev];
GO

SELECT COUNT(*) AS [PRIMARY_TABLE]
FROM [PRIMARY_TABLE];

SELECT COUNT(*) AS [FG2010_TABLE]
FROM [FG2010_TABLE];

SELECT COUNT(*) AS [FG2011_TABLE]
FROM [FG2011_TABLE];

SELECT COUNT(*) AS [FG2012_TABLE]
FROM [FG2012_TABLE];

SELECT COUNT(*) AS [FG2013_TABLE]
FROM [FG2013_TABLE];

SELECT COUNT(*) AS [FG2014_TABLE]
FROM [FG2014_TABLE];

SELECT TOP (1) *
FROM [FG2014_TABLE];
GO

因此，从所做的数据更改中，我们预计将在 PRIMARY和FG2014文件组中看到300个记录，在其余文件组中为100个记录，并且FG2014集合中所有名称列中的值均设置为“NEW”。