sql server段落还原/部分还原,sql server文件组的备份还原,sql server文件的备份还原
2023-09-11 14:21:09 时间
【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
相关文章
- 电脑控制手机的另一选择——android vnc server
- 【转载】SAP_ECC6.0_EHP4或SAP_ECC6.0_EHP5_基于Windows_Server_2008R2_和SQL_server_2008下的安装
- [HTML 5] Transforming FormData for the Server (URLSearchParams)
- server r2 系统更新文件清理
- sql server 学习笔记
- mongoose在Windows Server 2003上不能访问问题的解决
- zabbix server 在配置客户端的时候 在zabbix server端的/etc/hosts文件的hostname 对应的ip这样可以更快的让server端发现agent端
- SQL SERVER 2005 获取表的所有索引信息以及删除和新建语句
- VIEW SERVER STATE permission was denied on object 'server', database 'master'
- SQL SERVER全面优化-------Expert for SQL Server 诊断系列
- loadrunner12.55 :HTTP Properties > Advanced设置 之 parameterize server names
- Windows Server 2008 显示隐藏文件 扩展名
- arcgis server 10.1 发布动态图层展示海量及频繁更新的数据步骤
- 使用form上传文件到application server的另一种办法
- SAP Spartacus 服务器端渲染文件 server.js 和构建后的 main.js 对比
- SAP CRM WebClient UI BSP server event
- SQL Server 为什么事务日志自动增长会降低你的性能
- SAP Server Side JavaScript解决方案
- 天龙八部TLBB系列 - 网单服务端Server/Config目录文件说明
- MYSQL导入数据报错|MYSQL导入超大文件报错|MYSQL导入大数据库报错:2006 - MySQL server has gone away
- yii phpexcel自己主动生成文件保存到server上
- Androidclient和server端数据交互的第一种方法
- Ubuntu 20.04 server 安装桌面后 ssh 自动断开