sql数据库日常维护脚本

–备份

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

–清除日志

dumptransaction databasename with no_log 
backuplog 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

backuplog@database_namewith no_log 
dbcc shrinkfile (@database_name_log, 5) 

–分析SQL Server SQL 语句的方法:

setstatistics time {on|off} 
setstatistics io {on|off} 

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

–文本方式显示查询执行计划

set showplan_all {on|off} 
set showplan_text { on|off } 
setstatistics profile { on|off }

/*********完整备份作业*********/
–完整备份,每周一次

USE Master
GO
declare@strvarchar(100)
set@str='D:\DBtext\jgj\DBABak\FullBak'+replace(replace(replace(convert(varchar,getdate(),20),'-',''),'',''),':','')+'.bak'
BACKUPDATABASE[demo]TODISK=@str
WITH RETAINDAYS=15,NOFORMAT,NOINIT,
NAME=N'Demo完整备份',SKIP,NOREWIND,
NOUNLOAD,STATS=10
GO

/*********差异备份作业*********/
–截断日志

USE Master
GO
BACKUPLOG Demo  WITH NO_LOG
GO

–收缩日志文件

USE Demo
GO
DBCC SHRINKFILE (N'Demo_log',0,TRUNCATEONLY)
GO

–差异备份,每天一次

USE Master
GO
declare@strvarchar(100)
set@str='D:\DBtext\jgj\DBABak\DiffBak'+replace(replace(replace(convert(varchar,getdate(),20),'-',''),'',''),':','')+'.diff'
BACKUPDATABASE[Demo]TODISK=@str
WITH DIFFERENTIAL,RETAINDAYS=8,NOFORMAT,NOINIT,
NAME=N'Demo差异备份',SKIP,NOREWIND,
NOUNLOAD,STATS=10
GO

/********日志备份作业*********/
–日志备份,每小时一次

USE Demo
GO
declare@strvarchar(100)
set@str='D:\DBtext\jgj\DBABak\logbak'+replace(replace(replace(convert(varchar,getdate(),20),'-',''),'',''),':','')+'.trn'
BACKUPLOG[Demo]TODISK=@str
WITH RETAINDAYS=3,NOFORMAT,NOINIT,
NAME=N'Demo日志备份',SKIP,NOREWIND,
NOUNLOAD,STATS=10
GO

–删除过期的备份文件,每天两次

declare@strvarchar(100),@dirvarchar(100),@fileNamevarchar(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