备份和还原Server数据库文件组

Question

我使用sql server，并拥有一个按日期在多个文件组中分区的大型数据库。数据库文件组是PRIMARY, FG2010, FG2011, FG2012, FG2013, and FG2014，FG2010、FG2011、FG2012和FG2013是只读的。

现在，备份场景是：

每个星期五凌晨2点得到一个完整的备份。 每周的每一天，除了星期五，在凌晨2点得到一个不同的备份。

我想将此场景更改为：

获得数据库的完整备份(一次) 在每个星期五凌晨2:00获得主站和FG2014的完整备份 除星期五凌晨2:00外，每天对主站和FG2014进行差异备份

问题1:能给我这个场景吗？

我也有恢复计划。每天我都会通过作业将备份文件自动复制到另一台服务器，然后将其还原，以便有恢复测试计划，并将已恢复的数据库用于开发人员和测试人员。

我希望有以下恢复计划的场景：

恢复数据库的完全备份。 恢复主备份和FG2014的最后一个完整备份。 恢复主备份和FG2014的最后一次差异备份。

问题2: --我可以有这个场景来恢复计划吗？

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

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

Answer 1

Q1。可以，停那儿吧。您还需要定期进行事务日志备份。

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

Q3。当您使用备份创建开发数据库时，我将坚持这样做。有一些选项，如日志传送，但我不会实现这些选项，以使开发数据库从生产中得到更新。

我已经编写了一些演示脚本，您可以使用这些脚本来运行您在本地实例上建议的场景。

剧本将：-

1. 创建一个测试数据库，包含多个文件组，其中一些文件组是只读的。
2. 从测试数据库的备份创建开发数据库。
3. 对测试数据库进行文件组备份并通过开发数据库进行还原

请看一看，并运行脚本。如果你有任何问题，请告诉我。

在使用脚本之前，只需确保计算机上有以下文件：

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

我使用的Server版本是2012 SP2 CU2 Developer Edition。

首先创建测试数据库：-

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

现在我们将修改主文件组和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

好的，现在我们可以恢复开发数据库了。首先，我们进行尾日志备份，将数据库恢复。注意：-我们不会使用这个备份！

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

因此，从所做的数据更改中，我们可以看到主& FG2014文件组中有300个记录，其余文件组中有100个，而FG2014列中的所有值都是'NEW‘。

Answer 2

A1:是的--但是，如果需要的话，备份策略不会提供持续到2013年恢复FG2010的方法，因为只有一次完整的数据库备份。

A2:是的，只是做了一些调整。您希望实现的是分段恢复，所需方法的大部分指令可以在联机丛书示例：只对某些文件组进行分段恢复中找到。

关键的区别是，您将通过先前还原的完整数据库备份来恢复，而不是使用特定文件组遇到问题的活动数据库。

您应该能够轻松地为您的需要创建一个测试用例/概念证明，使用上面的引用。如果你陷入困境，将你的尝试/代码添加到问题中，这里的好社区伙伴将提供指导。只要你的一点努力和社区的一些方向，我相信你可以回答你自己的问题。教一个人钓鱼什么的.