SSMS使用笔记

sqlserver内存管理

sqlserver对系统内存的管理原则是：按需分配，且贪婪(用完不还)。

​ 它不会自动释放内存，因此执行结果集大的sql语句时，数据取出后，会一直占用内存，直到占满机器内存(并不会撑满，还是有个最大限制，比机器内存稍小)，在重启服务前，sqlserver不会释放该内存，也没有任何办法可人为释放。

​ 以下命令虽然可释放缓存，但sqlserver并不会因此释放已占用的内存。

DBCC FREEPROCCACHE     --清除存储过程相关的缓存
DBCC FREESESSIONCACHE     --清除会话缓存
DBCC FREESYSTEMCACHE('All')     --清除系统缓存
DBCC DROPCLEANBUFFERS     --清除所有缓存

修改最大可占用物理内存进行限制。

修改办法如下：

​ 管理员用户登录数据库客户端（Microsoft SQL Server Management Studio），在实例名（树形菜单根节点，显示登录名那栏）上右键，选择属性–>内存，设置“最大服务器内存”，单位为MB，可根据实际内存调整。

如此调整后，机器内存便不会被sqlserver耗尽了。

SQLServer数据库备份的使用

推荐使用方法二

方法一：使用SQL语句添加语句

1. 数据库备份

选择想要备份的数据库，”右键“→”任务“→”生成脚本“。

直接“下一步”。

选择好具体的数据库对象，然后点击“下一步”。

选择要保存的脚本选项，然后点击“下一步”。

默认选择“下一步”。

2. 数据库还原

使用刚才生成的SQL语句，点击执行。即可在数据库中创建数据表。

注意： 此处要注意的是，在新的数据库中创建一个数据库的名字，必须是跟之前的数据库名字保持一致。比如之前是PerformanceAppraisalDb，就要创建一个数据库名为PerformanceAppraisalDb的数据库。

注意： 此方法只会还原数据表，并不会对数据库进行添加数据项。

方法二：使用备份还原方式

1. 数据库备份

选择要备份的数据库，“右键”→“任务”→“备份”。

留意好备份路径，点击“确定”进行备份。

2. 数据库还原

在新数据库中，添加一个名字与之前备份的数据库名字相同的数据库。例如：PerformanceAppraisalDb

在新建的数据库中，“右键”→“任务”→“还原”→“数据库”。

选择“设备”，点击三个小点的按钮，打开资源管理器。在新弹出的窗口中，点击“添加”。

选择好备份的数据库脚本，点击确定。

此处不要着急点击"确定"。

在“选项”中，选中“覆盖现有数据库”，取消掉“日志相关的”。最后点击“确定”。

注意： 此方法的好处是可以备份到数据表内的数据项。

SSAS数据库备份与还原

1. SSAS备份

选择想要备份的数据库，”右键“→”备份“。点击浏览可以选择备份路径

选择好路径直接点击OK。

2. SSAS还原

直接右键任意数据库，”右键“→”还原“。通过浏览选择备份文件

通过还原数据库选项选择需要还原的数据库，现有一般是覆盖，新的就是新增

存储位置设置还原数据库的路径

上述设置完后 点击确定 等待还原结束

sqlserver用户权限设置

数据库

新建一个用户test，拥有浏览数据库POC_test中表数据的权限，但没有增删改的权限

1.新建用户test

安全性–>登录名，右键登录名 选择新建登录名

2.服务器角色

除非是管理员账户，一般默认public即可

bulkadmin:允许用户执行 BULK INSERT 任务。
dbcreator:允许用户创建、更改、删除数据库。
diskadmin:允许用户管理磁盘文件。
processadmin:允许用户终止 SQL Server 进程。
public:所有登录用户都是 public 角色的成员，但它没有显式权限。
securityadmin:允许用户管理登录、服务器角色和数据库角色。
serveradmin:允许用户配置服务器范围的配置选项。
setupadmin:允许用户添加和删除 SQL Server 实例。
sysadmin:具有服务器上的最高权限，可以执行任何操作。

3.用户映射

选择映射的库给test用户，角色身份按需授予

db_owner:db_owner 是数据库的所有者，拥有数据库中所有对象的完全控制权限，包括创建、删除、更改和查询。
db_accessadmin:允许成员添加或删除 Windows 用户、Windows 组或 SQL Server 登录作为数据库用户。
db_securityadmin:允许成员管理数据库角色、权限和用户。
db_ddladmin:允许成员运行 CREATE, ALTER, DROP 和其他 DDL 语句。
db_datawriter:允许成员在所有用户表中插入、更新或删除数据。
db_datareader:允许成员在所有用户表中选择数据。
db_denydatawriter:阻止成员在所有用户表中插入、更新或删除数据。
db_denydatareader:阻止成员在所有用户表中选择数据。
public:所有数据库用户都是 public 角色的成员，但它没有显式权限。

也可以通过命令行分配角色

-- 要将用户添加到 db_datareader 角色，可以使用以下 T-SQL 命令：
USE YourDatabaseName;  -- YourDatabaseName数据库名
ALTER ROLE db_datareader ADD MEMBER YourUserName;  -- YourUserName用户名

4.安全对象

分配连接SQL的权限即可

5.状态

存储过程

对于POC_test库下的存储过程，新建用户test没有查看的权限

POC_test–>可编程性–>存储过程–>选择分配权限的函数右键点击属性–>搜索用户并选择–>分配权限

也可以通过命令行分配权限

-- GRANT EXECUTE 语句将授予用户对指定函数的执行权限
USE YourDatabaseName; -- YourDatabaseName数据库名
GRANT EXECUTE ON YourFunctionName TO YourUserName; -- YourFunctionName函数名、YourUserName用户名

