問題描述

每當(dāng)我在 SQL Server 中恢復(fù)我的數(shù)據(jù)庫備份時，都會出現(xiàn)以下錯誤:

Whenever I restore a backup of my database in SQL Server I am presented with the following error:

Msg 3101, Level 16, State 1, Line 1
Exclusive access could not be obtained because the database is in use.
Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.

通常為了解決這個問題，我只是重新啟動服務(wù)器.當(dāng)我們在我們的開發(fā)機(jī)器上的本地實(shí)例上進(jìn)行開發(fā)時，這很好.但是我們有一些程序員需要訪問數(shù)據(jù)庫，以及讓每個人編寫他們的更改腳本并將其放入 Subversion 正在變成一場噩夢.不管怎樣，我們的簡單解決方案是將它放在辦公室的共享服務(wù)器上，并偶爾備份服務(wù)器，以防有人搞砸了數(shù)據(jù).

Usually to get around this I just restart the server. This was fine when we were developing on our local instance on our development machines. But we have a few programmers that need to access the database, and the logistics of having everyone script their changes and drop them into Subversion was becoming a nightmare. Regardless our simple solution was to put it on a shared server in the office and backup the server occasionally in case someone screwed up the data.

好吧，我搞砸了數(shù)據(jù)，需要恢復(fù).不幸的是，我在辦公室有另一個同事正在從事另一個項(xiàng)目，并且正在使用相同的數(shù)據(jù)庫服務(wù)器進(jìn)行開發(fā).為善起見，我想在不重新啟動 SQL Server 和可能中斷他的工作的情況下進(jìn)行恢復(fù).

Well, I screwed up the data and needed to restore. Unfortunately, I have another co-worker in the office who is working on another project and is using the same database server for development. To be nice I'd like to restore without restarting the SQL Server and possibly disrupting his work.

有沒有辦法在 T-SQL 中編寫腳本以獲取獨(dú)占訪問或刪除所有連接?

Is there a way to script in T-SQL to be able to take exclusive access or to drop all connections?

推薦答案

您可以通過以下方式強(qiáng)制數(shù)據(jù)庫脫機(jī)并斷開連接:

You can force the database offline and drop connections with:

EXEC sp_dboption N'yourDatabase', N'offline', N'true'

或者你可以

ALTER DATABASE [yourDatabase] SET OFFLINE WITH
ROLLBACK AFTER 60 SECONDS

回滾指定是否正在執(zhí)行任何操作.在那段時間之后，它們將被回滾.所以它提供了一些保護(hù).

Rollback specifies if anything is executing. After that period they will be rolled back. So it provides some protection.

抱歉，我沒有正確思考/閱讀.您可以重新聯(lián)機(jī)并備份.在 Stack Overflow 上還有一篇關(guān)于 T-SQL 片段的帖子，用于刪除所有連接而不是先離線連接:SQL Server 的隱藏功能

Sorry I wasn't thinking/reading right. You could bing back online and backup. There was also a post on Stack Overflow on a T-SQL snippet for dropping all connections rather than binging offline first: Hidden Features of SQL Server

這篇關(guān)于如何獲得對 SQL Server 2005 數(shù)據(jù)庫的獨(dú)占訪問權(quán)限來恢復(fù)?的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網(wǎng)！