sql server段落还原/部分还原，sql server文件组的备份还原，sql server文件的备份还原

【1】创建文件组和文件

--创建文件组
USE [master]
GO
ALTER DATABASE [DWH_Optimizing] ADD FILEGROUP [DWH_Optimizing_1]
GO
ALTER DATABASE [DWH_Optimizing] ADD FILEGROUP [DWH_Optimizing_2]
GO
ALTER DATABASE [DWH_Optimizing] ADD FILEGROUP [DWH_Optimizing_3]
GO
ALTER DATABASE [DWH_Optimizing] ADD FILEGROUP [DWH_Optimizing_4]
GO

--创建文件
USE [master]
GO
ALTER DATABASE [DWH_Optimizing] ADD FILE ( NAME = N'DWH_Optimizing_11', FILENAME = N'E:\MSSQL11.BIS01\MSSQL\DATA\DWH_Optimizing_11.ndf' , SIZE = 512000KB , FILEGROWTH = 50%) TO FILEGROUP [DWH_Optimizing_1]
GO
ALTER DATABASE [DWH_Optimizing] ADD FILE ( NAME = N'DWH_Optimizing_12', FILENAME = N'E:\MSSQL11.BIS01\MSSQL\DATA\DWH_Optimizing_12.ndf' , SIZE = 512000KB , FILEGROWTH = 50%) TO FILEGROUP [DWH_Optimizing_1]
GO
ALTER DATABASE [DWH_Optimizing] ADD FILE ( NAME = N'DWH_Optimizing_21', FILENAME = N'E:\MSSQL11.BIS01\MSSQL\DATA\DWH_Optimizing_21.ndf' , SIZE = 512000KB , FILEGROWTH = 50%) TO FILEGROUP [DWH_Optimizing_2]
GO
ALTER DATABASE [DWH_Optimizing] ADD FILE ( NAME = N'DWH_Optimizing_22', FILENAME = N'E:\MSSQL11.BIS01\MSSQL\DATA\DWH_Optimizing_22.ndf' , SIZE = 512000KB , FILEGROWTH = 50%) TO FILEGROUP [DWH_Optimizing_2]
GO
ALTER DATABASE [DWH_Optimizing] ADD FILE ( NAME = N'DWH_Optimizing_31', FILENAME = N'E:\MSSQL11.BIS01\MSSQL\DATA\DWH_Optimizing_31.ndf' , SIZE = 512000KB , FILEGROWTH = 50%) TO FILEGROUP [DWH_Optimizing_3]
GO
ALTER DATABASE [DWH_Optimizing] ADD FILE ( NAME = N'DWH_Optimizing_32', FILENAME = N'E:\MSSQL11.BIS01\MSSQL\DATA\DWH_Optimizing_32.ndf' , SIZE = 512000KB , FILEGROWTH = 50%) TO FILEGROUP [DWH_Optimizing_3]
GO
ALTER DATABASE [DWH_Optimizing] ADD FILE ( NAME = N'DWH_Optimizing_41', FILENAME = N'E:\MSSQL11.BIS01\MSSQL\DATA\DWH_Optimizing_41.ndf' , SIZE = 512000KB , FILEGROWTH = 50%) TO FILEGROUP [DWH_Optimizing_4]
GO
ALTER DATABASE [DWH_Optimizing] ADD FILE ( NAME = N'DWH_Optimizing_42', FILENAME = N'E:\MSSQL11.BIS01\MSSQL\DATA\DWH_Optimizing_42.ndf' , SIZE = 512000KB , FILEGROWTH = 50%) TO FILEGROUP [DWH_Optimizing_4]
GO
ALTER DATABASE [DWH_Optimizing] MODIFY FILE ( NAME = N'DWH_Optimizing_log', MAXSIZE = UNLIMITED)
GO

【2】备份的方式

【2.1】通用备份

--文件组备份
BACKUP DATABASE [DWH_Optimizing] FILEGROUP = N'DWH_Optimizing_1' TO  DISK = N'F:\MSSQL11.BIS01\MSSQL\Backup\DWH_Optimizing.bak' WITH NOFORMAT, NOINIT,  NAME = N'DWH_Optimizing-Full Filegroup Backup', SKIP, NOREWIND, NOUNLOAD,  STATS = 10
GO

--文件备份
BACKUP DATABASE [DWH_Optimizing] FILE = N'DWH_Optimizing_11' TO  DISK = N'F:\MSSQL11.BIS01\MSSQL\Backup\DWH_Optimizing.bak' WITH NOFORMAT, NOINIT,  NAME = N'DWH_Optimizing-Full File Backup', SKIP, NOREWIND, NOUNLOAD,  STATS = 10
GO

--完整备份
BACKUP DATABASE [DWH_Optimizing] TO  DISK = N'F:\MSSQL11.BIS01\MSSQL\Backup\DWH_Optimizing.bak' WITH NOFORMAT, NOINIT,  NAME = N'DWH_Optimizing-Full Database Backup', SKIP, NOREWIND, NOUNLOAD,  STATS = 10
GO

