select GETDATE() select @@VERSION go create database easthome go use easthome go use test go create table h3(id int) on hr go --查看数据库test的相关信息 sp_helpdb test go sp_help h3 go select * from master.sys.syslogins go create table easthome.dbo.t2(id int) go --查看系统用户 select * from easthome.sys.sysusers go --生成完全备份数据信息和日志信息 backup database easthome to disk='d:\bk\et.bak' go backup log easthome to disk='d:\bk\et_log_1.bak' go backup log easthome to disk='d:\bk\et_log_2.bak' go --验证备份信息 --使用该语句可以查看备份文件的版本信息、是否进行过压缩 restore headeronly from disk='d:\bk\et.bak' go --数据文件和日志文件的信息查看SIZE大小 restore filelistonly from disk='d:\bk\et.bak' go --完全备份（压缩） --Checksum:一边做备份一边验证数据库当中是否有损害的数据页， --有损坏页就不再进行备份 backup database easthome to disk='d:\bk\east.bak' with checksum,compression go --察看数据库是否有损坏的数据页，标示为红色（indid） --indid值为0，1数据页坏了 解决办法：恢复数据 --indid值为〉=2 代表索引坏了 解决办法：重建索引即可 --建议按月查看，但是不能在业务高峰时间查看，会给数据添加锁 dbcc checkdb(easthome) go --执行有个友好提示，提示用户备份当前状态的信息 --用户可以先备份一个日志信息，然后再做还 --如果不加recovery选项，默认是recovery(不保留那半个事务） backup log easthome to disk='d:\bk\et_log_4.bak' go restore database easthome from disk='d:\bk\et.bak' go --执行log恢复保证你的数据恢复是norecovery --如果使用norecovery，数据库刷新状态为restoring（不可读也不可写） --这样数据和日志才能衔接上 restore database easthome from disk='d:\bk\et.bak' with norecovery go restore log easthome from disk='d:\bk\et_log_1.bak' go --一步步地还原必须添加with norecovery选项，除非你不想要某个 --备份信息，在还原之前使用with recovery选项 restore log easthome from disk='d:\bk\et_log_1.bak' with norecovery go /* 如果数据库坏掉，应该首先对数据库的日志文件进行备份，然后再进行数据 恢复，这样才能将数据恢复到当前点。 如果数据文件坏掉，但是日志文件没有问题，可以将日志文件放到另外机器 做恢复，将数据恢复。 SQLServer 不能直接读当前日志文件恢复数据，只能读取备份的日志文件恢 复数据，所以要放到其他机器折腾一下 */ --备份msdb库 backup database msdb to disk='d:\bk\msdb.bak' go --启动sqlserver agent服务,处理自动化作业 --需要停止服务，在服务列表中停止，软件不能停止 --只有停止代理服务（sqlserver agent）后才能恢复msdb restore database msdb from disk='d:\bk\msdb.bak' go --备份master backup database master to disk='d:\bk\master.bak' go /* 只能在单用户模式下才能还原master 怎么启动单用户模式方法 1:首先停止SQLServer服务 2:在启动参数添加-m 3:不能点击确定，需要点击启动（注意） */ /* 使用抢连接方式 启动单用户模式后，我们重新登陆SQLServer提示错误 原因是连接被其他（reporting service)抢到了 打开CMD输入sqlcmd -E（-E 以操作系统用户连接） 上面的3点击启动，同时赶紧在DOS界面回车（必须快才能抢到连接，多次尝试） 进行单用户模式后才能进行master库的恢复 */ /* --------------------------------------------------------------------- **********恢复master数据库总结步骤*********************************** --------------------------------------------------------------------- 1：重建系统数据库 不同数据库版本的命令是不一样的 2：停止SQLServer服务 3：使用-m启动单用户模式 4：恢复master数据库 5：手动正常启动SQL Server服务 6：恢复MSDB数据库（尽管你的MSDB数据库是空的也必须作备份恢复） 保持Master和MSDB配套，不然以后实行自动化作业是个隐患 7：手动正常启动SQL Server代理服务（Agent） */ restore database master from disk='d:\bk\master.bak' go /* --------------------------------------------------------------------- **************维护计划*********************************************** --------------------------------------------------------------------- 建议用户分门别类的进行维护计划 1：用户数据库备份 2：系统数据库备份 3：日志文件的备份 4：删除以前的备份文件(清除MSDB记录的备份信息）建议删除6个月以前的 --清除历史任务 --清除维护任务 使用之前需要启动SQL Server Agent代理服务 如果报错：有CLSID等，把SQLServer Management Studio关闭重新打开即可 执行多次，就产生多个文件 */ /* --------------------------------------------------------------------- **************自动化作业********************************************* --------------------------------------------------------------------- 可以将自动化作业的运行结果、查询结果等以发邮件的形式发送 发邮件需要注意;SMTP 注意防火墙和杀毒软件 配置左边树状Database Mail时需要停止 SQL Server Agent代理服务 配置时会提示启动ServiceBroker，点击Yes，系统会假死，必须关闭 SQL Server Management Studio 窗口，而不能关闭配置邮件的界面 如果有问题，多试几次或者重启SQL Server服务 配置完毕之后使用Database Mail需要启动SQL Server Agent代理服务 这个是配置邮件的发件人 在SQL Server Agent的Operators配置收件人 */ /* --------------------------------------------------------------------- ***************创建一个Job******************************************* --------------------------------------------------------------------- a:设置一个Job的名称 b:选择Steps 1:backup--备份数据库 2:copy--拷贝备份好的文件到另外地方 将自己的SQLSErver文件拷贝到其他机器使用代理服务的启动帐户 将别的文件拷贝到自己SQlServer使用SQLServer的启动帐户 Advanced可以确定流程控制 c:设置时间 d:发送信息给收件人 该步骤必须选择Agent右键属性-Alert System-Enable Mail profile 作业失败，View History，哪个有红色就是哪个失败了 */ /* --------------------------------------------------------------------- ******************Replication**************************************** --------------------------------------------------------------------- 1：数据同步 2：负载均衡 3：HA 该功能是基于表的，不包括Database外面的视图、存储过程、用户等对象 该功能包含 1：快照复制 复制表里面当前的状态 一次性的 2：事务复制 增量的，原有变化目标会跟着变化， 单向的（源变目标变，目标变对源无影响） 表里面必须有主键或者唯一值约束 3：合并复制 增量的 同上 多向的（一个节点改，所有参与合并复制的都需要改） 会自动给表添加一列-------客户端程序需要注意 */ /* --------------------------------------------------------------------- ****************High Availablility*********************************** --------------------------------------------------------------------- 1：log shipping 日志传授 原理：主服务器不停的往一个目录（需要共享）作备份，然后将这个目录 文件拷贝到另外一个目录，另外一个目录为备用服务器作恢复（norecovery) 三个周期 两个目录 一个共享 替代日志文件备份策略，不能同时执行 特点：数据非实时、对主机无影响 适用于报表服务器 2：Mirror（SQL Server2005后） 特点：实时/非实时的灾备中心、对主机性能有影响 想读取mirror的数据，需要创建mirror快照 企业面临问题; 需要修改客户端连接字符串，只支持ADO 如果主服务器宕机了，mirror服务器需要自动起来，需要借助于 Witness Server（见证服务器），mirror如果不自动起来，手动 起来，就不需要witness server 保护机器、存储 3：Cluster 保护机器，不保护存储 Oracle RAC模式在SQL Server 2008 R2 的并行计算版本/云计算版本 能够实现，企业版只能做（双机热备），不能做类似RAC的模式 */ /* --------------------------------------------------------------------- ***************监控SQL Server**************************************** --------------------------------------------------------------------- 1：管理工具-事件查看器 Q：以前可以用，现在用不了了 A：我们需要知道用户作了什么变更 变更包括（硬件变化、软件变化（打补丁）、升级变化、客户端程序变化等） 经验： a:服务器做变更（打补丁）之前一定要做测试 b:服务器作任何变更，一定要进行记录 2：SQL Server-Management-SQL Server Logs 记录SQL Server运行综合的状况，该日志与时间查看器有重叠 C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Log 查看该文件夹里面的log..文件，双击查看，可以对表结构的增删改、数据库备份等作查看 3：管理工具-性能监视器 锁的问题，一般都是客户端代码的问题 选择性能监视器-右边(报告)-添加计数器-LogicalDisk(下面的disk read time等信息不用看，不正确） 需要查看Avg.Disk Read Queue Length和Avg.Disk Write Queue Length，选择所有实例，点击添加 相关的值不超过10都算正常， 读队列最容易出问题 a:消除页碎片 b:索引 c:调整SQL语句 不能修改代码的在后台建立索引视图、分区表、计算列 添加-Memory-Available MBytes 估值在100MB以上 添加-Memory-Pages/sec(物理内存和虚拟内存的交换） 选择用户连接-属性-Memory-Maximum Server memory(设置）-避免SQLServer抢操作系统内存 --Use AWE to allocate memory 给32Bit的SQL Server的企业版来使用 添加SQLServer：Memory Manger- Target Server Memory：Windows给了SQLServer多少内存 Total Server Memory：SQLServer使用了多少内存 32Bit的SQL Server软件安装在64Bit的操作系统是不能看到SQL Server开头的添加计数器 SQLServer:SQL Statistics-SQL Compilations/sec */ /* --------------------------------------------------------------------- ***************************锁**************************************** --------------------------------------------------------------------- 1：阻塞：顺序等待 2：死锁：交叉等待 测试阻塞 */ use easthome go create table w5(id int) go insert into w5 values(1) insert into w5 values(2) go begin tran update w5 set id=10 where id=1 ---排他锁需要等事务结束才释放(更新时添加） ---共享锁需要等程序结束才释放（查询时添加） go --使用sp_lock可以监控加锁的情况 X（排他锁） S（共享锁） sp_lock --查看DBID（库名）和ObjID（表名） --TAB（表）PAG（页）RID（行） --1：79：0（第一个文件上面的第79页，“：0” 是这个页的第一行） go select DB_NAME(7) select OBJECT_NAME(2121058592) go --执行下面语句，检测，因为上面的SQL语句事务未提交，所以执行下面语句无相应 --执行Sp_lock有Wait状态，这就模拟了一个阻塞的现象 select * from w5 where id=1 go --数据连接-右键-Activity Monitor-Processes-HEB1（阻塞的人） --使用sp_who查看blk是非0值，说明有阻塞现象 sp_who --阻塞现象修改1：程序 2：索引 --如果表内有并发修改，小表也需要创建索引，不是为了查询性能，提高并发性 --使用DMV来查询相关信息 /* --------------------------------------------------------------------- ***************************SQL Server Profiler*********************** --------------------------------------------------------------------- 打开SQL Server Profiler-New Trace，Trace属性-Events Selection 一般选择 RPC:Completed SQL:BatchCompleted Show All Event-Locks-Deadlock graph Show All columns-添加DatabaseName和HostName */ /* --------------------------------------------------------------------- ********************Troubleshooting and Performance Tuning*********** --------------------------------------------------------------------- Database Engine Tuning Advisor File添加[SQL Server Profiler跟踪的结果文件] Start Analysis-Apply Recommendations仅供参考即可 Resource Governor(SQL Server 2008之后） 限制某个用户、某个数据库最多可以使用CPU、内存等资源 编程实现 Data Collector */