【SQLSERVER】备份还原除当前数据库～之外的其他数据库的bak备份

在使用 SQL Server 时，有些人在迁移数据库时会尽量避免采用备份→还原的步骤。原因是在还原过程中会出现晦涩难懂且奇怪的错误。
 
首先，确认还原时的选项中是否带有 “WITH REPLACE”。这是一个关于是否完全替换数据库的选项。
即便这样做，有时也可能无法解决问题，但从这里开始才是重点。指定数据库的路径名。由于在 Management Studio 中无法做到这一点，因此请执行以下查询。
RESTORE DATABASE TEST
FROM FILE='C:\MSSQLData\TEST.bak'
WITH REPLACE,
MOVE 'TEST' TO 'C:\MSSQLData\TEST.mdf',
MOVE 'TEST_ldf' TO 'C:\MSSQLData\TEST.ldf'

推测原因是备份文件中记录了数据库的路径，而该路径与数据库的实际路径名不匹配。通过使用 MOVE 指定与数据库路径名相符的路径，问题即可得到解决。

在 SQL Server 中设置数据库 TCP/IP 访问，可按以下步骤操作：

1. 开启 SQL Server 配置管理器

SQL Server 配置管理器是一个工具，借助它能对 SQL Server 的网络配置进行管理。你可以通过以下方式打开它：

 

  • 按下 Win + R 组合键，输入 SQLServerManager15.msc（这里的 15 对应 SQL Server 2019 版本，若为其他版本，数字会有所不同，例如 SQL Server 2017 是 14，SQL Server 2016 是 13），然后回车。
  • 也可以在开始菜单里找到 SQL Server 程序组，从中找到并打开 SQL Server 配置管理器。

2. 启用 TCP/IP 协议

在 SQL Server 配置管理器的左侧面板，展开 “SQL Server 网络配置”，选择你的 SQL Server 实例（如 MSSQLSERVER），然后在右侧面板中找到 “TCP/IP”。右键点击 “TCP/IP”，选择 “启用”。

3. 配置 TCP/IP 属性

  • 右键点击 “TCP/IP”，选择 “属性”。
  • 在 “协议” 选项卡中，确保 “已启用” 设置为 “是”。
  • 在 “IP 地址” 选项卡中：
    • 对于 “IPAll” 部分，设置 “TCP 端口” 为你想要使用的端口号，默认是 1433。
    • 你可以根据需要启用或禁用其他 IP 地址。

4. 重启 SQL Server 服务

在 SQL Server 配置管理器的左侧面板，展开 “SQL Server 服务”，右键点击你的 SQL Server 实例（如 SQL Server (MSSQLSERVER)），选择 “重启”。

5. 配置防火墙

若你的服务器启用了防火墙，需要开放相应的端口（如 1433），以允许外部 TCP/IP 连接。以下是在 Windows 防火墙中开放端口的示例命令：
New-NetFirewallRule -DisplayName "SQL Server TCP Port 1433" -Direction Inbound -Protocol TCP -LocalPort 1433 -Action Allow

6. 开启混合认证模式

步骤 1：打开 SQL Server 配置管理器

可以通过在开始菜单中找到 “SQL Server 配置管理器” 来打开它；也可以使用 “运行”（Win + R）并输入 SQLServerManagerXX.msc（XX 是对应 SQL Server 版本号，如 2019 对应 15，2017 对应 14）来打开。

步骤 2：停止 SQL Server 服务

在 “SQL Server 服务” 中，右键单击你要配置的 SQL Server 实例（如 SQL Server (MSSQLSERVER)），选择 “停止”。

步骤 3：修改身份验证模式

  1. 右键单击 SQL Server 实例，选择 “属性”。
  2. 在弹出的属性窗口中，切换到 “安全性” 选项卡。
  3. 在 “服务器身份验证” 区域，选择 “SQL Server 和 Windows 身份验证模式”。
  4. 点击 “确定” 保存更改。

步骤 4：重启 SQL Server 服务

在 “SQL Server 服务” 中，右键单击你要配置的 SQL Server 实例，选择 “启动”。

7. 创建SQL Server用户

在 SQL Server 中创建用户可按以下步骤进行：
-- 在 master 数据库中创建登录名
USE master;

CREATE LOGIN new_login 
WITH PASSWORD = 'YourPassword',
CHECK_POLICY = ON,
CHECK_EXPIRATION = ON;

-- 切换到目标数据库
USE your_database;

-- 在目标数据库中创建用户并关联登录名
CREATE USER new_user FOR LOGIN new_login;

-- 授予用户对数据库的访问权限
EXEC sp_addrolemember 'db_datareader', 'new_user';
EXEC sp_addrolemember 'db_datawriter', 'new_user';

-- 授予用户数据库所有者（owner）权限
ALTER AUTHORIZATION ON DATABASE::your_database TO new_user;
 
这里先在 master 数据库中创建了一个登录名 new_login，然后在目标数据库 your_database 中创建用户 new_user 并关联该登录名，最后授予该用户读取和写入数据的权限。

8. 测试连接

你可以使用 SQL Server Management Studio（SSMS）或者其他数据库客户端工具，通过 TCP/IP 协议连接到 SQL Server 实例，验证配置是否成功。连接时，服务器名称格式为 服务器 IP 地址,端口号（如 192.168.1.100,1433）。
通过以上步骤，你就可以成功设置 SQL Server 数据库的 TCP/IP 访问。