MSSQL 备份及相关核心系统存储过程

谁践踏了优雅 2022-10-01 03:59 160阅读 0赞

\------下面是从不同的高手博客中摘取出来的。本来打算自己写一片来着。发现 高手们都写好了。所以这里就拿来直接用了。

\--备份  
declare  @sql  varchar(8000)   
set  @sql='backup  database  mis    to  disk=''d:\\databack\\mis\\mis'   
\+rtrim(convert(varchar,getdate(),112))+'.bak'''   
exec(@sql)

\--删除15天前备份文件  
set  @sql='del  d:\\databack\\mis\\mis'   
\+rtrim(convert(varchar,getdate()-15,112))+'.bak'''   
exec  master..xp\_cmdshell  @sql

\--清除日志  
dump transaction databasename with no\_log  
backup log databasename with no\_log

\--查看数据库里用户和进程的信息  
sp\_who

\--查看SQL Server数据库里的活动用户和进程的信息  
sp\_who 'active'

\--查看SQL Server数据库里的锁的情况  
sp\_lock  
\--进程号--50是SQL Server系统内部用的,进程号大于的才是用户的连接进程.  
\--spid是进程编号,dbid是数据库编号,objid是数据对象编号

\--收缩数据库日志文件的方法  
\--收缩简单恢复模式数据库日志，收缩后@database\_name\_log的大小单位为M  
backup log @database\_name with no\_log  
dbcc shrinkfile (@database\_name\_log, 5)

\--分析SQL Server SQL 语句的方法:  
set statistics time \{on | off\}  
set statistics io \{on | off\}

\--图形方式显示查询执行计划  
\--在查询分析器->查询->显示估计的评估计划(D)-Ctrl-L 或者点击工具栏里的图形

\--文本方式显示查询执行计划  
set showplan\_all \{on | off\}  
set showplan\_text \{ on | off \}  
set statistics profile \{ on | off \}

/\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*完整备份作业\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*/  
\--完整备份,每周一次  
USE Master  
GO  
declare @str varchar(100)  
set @str='D:\\DBtext\\jgj\\DBABak\\FullBak'+replace(replace(replace(convert(varchar,getdate(),20),'-',''),' ',''),':','')+'.bak'  
BACKUP DATABASE \[demo\] TO [DISK=@str][DISK_str]  
WITH RETAINDAYS=15,NOFORMAT,NOINIT,  
NAME=N'Demo完整备份',SKIP,NOREWIND,  
NOUNLOAD,STATS=10  
GO

/\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*差异备份作业\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*/  
\--截断日志  
USE Master  
GO  
BACKUP LOG Demo  WITH NO\_LOG  
GO  
\--收缩日志文件  
USE Demo  
GO  
DBCC SHRINKFILE (N'Demo\_log',0,TRUNCATEONLY)  
GO  
\--差异备份,每天一次  
USE Master  
GO  
declare @str varchar(100)  
set @str='D:\\DBtext\\jgj\\DBABak\\DiffBak'+replace(replace(replace(convert(varchar,getdate(),20),'-',''),' ',''),':','')+'.diff'  
BACKUP DATABASE \[Demo\] TO [DISK=@str][DISK_str]  
WITH DIFFERENTIAL,RETAINDAYS=8,NOFORMAT,NOINIT,  
NAME=N'Demo差异备份',SKIP,NOREWIND,  
NOUNLOAD,STATS=10  
GO

/\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*日志备份作业\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*/  
\--日志备份,每小时一次  
USE Demo  
GO  
declare @str varchar(100)  
set @str='D:\\DBtext\\jgj\\DBABak\\logbak'+replace(replace(replace(convert(varchar,getdate(),20),'-',''),' ',''),':','')+'.trn'  
BACKUP LOG \[Demo\] TO [DISK=@str][DISK_str]  
WITH RETAINDAYS=3,NOFORMAT,NOINIT,  
NAME=N'Demo日志备份',SKIP,NOREWIND,  
NOUNLOAD,STATS=10  
GO

\--删除过期的备份文件,每天两次  
declare @str varchar(100),@dir varchar(100),@fileName varchar(30)  
set @dir='del D:\\DBtext\\jgj\\DBABak\\'  
set @filename=left(replace(replace(replace(convert(varchar,getdate()-15,20),'-',''),' ',''),':',''),8)  
set @str=@dir+'fullbak'+@filename+'\*.bak'  
exec xp\_cmdshell @str  
set @filename=left(replace(replace(replace(convert(varchar,getdate()-8,20),'-',''),' ',''),':',''),8)  
set @str=@dir+'diffbak'+@filename+'\*.diff'  
exec xp\_cmdshell @str  
set @filename=left(replace(replace(replace(convert(varchar,getdate()-8,20),'-',''),' ',''),':',''),8)  
set @str=@dir+'logbak'+@filename+'\*.trn'  
exec xp\_cmdshell @str  
\--最好备份日志，以后可通过日志恢复数据。。。  
以下为日志处理方法  
一般不建议做第4,6两步  
第4步不安全,有可能损坏数据库或丢失数据  
第6步如果日志达到上限,则以后的数据库处理会失败,在清理日志后才能恢复.  
\--\*/

