MISTECH 技術手抄本

 
一、
從 SQL Server Management Studio
在資料庫上按右鍵->「屬性」->「選項」->「復原模式」，改成「簡單」模式
或是下指令
ALTER DATABASE 資料庫名稱 SET RECOVERY SIMPLE
  
 
二、
use 資料庫名稱
GO
DBCC SHRINKFILE('LDF的邏輯名稱',2)
其中的LDF的邏輯名稱，可以下以下指令取得
select * from sys.database_files
通常都是：資料庫名稱_log
  

設置的截圖如下：
先設定帳號對 msdb 資料庫有權限，並且有 SQLAgentOperator & SQLAgentReaderRole & SQLAgentUserRole 的角色

Grant 給 MSSQL DB User：s1 上述權限後，就可以看到 SQL Server Agent

MSSQL Server 2012 對於 table  中的欄位作加密已有支援，作業如下:

-- Create EmployeeReviews table and grant permissions
USE AdventureWorks
IF EXISTS (SELECT * FROM sys.tables t JOIN sys.schemas s ON t.schema_id = s.schema_id
           WHERE t.[Name] = 'EmployeeReview' AND s.[Name] = 'HumanResources')
 DROP TABLE HumanResources.EmployeeReview
CREATE TABLE HumanResources.EmployeeReview
(EmployeeID int NOT NULL,
 ReviewDate datetime DEFAULT GETDATE() NOT NULL,
 Comments varbinary(2000) NOT NULL)
-- Create database master key
IF NOT EXISTS(SELECT * FROM sys.symmetric_keys WHERE name = '##MS_DatabaseMasterKey##')
CREATE MASTER KEY
ENCRYPTION BY PASSWORD = 'Pa$$w0rd'
-- Create certificate
CREATE CERTIFICATE HRAppCert
WITH SUBJECT = 'HR certificate'
GO
-- Create symmetric key
CREATE SYMMETRIC KEY HRKey
WITH ALGORITHM = TRIPLE_DES
ENCRYPTION BY CERTIFICATE HRAppCert
GO
-- Insert encrypted data
    OPEN SYMMETRIC KEY HRKey DECRYPTION BY CERTIFICATE HRAppCert
    INSERT INTO HumanResources.EmployeeReview
    VALUES
    (1, DEFAULT, EncryptByKey(Key_GUID('HRKey'),'Increasing salary to $35,000'))
    CLOSE ALL SYMMETRIC KEYS
-- Read the decrypted data as HRApp
    OPEN SYMMETRIC KEY HRKey DECRYPTION BY CERTIFICATE HRAppCert
    SELECT CONVERT(varchar,DecryptByKey(Comments)) AS Comments FROM HumanResources.EmployeeReview
    CLOSE ALL SYMMETRIC KEYS

MSSQL Server 2012 在帳號認證可以採用 Windows Authentication 或是 SQL Server and Windows Authentication 混合模式兩種，認證模式若有修改則需重啟 SQL Server 才會生效。
 
 
若您只要使用 MSSQL Server 內鍵資料庫的帳號，但又要使用OS系統的 Security Policy 的原則，則除了將 MSSQL Server 的 Security 改為SQL Server and Windows Authentication 混合模式外，在建立資料庫帳號前先設定好 Local Security Policy，例如：密碼長度至少要 4 碼。

MSSQL Server 2000  建立一個資料庫時, 會有兩個檔案 MDF 檔(資料檔) 及 LDF 檔(交易檔), 有時因為某種原因(如當機, 備份時間不一致...等), 致使這兩個檔的 LSN 不一致. 或者只備份 MDF 檔, 因此在附加資料庫時, 會發生 LSN 的錯誤. 以致於資料庫無法附加
環境:
OS: Windows Server 2k3 SP2
MSSQL: 2000 版本8.0
資料庫: ATest
資料庫檔案: D:\DB\ATest_Data.MDF (遺失 ATest_Log.LDF 或兩者不同步, 導致附加資料庫時出現 LSN 錯誤)
要救回資料庫的方法如下:
Step 01: 新建立一個相同的資料庫, 建立好後 shutdown MSSQL 2k
Step 02: 移除新建立資料庫的檔案 D:\DB\ATest_Data.MDF & ATest_Log.LDF , 將原來的 ATest_Data.MDF 放回 D:\DB
Step 03: 在 SQL Query Analyzer 中執行以下语句，把该数据库设置为紧急模式
Use Master
Go
sp_configure 'allow updates', 1
reconfigure with override
Go
Step 04: 接著執行 下列指令
update sysdatabases set status = 32768 where name = 'ATest'
Step 05: 重启SQL Server服务, 會發生此資料庫進入緊急模式
Step 06: 執行 sp_dboption 指令，把資料庫设为Single User模式；
sp_dboption 'ATest', 'single user', 'true'
Step 07: 執行 DBCC CHECKDB
DBCC CHECKDB('ATest')
Step 08: 執行 DBCC REBUILD_LOG , 重建立 LDF 檔, 此時可以看到資料庫內的資料表了
DBCC REBUILD_LOG('ATest','D:\DB\ATest_Log.LDF')
Step 09: 執行下列指令, 将資料庫回覆正常狀態
update sysdatabases set status=0 where name='ATest'
或者
sp_resetstatus "ATest"
go
sp_configure 'allow updates', 0
reconfigure with override
Step 10: 執行 sp_dboption 指令, 將資料庫設回多人模式:
sp_dboption 'ATest','dbo use only','false'
Step 11: 重啟 MSSQL Server 2000 即可

MSSQL Server 2012 Restore 備份還原概念：
MSSQL Server 2012 Restore 在還原的過程中，有三個階段 (Phase)：
1. Data Copy：建立實體檔案，與回存資料
2. Redo：從 LOG File 回存所有已經確認 ( commit ) 的 Transaction

MSSQL Server 2012 Transaction Concept 概念：
MSSQL Server 2012 在資料寫的的運作上，是秉持著 WAL(Write Ahead Logging) 的機制，Transaction 存取磁碟的作業，主要是依照下列 4 個步驟來作業：

1. 將 Transaction 寫入 LOG File
2. 將 Transaction 的異動修改 Buffer (記憶體) 裡的資料
3. 交易完成 Transaction Commit
4. 將 Buffer (記憶體) 裡的資料寫入 Disk ( Data Files )裡

當電腦主機名變更時，如：SA01 變更為 VS21，MSSQL Server 2012 也要作相對應的更名作業，不然在執行某些作業時會有問題，變更的操作如下：
Step 01：change to SQLCMD MODE
 
Step 02: Drop 現行的 Instance：SA01

MS SQL Server 2008 R2 在 Windows Server 2008 R2 SP1 上運行一段時日，近來常發現 MS SQL Server 2008 R2 常因 MSSQLSERVER_701：資源集區 'internal' 中的系統記憶體不足，無法執行此查詢 而自動停止 SQLSERVERAGENT 服務，導致 MSSQL Server 自動停止 。

 

關於Express的版本

SQL Server Express (x86 和 x64)