### 数据库日常维护常用脚本知识点
#### 一、数据库备份操作
数据库备份是数据库管理中最基础也是最重要的工作之一，确保数据的安全性与完整性。在给出的文档中，提供了如下一条备份语句：
```sql
declare @sql varchar(8000)
set @sql = 'backup database smallerpto disk = ''d:\'' + RTRIM(CONVERT(varchar, getdate(), 112)) + '.bak'''
exec (@sql);
```
**注意事项：**
- 引号不可缺少，否则会报错。
- `getdate()` 返回当前日期时间，`CONVERT(varchar, getdate(), 112)` 将其转换为“YYYYMMDD”的格式。
**应用场景：**
- 适用于日常定期备份，备份文件按照日期命名并保存至指定目录。
#### 二、删除5天前的备份文件
为了保持备份文件的有序性和减少存储空间占用，需要定期清理旧备份文件。给出的脚本如下：
```sql
declare @sql varchar(8000)
set @sql = 'del d:\' + RTRIM(CONVERT(varchar, getdate() - 5, 112)) + '.bak'
exec master..xp_cmdshell @sql;
```
**注意事项：**
- 需要启用`xp_cmdshell`扩展存储过程，可以通过以下命令开启：
```sql
EXEC sp_configure 'show advanced options', 1; RECONFIGURE;
EXEC sp_configure 'xp_cmdshell', 1; RECONFIGURE;
```
**应用场景：**
- 清理5天前的备份文件，减少磁盘空间占用，保证备份文件的有效性。
#### 三、收缩数据库语句
随着数据库的使用，可能会出现未使用的空间，通过收缩数据库可以释放这些空间，减少磁盘占用。
**旧语句**（已弃用）：
```sql
dump transaction smallerp with no_log;
```
**新语句**：
```sql
ALTER DATABASE smallerp SET RECOVERY SIMPLE;
DBCC SHRINKDATABASE (smallerp, 0);
```
**注意事项：**
- SQL Server 2008及以后版本不再支持使用`dump transaction`来收缩数据库。
- 使用`ALTER DATABASE SET RECOVERY SIMPLE`将数据库设置为简单恢复模式后才能进行收缩操作。
- `DBCC SHRINKDATABASE (dbname, target_percent)`其中`target_percent`表示目标百分比，0表示尽可能缩小。
**应用场景：**
- 在数据库空间利用率较低时，进行空间回收，优化存储。
#### 四、查看数据库活动用户和进程信息
通过`sp_who`存储过程可以查看当前数据库中的活动用户和进程信息。
**命令**：
```sql
EXEC sp_who 'active';
```
**注意事项：**
- 需要在命令前加上`EXEC`关键字，否则会出现语法错误。
- 进程号（SPID）小于50的通常为SQL Server内部进程，大于50的为用户连接进程。
- `dbid`表示数据库编号，`objid`表示数据对象编号。
**应用场景：**
- 监控数据库中的活动连接，定位性能问题。
#### 五、查看数据库中的锁情况
通过`sp_lock`存储过程可以查看当前数据库中的锁情况。
**命令**：
```sql
EXEC sp_lock;
```
**应用场景：**
- 用于监控数据库中的锁冲突，避免死锁问题。
#### 六、分析SQL Server里SQL语句的方法
设置统计信息以帮助分析SQL语句的执行情况。
**命令**：
```sql
SET STATISTICS TIME {ON | OFF};
SET STATISTICS IO {ON | OFF};
```
**应用场景：**
- 分析查询执行效率，优化SQL语句。
#### 七、文本方式显示查询执行计划
通过设置相关参数，可以在文本方式下显示查询执行计划。
**命令**：
```sql
SET SHOWPLAN_ALL {ON | OFF};
SET SHOWPLAN_TEXT {ON | OFF};
SET STATISTICS PROFILE {ON | OFF};
```
**应用场景：**
- 调试复杂查询，理解查询执行流程。
#### 八、差异备份
差异备份只备份自上次完整备份以来发生更改的数据。
**命令**：
```sql
DECLARE @str VARCHAR(100)
SET @str = 'D:\' + REPLACE(REPLACE(REPLACE(CONVERT(varchar, GETDATE(), 20), '-', ''), '', ''), ':', '') + '.diff'
BACKUP DATABASE smallerpto DISK = @str WITH DIFFERENTIAL, RETAINDAYS = 8, NOFORMAT, NOINIT, NAME = N'Demo差异备份', SKIP, NOREWIND, NOUNLOAD, STATS = 10
GO
```
**注意事项：**
- 通过`GETDATE()`获取当前日期时间，使用`CONVERT`函数转换为字符串，并去除特殊字符，得到文件名。
- `RETAINDAYS`定义备份文件保留天数。
- `DIFFERENTIAL`指定备份类型为差异备份。
**应用场景：**
- 快速恢复最近的数据变化，提高数据恢复效率。
#### 九、删除过期的备份文件
定期删除过期的备份文件，以保持备份文件列表的整洁性。
**命令**：
```sql
DECLARE @str VARCHAR(100), @dir VARCHAR(100), @filename VARCHAR(30)
SET @dir = 'del D:\'
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
```
**注意事项：**
- 需要启用`xp_cmdshell`扩展存储过程。
- 通过`LEFT`函数截取文件名的一部分以匹配文件。
**应用场景：**
- 自动化清除过期备份文件，节省存储空间，保证备份文件的新鲜度。
以上内容覆盖了数据库日常维护中常用的一些脚本知识点，包括备份、删除旧备份、收缩数据库、监控活动用户和进程、查看锁情况以及分析SQL语句等。这些脚本对于确保数据库稳定运行、提高数据安全性具有重要作用。
