恢复数据库SQL Server查询

Question

我希望能够编写自己的查询语句来恢复数据库。所需恢复的数据库必须具备删除当前用户并重新映射相同用户的所有设置。原因是当数据库被恢复时，用户将没有正确的设置来使用数据库，并且需要重新分配用户权限。

- MohammedT

---

通常这被称为备份。 - Hogan

该功能仅适用于SQL2012。 - RoMEoMusTDiE

Answer 1

请看这里：

步骤1：从备份中检索数据库的逻辑文件名。

RESTORE FILELISTONLY
FROM DISK = 'D:BackUpYourBaackUpFile.bak'

GO

步骤2：在下一步中使用LogicalName列中的值。 ----将数据库设置为单用户模式

ALTER DATABASE YourDB
SET SINGLE_USER WITH
ROLLBACK IMMEDIATE

----恢复数据库

RESTORE DATABASE YourDB
FROM DISK = 'D:BackUpYourBaackUpFile.bak'
WITH MOVE 'YourMDFLogicalName' TO 'D:DataYourMDFFile.mdf',
MOVE 'YourLDFLogicalName' TO 'D:DataYourLDFFile.ldf'

/如果在数据库进入多用户模式之前没有出现错误，则该语句无误。 如果出现错误，请执行以下命令，它将把数据库转换为多用户模式。/

ALTER DATABASE YourDB SET MULTI_USER
GO

- Rahul Tripathi

---

你好。我只是想知道，如果我想通过从“.bak”文件还原来覆盖当前正在使用的数据库，该怎么办？谢谢。 - Haseeb Zahid

Answer 2

原因是当数据库被恢复时，用户将没有正确的设置来使用数据库，并且需要重新分配用户权限。我猜测以下情况：1.您正在使用混合模式身份验证，用户是SQL Server用户（而不是Windows用户）；2.你正在将数据库恢复到不同于备份所在的服务器上。如果是这样，您需要考虑以下几点：1.用户必须在第二台服务器上存在，它不会在您还原数据库时自动创建；2.仅仅在第二台服务器上创建一个同名的新用户是不够的，对于SQL Server来说，这将是一个不同的用户！我猜测第二个原因是为什么您的用户在还原后没有"正确的设置"的原因。一些背景：

• 在SQL Server中，所有用户都用SID（类似于GUID的唯一且不可读的标识）表示，SQL Server在内部不关心实际用户名。
• 每个用户在数据库上拥有的权限都保存在该数据库内部，使用SID而非用户名进行标识。
• 当您将数据库恢复到另一台服务器时，权限将随着数据库一起恢复...但它们仅在新服务器上存在具有相同SID的用户时才有效。
• 正如我之前所说：当您只是创建具有相同名称的新用户时，他会获得新的SID。

因此，可能发生了以下情况：

在旧服务器上，有一个名为“Mohammed Tahir”的用户，其SID为123456789。

在数据库内部，存在一种权限，即“允许SID 123456789 从该数据库中读取”。

您已将数据库恢复到新服务器上。

您在新服务器上创建了一个名为“Mohammed Tahir”的用户，但他有一个不同的SID（假设为987654321），因此现有的123456789权限对他无效！

---

所以你需要的是一种将用户从旧服务器“复制”到新服务器，并具有完全相同SID的方法。

有一个名为Microsoft的存储过程叫做sp_help_revlogin，可以实现这一点。它会生成一个包含来自旧服务器的所有用户的脚本。然后，您可以在新服务器上运行该脚本，它将创建具有与旧服务器上相同SID的用户。然后，您可以将数据库从旧服务器还原到新服务器，并且数据库中已经存在的所有权限都有效。

您可以从此MSDN文章中获取sp_help_revlogin： 如何在SQL Server实例之间传输登录和密码。

请注意，实际还原过程没有什么特别之处-用户及其SID是不同的关键。因此，您不需要任何“特殊”的命令来还原数据库，只需要标准命令即可，例如Rahul Tripathi's answer中的命令。

- Christian Specht