65.9K
CodeProject 正在变化。 阅读更多。
Home

备份 SQL Server 系统数据库

<b>Sujit Mandal</b>
starIconstarIconstarIconstarIcon
emptyStarIcon
starIcon

4.75/5 (6投票s)

2009年10月9日

CPOL

3分钟阅读

viewsIcon

43636

downloadIcon

1265

备份 SQL Server 系统数据库

目录

问题

由于 SQL Server 的正常运行依赖于存储在系统数据库中的大量信息，因此系统数据库的作用不容低估。SQL Server 2005 及更高版本中可用的系统数据库包括 Master、Resource、MSDB、MODEL、TempDB、Distribution、ReportServer 和 ReportServerTempDB。最佳实践是在服务器上的所有用户数据库成功备份后，每天创建所有系统数据库的备份。如果不能每天备份，则 DBA 至少应该在每次添加或修改服务器或数据库配置时备份所有系统数据库。这些配置包括 Service Packs、Hot Fixes、Cumulative Update、登录名更改、作业更改、操作员更改、数据库配置更改、SSIS 包更改、复制更改等...

解决方案

以下展示了系统数据库及其用途。由于这些数据仅存储在这些数据库中，因此备份这些数据库至关重要。

主要系统数据库

  • master - 包含有关登录名以及所有其他数据库的信息。
  • msdb - 存储作业、操作员、警报、备份和还原历史记录、数据库邮件信息等。
  • model - 用作所有新数据库的模型。如果您希望某些对象出现在所有新数据库中，可以在此处配置此信息。
  • tempdb - 此数据库每次 SQL Server 启动时都会创建，因此无需备份。

资源数据库

在 SQL Server 2005 中，Microsoft 引入了一个新的系统数据库，称为 Resource 数据库。Resource 数据库是一个只读的隐藏数据库，包含 SQL Server 中包含的所有系统对象。DBA 需要执行 Resource 数据库的 mssqlsystemresource.mdf 和 mssqlsystemresource.ldf 文件的基于文件的复制，因为 SQL Server 不支持备份 Resource 数据库。

在 SQL Server 2005 中，Resource 数据库位于“<drive>:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\”位置，在 SQL Server 2008 中，Resource 数据库位于“<drive>:\Program Files\Microsoft SQL Server\MSSQL10.<instance_name>\MSSQL\Binn\”位置。

Reporting Services 数据库

  • ReportServer 如果您安装了 Reporting Services，则可用。
  • ReportServerTempDB 如果您安装了 Reporting Services，则可用。

复制系统数据库

  • distribution - 当您配置复制时，此数据库将可用。

启用 XP_CMDSHELL

由于我们需要为 Resource 数据库文件执行基于文件的备份，我们需要使用 sp_configure 系统存储过程启用 xp_cmdshell 功能。可以使用以下 T-SQL 代码启用此功能

USE master
GO
sp_configure 'show advanced options'
GO
/* 0 = Disabled , 1 = Enabled */
sp_configure 'xp_cmdshell', 1
GO
RECONFIGURE WITH OVERRIDE
GO

备份系统数据库的 T-SQL 代码

数据库管理员可以使用以下 T-SQL 代码将所有系统数据库备份到 E 盘上的 SystemDatabaseBackups 文件夹。 您需要为您的系统更改此路径。

这是一个简单的脚本，包含要备份的每个数据库的代码

USE master
GO
SELECT GETDATE() AS 'System Database Backup Start Time'
GO
/* Backup Distribution Database */ 
BACKUP DATABASE Distribution 
TO DISK = 'E:\SystemDatabaseBackups\Distribution.BAK' 
WITH INIT
GO
/* Backup ReportServer Database */ 
BACKUP DATABASE ReportServer 
TO DISK = 'E:\SystemDatabaseBackups\ReportServer.BAK' 
WITH INIT
GO
/* Backup ReportServerTempDB Database */ 
BACKUP DATABASE ReportServerTempDB 
TO DISK = 'E:\SystemDatabaseBackups\ReportServerTempDB.BAK' 
WITH INIT
GO
/* Backup Master Model */ 
BACKUP DATABASE Model 
TO DISK = 'E:\SystemDatabaseBackups\Model.BAK' 
WITH INIT
GO
/* Backup Master Database */ 
BACKUP DATABASE Master 
TO DISK = 'E:\SystemDatabaseBackups\Master.BAK' 
WITH INIT
GO
/* Backup Master MSDB */ 
BACKUP DATABASE MSDB 
TO DISK = 'E:\SystemDatabaseBackups\MSDB.BAK' 
WITH INIT
GO
/* Copy Resource Database Files Using XP_CMDSHELL */ 
EXEC xp_cmdshell 'COPY /Y "D:\Program Files\Microsoft SQL Server\MSSQL10.
SQL2008\MSSQL\Binn\mssqlsystemresource.mdf" "E:\SystemDatabaseBackups"' 
GO
EXEC xp_cmdshell 'COPY /Y "D:\Program Files\Microsoft SQL Server\MSSQL10.
SQL2008\MSSQL\Binn\mssqlsystemresource.ldf" "E:\SystemDatabaseBackups"'
GO
SELECT GETDATE() AS 'System Database Backup End Time'
GO

以下是这些数据库和文件在备份后的列表

如果您有所有系统和用户数据库的适当备份，那么您可以在发生系统故障（例如硬盘损坏）时还原和恢复 SQL Server 系统。

关注点

  • 构建有关系统数据库的知识对于更好地理解 SQL Server 的内部工作方式非常重要。
  • 虽然系统数据库是普通数据库，但在使用它们时要小心，因为更改可能会对整个实例产生潜在影响。

历史

  • 2009 年 10 月 9 日：初始发布
© . All rights reserved.