--差异备份
BACKUP DATABASE [DWH_Optimizing] TO  DISK = N'F:\MSSQL11.BIS01\MSSQL\Backup\DWH_Optimizing.bak' WITH  DIFFERENTIAL , NOFORMAT, NOINIT,  NAME = N'DWH_Optimizing-Differential Database Backup', SKIP, NOREWIND, NOUNLOAD,  STATS = 10
GO

--事物日志备份
BACKUP LOG [DWH_Optimizing] TO  DISK = N'F:\MSSQL11.BIS01\MSSQL\Backup\DWH_Optimizing.bak' WITH NOFORMAT, NOINIT,  NAME = N'DWH_Optimizing-Transaction Log  Backup', SKIP, NOREWIND, NOUNLOAD,  STATS = 10
GO

【2.2】案例：文件组的全备与差异备

--Backup the files in the SalesGroup1 secondary filegroup.
BACKUP DATABASE Sales
   FILE = 'SGrp1Fi2',
   FILE = 'SGrp2Fi2'
   TO DISK = 'G:\SQL Server Backups\Sales\SalesGroup1.bck'
GOB. 为两个辅助文件组创建完整文件备份
下面的示例为两个辅助文件组中的所有文件创建完整文件备份。

  
--Back up the files in SalesGroup1.
BACKUP DATABASE Sales
   FILEGROUP = 'SalesGroup1',
   FILEGROUP = 'SalesGroup2'
   TO DISK = 'C:\MySQLServer\Backups\Sales\SalesFiles.bck'
GOC. 为两个辅助文件组创建差异文件备份
下面的示例为两个辅助文件组中的所有文件创建差异文件备份。

  
--Back up the files in SalesGroup1.
BACKUP DATABASE Sales
   FILEGROUP = 'SalesGroup1',
   FILEGROUP = 'SalesGroup2'
   TO DISK = 'C:\MySQLServer\Backups\Sales\SalesFiles.bck'
GO
   WITH
      DIFFERENTIAL,
GO

 

【3】还原（段落还原）

什么是段落还原？就是可以每次从备份文件中还原不同的文件组出来

--段落还原：数据库损坏范围比较大，跨多个数据文件甚至跨文件组的时候，我们不得不恢复整个数据库。
--这时如果数据库特别大，数据库恢复时间将会很长。但我们可以使用SQL Server提供的段落还原，来逐步恢复数据库。
--首先备份尾日志：
BACKUP LOG [AdventureWorks] TO DISK =N'D:\BACKUP_TEST\LOG_BACK_TAIL.trn' WITH NO_TRUNCATE ,NORECOVERY,COMPRESSION,STATS=10
--部分还原主文件组PRIMARY：
RESTORE DATABASE [AdventureWorks] FILEGROUP=N'PRIMARY' FROM DISK=N'D:\BACKUP_TEST\AD_FULL.bak' WITH PARTIAL,NORECOVERY,STATS=10
--还原副文件组MST：
RESTORE DATABASE [AdventureWorks] FILEGROUP=N'MST' FROM DISK=N'D:\BACKUP_TEST\AD_FULL.bak' WITH NORECOVERY,STATS=10
--依次还原日志：
RESTORE LOG [AdventureWorks] FROM DISK=N'D:\BACKUP_TEST\LOG_BACK_1.TRN' WITH NORECOVERY,STATS=10
RESTORE LOG [AdventureWorks] FROM DISK=N'D:\BACKUP_TEST\LOG_BACK_2.TRN' WITH NORECOVERY,STATS=10
--还原尾日志并恢复
RESTORE LOG [AdventureWorks] FROM DISK=N'D:\BACKUP_TEST\LOG_BACK_TAIL.TRN' WITH RECOVERY,STATS=10
--此时AdventureWorks数据库中位于PRIMARY和MST文件组中的文件已经可以访问。
--但是位于其他文件组如：TRN文件组中的表还不能访问。
--消息 8653，级别 16，状态 1，第 2 行
--查询处理器无法为表或视图“****”生成计划，因为该表驻留在不处于联机状态的文件组中。

--接下来还原副文件组TRN：
RESTORE DATABASE [AdventureWorks] FILEGROUP=N'TRN' FROM DISK=N'D:\BACKUP_TEST\AD_FULL.bak' WITH NORECOVERY,STATS=10
--如果数据库不是企业版，以上还原将会提示“尚未备份数据库 "AdventureWorks" 的日志尾部。”
--需要再次备份一次日志尾部，意味着还原副文件组TRN的时候整个数据库都处于正在还原状态。所以对于非企业版而言，只能离线段落还原，个人觉得意义不是很大......
--依次还原日志：
RESTORE LOG [AdventureWorks] FROM DISK=N'D:\BACKUP_TEST\LOG_BACK_1.TRN' WITH NORECOVERY,STATS=10
RESTORE LOG [AdventureWorks] FROM DISK=N'D:\BACKUP_TEST\LOG_BACK_2.TRN' WITH NORECOVERY,STATS=10
--还原尾日志并恢复
RESTORE LOG [AdventureWorks] FROM DISK=N'D:\BACKUP_TEST\LOG_BACK_TAIL.TRN' WITH RECOVERY,STATS=10
--此时位于副文件组TRN中的表已经可以访问了。
--段落还原全部完成

转自：https://www.cnblogs.com/ajiangg/p/3899472.html