-- 授予 VIEW DEFINITION 权限时，用户仅能够查看对象的定义，而不具备执行该对象的权限。
-- 如果用户需要执行函数，你还需要使用 GRANT EXECUTE 授予相应的执行权限
USE YourDatabaseName;
GRANT VIEW DEFINITION ON OBJECT::YourFunctionName TO YourUserName;

sqlserver邮件功能配置

在配置之前请先把邮件的POP3之类的设置一下：

简图：

其他邮箱都是类似的，下面我们开始SQL Server数据库的演示

注：服务器对应端口号可以直接百度查，比如上述SMTP服务器，它的默认端口号是25

图形化演示

第一步：找到数据库【管理】下的【数据库邮件】，如下图：

第二步：在弹出的配置向导中我们继续

第三步：按向导提示，我们选择第一个

配置名字随意取，可以用项目名。显示名称建议用版本号+服务器ip，这样出问题可以定位跟踪

第四步：添加邮件账户，账号具体信息如下图

第五步：配置完后点击下一步

微软图形化的东西一般有个特点，一路下一步基本上能解决所有基础问题

勾选一下（貌似不勾选也没事）

这样我们就配置完成了。

测试一下

发一封邮件到“我为NET狂”的官方邮件去

去看看：

命令演示

命令演示：（不需要记，你又不是DBA，会用即可）

脚本如下：

--开启发邮件功能
exec sp_configure 'show advanced options',1
reconfigure with override
go
exec sp_configure 'database mail xps',1
reconfigure with override
go

--创建邮件帐户信息
exec msdb.dbo.sysmail_add_account_sp
 @account_name ='dunitian1',                   -- 邮件帐户名称  
 @email_address ='xxxx@yeah.net',              -- 发件人邮件地址    
 @display_name ='SQLServer2014_192.168.1.20',  -- 发件人姓名
 @MAILSERVER_NAME = 'smtp.yeah.net',           -- 邮件服务器地址
 @PORT =25,                                    -- 邮件服务器端口
 @USERNAME = 'xxxx@yeah.net',                  -- 用户名
 @PASSWORD = '邮件密码'                            -- 密码  
GO
--数据库配置文件
exec msdb.dbo.sysmail_add_profile_sp
 @profile_name = 'SQLServer_DotNetCrazy1',     -- 配置名称
 @description = '数据库邮件配置文件'            -- 配置描述
go

--用户和邮件配置文件相关联
exec msdb.dbo.sysmail_add_profileaccount_sp
 @profile_name = 'SQLServer_DotNetCrazy1',     -- 配置名称
 @account_name = 'dunitian1',                  -- 邮件帐户名称    
 @sequence_number = 1                          -- account 在 profile 中顺序（默认是1）
go1.2.3.4.5.6.7.8.9.10.11.12.13.14.15.16.17.18.19.20.21.22.23.24.25.26.27.28.29.30.

发送邮件脚本：

exec msdb.dbo.sp_send_dbmail
@profile_name = 'SQLServer_DotNetCrazy1',   --配置名称
@recipients = 'dotnetcrazy@foxmail.com',    --收件名称
@body_format = 'HTML',                      --内容格式
@subject = '文章标题',
@body = '邮件内容'1.2.3.4.5.6.

结果：20的ip也发过来了

相关查询

select * from msdb.dbo.sysmail_allitems
select * from msdb.dbo.sysmail_faileditems --失败状态的消息
select * from msdb.dbo.sysmail_unsentitems --看未发送的消息
select * from msdb.dbo.sysmail_sentitems --查看已发送的消息
select * from msdb.dbo.sysmail_event_log  --记录日记

sqlserver数据库如何清理日志文件

很多时候SQLSERVER的日志文件是不看的，但时间久了，够把磁盘撑爆，这时候就需要清理日志文件。使用以下方法，在实际环境中经过测试，400G的日志文件1秒就被清理

1. 将恢复模式改成“简单”

右键数据库 - 属性，切换到选项，将恢复模式修改为简单。

2. 收缩日志

右键数据库 - 任务 - 收缩 - 文件

确定后会发现，日志文件被迅速清理。

3. 命令操作

USE [master]
GO
ALTER DATABASE 要清理的数据库名称 SET RECOVERY SIMPLE WITH NO_WAIT
GO
ALTER DATABASE 要清理的数据库名称 SET RECOVERY SIMPLE   --简单模式
GO
USE 要清理的数据库名称
GO
DBCC SHRINKFILE (N'要清理的数据库名称_log' , 2, TRUNCATEONLY)  --设置压缩后的日志大小为2M，可以自行指定
GO
USE [master]
GO
ALTER DATABASE 要清理的数据库名称 SET RECOVERY FULL WITH NO_WAIT
GO
ALTER DATABASE 要清理的数据库名称 SET RECOVERY FULL  --还原为完全模式
GO
```sql
USE [master]
GO
ALTER DATABASE 要清理的数据库名称 SET RECOVERY SIMPLE WITH NO_WAIT
GO
ALTER DATABASE 要清理的数据库名称 SET RECOVERY SIMPLE   --简单模式
GO
USE 要清理的数据库名称
GO
DBCC SHRINKFILE (N'要清理的数据库名称_log' , 2, TRUNCATEONLY)  --设置压缩后的日志大小为2M，可以自行指定
GO
USE [master]
GO
ALTER DATABASE 要清理的数据库名称 SET RECOVERY FULL WITH NO_WAIT
GO
ALTER DATABASE 要清理的数据库名称 SET RECOVERY FULL  --还原为完全模式
GO