還原資料庫時,"無法獲得獨佔存取權,因為資料庫正在使用中"的解決方法

使用Sql Server Management Studio 還原資料庫時,
常會遇到 "無法獲得獨佔存取權,因為資料庫正在使用中" 的錯誤訊息

我的一勞永逸解決方法
用指令還原
指令如下

ALTER DATABASE 你的DB名稱 SET OFFLINE WITH ROLLBACK IMMEDIATE
RESTORE DATABASE 你的DB名稱 FROM  DISK='C:\你的路徑\你的備份檔點BAK' WITH RESTRICTED_USER,REPLACE

絕對有效

.new 讀資料 sample

using System.Data;
using System.Data.SqlClient;

sql = "SELECT * FROM table";
string _connection = ConfigurationManager.ConnectionStrings["cn"].ConnectionString;
SqlConnection conn = new SqlConnection(_connection);
try {
    SqlCommand cmd = new SqlCommand();
    cmd.Connection = conn;
    cmd.CommandText = sql;
    conn.Open();
    SqlDataAdapter sda = new SqlDataAdapter(cmd);
    DataSet ds = new DataSet();
    sda.Fill(ds);
}
catch (Exception) {
    throw;
}
finally {
    conn.Close();
}

TRIGGER SAMPLE

USE [DB_NAME]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TRIGGER [dbo].[tg_NAME]
   ON  [dbo].[TABLE_TAB]
   AFTER INSERT,UPDATE,DELETE
AS
BEGIN
    SET NOCOUNT ON;
  
    IF (SELECT COUNT(*) FROM INSERTED) > 0 AND (SELECT COUNT(*) FROM DELETED) = 0 BEGIN --新增
        INSERT INTO TABLE_LOG
        SELECT *, 'INSERT' FROM INSERTED
    END
  
    IF (SELECT COUNT(*) FROM INSERTED) > 0 AND (SELECT COUNT(*) FROM DELETED) > 0 BEGIN --變更
        INSERT INTO TABLE_LOG
        SELECT *, 'UPDATE_BEFORE' FROM DELETED
        INSERT INTO TABLE_LOG
        SELECT *, 'UPDATE_AFTER' FROM INSERTED
    END
  
    IF (SELECT COUNT(*) FROM INSERTED) = 0 AND (SELECT COUNT(*) FROM DELETED) > 0 BEGIN --刪除
        INSERT INTO TABLE_LOG
        SELECT *, 'DELETE' FROM DELETED
    END
  
              
END

GO

SELECT TOP 1 * FROM TABLE

MS SQL SERVER COMMAND

SELECT TOP 5 * FROM TABLE

ORACLE COMMAND

SELECT * FROM (
      SELECT * FROM TABLE ORDER BY COLUMN1
)
WHERE ROWNUM <= 5
ORDER BY ROWNUM ASC

FOR WEB

FOR WEB

using System.Collections;

ArrayList ArrSql1 = new ArrayList();

ArrSql1.Add("COMMAND");


        private bool DBWriter(ArrayList SQL)
        {
            try
            {
                SqlCommand DBCommand = new SqlCommand();
                SqlTransaction DBTransaction;
                SqlConnection DBConnection = new SqlConnection();
                DBConnection.ConnectionString = ConfigurationManager.ConnectionStrings["INFOBCKConnectionString"].ConnectionString.Trim();
                DBConnection.Open();
                DBCommand = DBConnection.CreateCommand();
                DBTransaction = DBConnection.BeginTransaction("SQLTransaction");
                DBCommand.Connection = DBConnection;
                DBCommand.Transaction = DBTransaction;
                try
                {
                    for (Int32 i = 0; i < SQL.Count; i++)
                    {
                        DBCommand.CommandText = SQL[i].ToString();
                        DBCommand.ExecuteNonQuery();
                    }
                    DBTransaction.Commit();
                    return true;
                }
                catch (Exception ex)
                {
                    DBTransaction.Rollback();
                    ex.ToString();
                    return false;
                }
            }
            catch (Exception ex)
            {
                ex.ToString();
                return false;
            }
        }

處理中英文夾雜時，要寫入oracle卻發生長度過常的處理方式

直接使用

System.Text.Encoding.GetEncoding("big5").GetByteCount("中文字串ABC")

抓取長度，上例是 11

SQL Server Management Studio無法記住密碼

用sa賬戶登錄sql server 2008，勾選了“記住密碼”，但重新登錄時，SQL Server Management Studio無法記住密碼。

後來發現，在重新登錄時，登錄名顯示的並非是sa賬戶，而是其他賬戶。點擊下拉框，發現記錄的登錄名不止一個。於是嘗試清除這些歷史記錄。

清除SQL Server Management Studio的歷史記錄很簡單，只要刪除或重命名文件SqlStudio.bin即可。該文件通常在以下目錄：
xp在C:\Documents and Settings\Administrator\Application Data\Microsoft\Microsoft SQL Server\100\Tools\Shell\SqlStudio.bin
win7在C:\Users\%username%\AppData\Roaming\Microsoft\Microsoft SQL Server\100\Tools\Shell\SqlStudio.bin

清除之後，重新打開SQL Server Management Studio發現，之前那些記錄的登錄名果然沒了，然後重新用sa賬戶登錄，勾選“記住密碼”。如此反复幾次，發現就可以記住sa密碼了！
如果是 sql server 2005 出現同樣的問題，也可以嘗試以上解決方案。

sql server 2005記錄登錄名的文件是mru.dat，
路徑xp位於C:\Documents and Settings\Administrator\Application Data\Microsoft\Microsoft SQL Server\90\Tools\Shell\文件夾下。