\--下面的所有库名都指你要处理的数据库的库名

1.清空日志  
DUMP TRANSACTION 库名 WITH NO\_LOG

2.截断事务日志：  
BACKUP LOG 库名 WITH NO\_LOG

3.收缩数据库文件(如果不压缩,数据库的文件不会减小  
企业管理器--右键你要压缩的数据库--所有任务--收缩数据库--收缩文件  
\--选择日志文件--在收缩方式里选择收缩至XXM,这里会给出一个允许收缩到的最小M数,直接输入这个数,确定就可以了  
\--选择数据文件--在收缩方式里选择收缩至XXM,这里会给出一个允许收缩到的最小M数,直接输入这个数,确定就可以了

也可以用SQL语句来完成  
\--收缩数据库  
DBCC SHRINKDATABASE(库名)

\--收缩指定数据文件,1是文件号,可以通过这个语句查询到:select \* from sysfiles  
DBCC SHRINKFILE(1)

4.为了最大化的缩小日志文件(如果是sql 7.0,这步只能在查询分析器中进行)  
a.分离数据库:  
企业管理器--服务器--数据库--右键--分离数据库

b.在我的电脑中删除LOG文件

c.附加数据库:  
企业管理器--服务器--数据库--右键--附加数据库

此法将生成新的LOG，大小只有500多K

或用代码：  
下面的示例分离 pubs，然后将 pubs 中的一个文件附加到当前服务器。

a.分离  
EXEC sp\_detach\_db @dbname = '库名'

b.删除日志文件

c.再附加  
EXEC sp\_attach\_single\_file\_db @dbname = '库名',  
@physname = 'c:\\Program Files\\Microsoft SQL Server\\MSSQL\\Data\\库名.mdf'

5.为了以后能自动收缩,做如下设置:  
企业管理器--服务器--右键数据库--属性--选项--选择"自动收缩"

\--SQL语句设置方式:  
EXEC sp\_dboption '库名', 'autoshrink', 'TRUE'

6.如果想以后不让它日志增长得太大  
企业管理器--服务器--右键数据库--属性--事务日志  
\--将文件增长限制为xM(x是你允许的最大数据文件大小)

\--SQL语句的设置方式:  
alter database 库名 modify file(name=逻辑文件名,maxsize=20)

\---查询索引操作的信息  
select \* from sys.dm\_db\_index\_usage\_stats

\--查看表的索引信息  
exec sp\_helpindex tb

\--结合sys.indexes和sys.index\_columns,sys.objects,sys.columns查询索引所属的表或视图的信息  
select  
  o.name as 表名,  
  i.name as 索引名,  
  c.name as 列名,  
  i.type\_desc as 类型描述,  
  is\_primary\_key as 主键约束,  
  is\_unique\_constraint as 唯一约束,  
  is\_disabled as 禁用  
from  
  sys.objects o  
inner join  
  sys.indexes i  
on  
  i.object\_id=o.object\_id  
inner join  
  sys.index\_columns ic  
on  
  ic.index\_id=i.index\_id and ic.object\_id=i.object\_id  
inner join  
  sys.columns c  
on  
  ic.column\_id=c.column\_id and ic.object\_id=c.object\_id  
go

\--查询索引的键和列信息  
select  
  o.name as 表名,  
  i.name as 索引名,  
  c.name as 字段编号,  
from  
  sysindexes i inner join sysobjects o  
on  
  i.id=o.id  
inner join  
  sysindexkeys k  
on  
  o.id=k.id and i.indid=k.indid  
inner join  
  syscolumns c  
on  
  c.id=i.id and k.colid=c.colid  
where  
  o.name='表名'  
\--查看统计信息的明细信息  
dbcc show\_statistics

\--查看索引自动创建的统计信息  
exec sp\_autostats '对象名'

\--关闭自动生成统计信息的数据库选项  
alter datebase 数据库名 set auto\_create\_statistics off

\--创建统计信息  
create statistics 统计信息名称 on 表名(列名)  
\[with  
 \[\[fullscan  
   sample number\{percent|rows\}\]  
 \[norecompute\]  
\]  
go  
解释一下上面的参数：  
fullscan:指定对表或视图中所有的行收集统计信息  
sample number\{percent|rows\}:指定随机抽样应读取的数据行数或者百分比 sample选项不能与fullscan选项同时使用  
norecompute:指定数据库引擎不自动重新计算统计信息

\--计算随机抽样统计信息  
create statistics 统计信息名称 on 表名(列名)  
with sample 5 percent---创建统计信息，按5%计算随机抽样统计信息  
go

\--创建统计信息  
exec sp\_createstats--参数自己去查下帮助，在这里不一一列举

\--修改统计信息  
update statistics 表名|视图名  
    索引名|统计信息名,索引名|统计信息名,.....  
\[with  
 \[\[fullscan  
   sample number\{percent|rows\}\]  
 \[norecompute\]  
\]  
\---参数与create statistics 语句相似，下面介绍几种常用应用  
1.更新指定表的所有统计信息  
update statistics 表名

2.更新指定表的单个索引的统计信息  
update statistics 表名 索引名

3.对表进行全面扫描，更新统计信息  
update statistics 表名(列名) with fullscan  
\---查询索引操作的信息  
select \* from sys.dm\_db\_index\_usage\_stats

\--查询指定表的统计信息(sys.stats和sysobjects联合查询)  
select  
  o.name,--表名  
  s.name,--统计信息的名称  
  auto\_created,--统计信息是否由查询处理器自动创建  
  user\_created--统计信息是否由用户显示创建  
from  
  sys.stats  
inner join  
  sysobjects o  
on  
  s.object\_id=o.id  
where  
  o.name='表名'  
go

\--查看统计信息中列的信息  
select  
  o.name,--表名  
  s.name,--统计信息的名称  
  sc.stats\_column\_id,  
  c.name---列名  
from  
  sys.stats\_columns sc  
inner join  
  sysobjects o  
on  
  sc.object\_id=o.id  
inner join  
  sys.stats s  
on  
  sc.stats\_id=s.stats\_id and sc.object\_id=s.object\_id  
inner join  
  sys.columns c  
on  
  sc.column\_id=c.column\_id and sc.object\_id=c.object\_id  
where  
  o.name='表名'

\--查看统计信息的明细信息  
dbcc show\_statistics

\--查看索引自动创建的统计信息  
exec sp\_autostats '对象名'

\--关闭自动生成统计信息的数据库选项  
alter datebase 数据库名 set auto\_create\_statistics off

\--计算随机抽样统计信息  
create statistics 统计信息名称 on 表名(列名)  
with sample 5 percent---创建统计信息，按5%计算随机抽样统计信息  
go

\--创建统计信息  
exec sp\_createstats--参数自己去查下帮助，在这里不一一列举

2.更新指定表的单个索引的统计信息  
update statistics 表名 索引名

3.对表进行全面扫描，更新统计信息  
update statistics 表名(列名) with fullscan

\--获取磁盘读写情况  
select  
  @@total\_read as '读取磁盘的次数',  
  @@total\_write as '写入磁盘的次数',  
  @@total\_error as '磁盘写入错误数',  
  getdate() as '当前时间'

\--获取数据库文件的I/O统计信息  
select \* from fn\_virtualfilestats(null,null)  
\--两个参数  
database\_id--指定数据库编号，如果为null,则为所有数据库实例返回I/O统计信息  
file\_id --文件的编号,如果为null,则为所有文件返回信息

\--获取I/O工作情况  
select  
  @@id\_busy，--SQL自上次启动以来的用于执行输入和输出操作的时间  
  @@timeticks, --每个时钟周期对应的微秒数  
  @@id\_busy\*@@timeticks as 'I/O 操作毫秒数',  
  getdate() as '当前时间'

\--查看SQL SEVER CPU活动,工作情况  
select  
  @@cpu\_busy,--自上次启动以来的工作时间  
  @@timeticks, --每个时钟周期对应的微秒数  
  @@cpu\_busy\*cast(@@timeticks as float)/1000 as 'cpu工作时间(秒)',  
  @@idie\*cast(@@timeticks as float)/1000 as 'CPU空闲时间(秒)'  
  getdate() as '当前时间'

\--获取网络数据包统计信息  
select  
  getdate() as '当前时间',  
  @@pack\_received as'输入数据包数量',  
  @@pack\_sent as '输出数据包数量',  
  @@packet\_error as '错误包数量'

服务器配置选项  
\--启动AWE  
sp\_configure 'show advanced options',1  
reconfigure  
go  
sp\_configure 'awe enable',1--启动AWE选项，用于支持超过4G内存 具体用法见笔记三  
go  
sp\_configure 'show advanced options',0  
reconfigure  
go

\--指定游标集中的行数  
sp\_configure 'show advanced options',1  
reconfigure  
go  
sp\_configure 'cursor threshold'--指定游标集中的行数，超过此行数，将异步生成游标键集  
go  
sp\_configure 'show advanced options',0  
reconfigure  
go  
/\*配置选项 'show advanced options' 已从 0 更改为 1。请运行 RECONFIGURE 语句进行安装。  
name                                minimum     maximum     config\_value run\_value  
\----------------------------------- ----------- ----------- ------------ -----------  
cursor threshold                    -1          2147483647  -1           -1

配置选项 'show advanced options' 已从 1 更改为 0。请运行 RECONFIGURE 语句进行安装。\*/

\--指定全文索引列的默认语言值  
sp\_configure 'show advanced options',1  
reconfigure  
go  
sp\_configure 'default full-text language'--2052代表简体中文，具体的查询联机丛书  
go  
sp\_configure 'show advanced options',0  
reconfigure  
go  
/\*name                                minimum     maximum     config\_value run\_value  
\----------------------------------- ----------- ----------- ------------ -----------  
default full-text language          0           2147483647  2052         2052\*/

\--控制是否让触发器返回结果集  
sp\_configure 'show advanced options',1  
reconfigure  
go  
sp\_configure 'disallow results from triggers',1--1代表on  
go  
sp\_configure 'disallow results from triggers',0--0代表off  
go  
sp\_configure 'show advanced options',0  
reconfigure  
go  
/\*配置选项 'disallow results from triggers' 已从 1 更改为 1。请运行 RECONFIGURE 语句进行安装。  
配置选项 'disallow results from triggers' 已从 1 更改为 0。请运行 RECONFIGURE 语句进行安装。\*/

\--控制最初为创建索引分配的最大内存量  
sp\_configure 'index create memory', 4096  
GO

\--设置可用的锁的最大个数  
sp\_configure 'show advanced options',1  
reconfigure  
go  
sp\_configure 'locks'---要设置的话在后面加',数字'  
go  
sp\_configure 'show advanced options',0  
reconfigure  
go  
/\*name                                minimum     maximum     config\_value run\_value  
\----------------------------------- ----------- ----------- ------------ -----------  
locks                               5000        2147483647  0            0  
\*/

\--设置SQL进程可使用的工作线程数  
sp\_configure 'show advanced options',1  
reconfigure  
go  
sp\_configure 'max worker threads'--要设置的话在后面加',数字'  
go  
sp\_configure 'show advanced options',0  
reconfigure  
go  
/\*name                                minimum     maximum     config\_value run\_value  
\----------------------------------- ----------- ----------- ------------ -----------  
max worker threads                  128         32767       0            0  
\*/

\--指定一个查询在超时前等待所需资源的时间  
sp\_configure 'query wait',数字  
go

\--指定在SQL SERVER超时之前远程操作可以持续的时间  
sp\_configure 'remote query  timeout',数字  
go

\--是否允许运行系统存储过程xp\_cmdshell  
sp\_configure 'show advanced options',1  
reconfigure  
go  
sp\_configure 'xp\_cmdshell',1  
reconfigure  
go  
sp\_configure 'show advanced options',0  
reconfigure  
go

\--从运行SQL SERVER实例的本地或远程服务器上控制存储过程的执行  
sp\_configure 'show advanced options',1  
reconfigure  
go  
sp\_configure 'remote access',1 --1表示允许  
reconfigure  
go  
sp\_configure 'remote access',0 --0表示禁止  
reconfigure  
go  
sp\_configure 'show advanced options',0  
reconfigure  
go

\---更多的查看联机丛书

\--启动，暂停和停止本地的SQL SERVER 服务  
net start MSSQLSERVER  --启动  
net pause MSSQLSERVER  --暂停  
net continue MSSQLSERVER  ---继续被停止的服务  
net stop MSSQLSERVER --停止

\--查询服务器配置选项信息  
select \* from sys.configurations  
go  
\--得到的结果中  
configuration\_id --配置选项的唯一ID  
name             --配置选项的名称  
value            --配置选项的值  
minimum          --配置选项的最小值  
maximum          --配置选项的最大值  
value\_in\_use     --配置选项当前使用的运行值  
description      --配置选项的说明  
is\_dynamic       --等于1时表示需要执行reconfiguration语句才能生效的变量  
is\_anvanced      --等于1时表示需要执行show advanced语句才能生效的变量

\--也可以使用sp\_configure查询服务器配置选项信息，只是参数有所不同，具体查看联机丛书

[DISK_str]: mailto:DISK=@str