在特定情況下 (修改資料庫之前、備份還原至別的SQL server等等...) 有些時候我們需要將現有的資料庫檔案作備份 此時就會需要用到備份資料庫的功能 執行一次性備份的方式如下： 1、首先我們先輸入完登入資訊並連入資料庫 2、選擇預計要備份的資料庫並在上方按滑鼠右鍵 > [工作] > [備份] 3、點選進入備份選單後我們可以選擇要備份的資料庫類型(這裡選擇完整備份) 完整、差異、交易紀錄的觀念可參考下列文章保哥部落格：觀念釐清：SQL Server 完整備份、差異備份、交易記錄備份 4、直接按下右下方的確定選項，將資料庫直接備份在預設路徑 5、一般我們為選擇不同的磁碟或著主機(非本機)來儲存備份檔， 可以透過點選[加入]並修改框中[檔案名稱]的路徑 6、由於這裡是測試性質暫時先將檔案備份在"C:\backup\SQLDB_backup.bak"並按下[確定] 7、完成後會除現資料庫的備份已順利完成的字樣 8、我們到相對應的路徑下即可檢視到剛剛備份完成的檔案   上述的方式比較適用於一次性備份的需求 若需要長期進行備份可參考SQL 2008R2 設置維護計畫的方式作長期性備份及維護的作業   參考資料： 如何：備份資料庫 SQL 2008R2 設置維護計畫 保哥部落格：觀念釐清：SQL Server 完整備份、差異備份、交易記錄備份

SQL Server 維護管理的部分一般是由SQL Agent來執行 (非Express版本) 1、首先確認服務中的SQL ServerAgent 是否”已啟動” 2、確認SQL Server組態管理員的 SQL server 及SQL Agent都正常運作 3、使用SSMS連接資料庫部開啟”管理”中的”維護計畫”，我們可以透過維護計畫精靈達到快速建置維護計畫的目的 4、透過維護計畫精靈建立維護計畫 (紅框中的設定可以將維護作業個別排程) 5、選擇要執行的維護計劃(本測試僅選擇基本的完整、差異備份及清除工作) 6、若在第4點選擇”對整個計劃單一排程或沒有排程”，這裡可以調整維護計畫執行時的順序 7、完整備份工作的設定(下拉式選單可選擇”所有資料庫、系統資料庫、特定資料庫) 8、差異備份工作的設定(下拉式選單可選擇”所有資料庫、系統資料庫、特定資料庫) 9、維護清除工作的設定(可自行調整路徑及選擇時間) 10、完成後在”管理”>”維護計畫”中會產生一個多個子計劃的維護計畫 11、也可在SQL Agent的作業中檢視維護計畫中的相關作業 12、設定完成後，可以執行”從下列步驟啟動作業”來確認能否正常運作 13、確認資料庫備份檔 待備份作業完成後即可至備份路徑確認 (資料庫名稱).bak 的檔案是否已建立 完成!! 參考資料：建立維護計畫、使用維護計畫、維護計畫設計界面

雖然說微軟的應用程式安裝起來總是下一步、下一步就能夠輕鬆完成，但是如果 SQL Server 的安裝也是有這樣的心態的話，可是會吃大虧的，因為裡面包含的細節實在太多了。 想說最近有客戶要請我們幫忙安裝 SQL Server 2012，我就寫一篇文章，也算是提醒自己不要忘掉細節。 在這篇文章的結構上，我分作三個部份： 1、安裝前準備   2、開始安裝流程   3、安裝後檢查 一、安裝前準備 我的系統是 Windows Server 2008 R2 & SQL Server 2012 我準備安裝預設執行個體 (另外，現在 SQL Server 2012 已經支援安裝在 Windows Server 2008 R2 Core SP1 的版本上。 如果想大幅減少 Windows Update 以及攻擊面，這是個很好的選擇。) 1、在 Windows Server 2008 R2 SP1 作業系統的電腦上執行安裝程式，就必須啟用 .NET Framework 3.5 SP1，再安裝 SQL Server 2012 2、是否需要安裝 Application Server 中的 Distributed Transactions Coordinator (通常會使用在 SQL Cluster 上) Distributed Transaction Coordinator 3、不同版本支援的 CPU & Memory 的容量不盡相同，務必要再次確認 4、PowerShell 2.0 為必要條件 (Windows Server 2008 R2 以上都會自動安裝好，如果作業系統非此版本，請再確認要安裝。 5、建議在安裝前先執行過一遍 Windows Update 安裝 SQL Server 2012 軟體硬需求 二、開始安裝流程 我這邊就不贅述每一個步驟，以下我單就其重點進行說明，詳細安裝步驟可參考德瑞克老師所製作的影片 影片：下載與安裝 SQL Server 2012 RTM 中文版本 1、安裝功能選擇 如果只有資料庫讀寫、資料庫備份的需求，其實只要安裝 Database Engine Service 以及 Integration Services 即可。以下就相關的服務進行說明 (1) Replication 基本上這是做 H.A. 或有中央及地方資料庫時才會用到的元件，單一資料庫的話可以不必安裝。 (舉例來說，HQ  的資料庫會需要將一部份的資料 replicate 到 Branch Office，此時就必須使用 replication 的服務) (2) Full-Text and Semantic Extractions for Search 全文檢索的服務，如果應用程式沒有特別需求，也不必安裝，進一步了解請參考連結。 全文檢索概觀 (3) Data Quality Services 這是 SQL Server 2012 的新服務。舉例而言，可以開發一個 third-party 的工具用來檢視某個 table 中欄位的地址資料，如果有比較不符合的部份，經過 DQS scan 之後，就會報錯。 (4) Analysis Services 企業如果有導入 BI 的專案才需使用，否則不用安裝。 (5) SQL Server Data Tools SQL Server Data Tools (它的前身是 Business Intelligence Development Studio ) 整合進 Visual Studio 工具，方便開發者進行更便利的開發。 SSDT 提供了一個 IDE 的環境，主要是給 BI 使用，相關元件有 Analysis Service、Reporting Service、Integration Service)。 舉例而言，如果要自行建立一個 SSIS 的專案，就必須使用這個 tools，安裝時要準備 1G 以上喔。 SSDT 參考文件 (6) Client Tools Connectivity 基本上不用安裝，有相關文件請參考 有關 client tools connectivity 的討論 sql server 2008 R2 的 feature selection 安裝功能至 instance 的步驟  版本及元件參考 (7) Data Quality Client 連線至 Data Quality Server 的工具，類似使用 client 的 SSMS 去連遠端的 SQL Server。 2、定序 這個很重要，選擇之前有一個大原則，就是企業內的定序儘可能的要一致，否則會產生無法預期的查詢錯誤，或是與 tempDB 互動時失敗。 3、驗證 在 Windows Server 2008 R2 的新功能中，有新增「虛擬帳戶」與「受管理的服務帳戶」兩種。在 SQL Server 2012 中，預設是虛擬帳戶作為各個相關服務的執行帳戶 (如下圖)。如果沒有特別需求，建議使用預設值，以提高安全性。   由於 SQL Server 2012 安裝程式細節繁多，這篇文章暫時寫到這邊，待我繼續完成下部。

不知道大家在進立 SQL Server 作業排程時，有無發生以下訊息   我是使用 SQL Server 2005 內建的維護計劃建立排程，完成後發生如上圖的錯誤。另外，我去找對應的工作排程中的步驟與排程，都沒有正確的資料建立   我後來想想，應該是 SSIS 服務並沒有安裝起來。 原因很簡單，雖然維護計劃最後是透過 Jobs 來執行，但是執行步驟裡的定義，其實是由維護計劃精靈去產生一個 SSIS 再交由作業進行執行的，下圖是建立完畢後的圖片：   但是話說回來，SQL Server 在規劃部署的時候，一定要想清楚會用什麼功能。裝太多會影響伺服器效能，裝太少有些功能又無法正常 work，所以囉，一定要想清楚才行。

之前有客戶因為安裝資料庫太分散，導致授權數不足，經過我們的分析之後，其實用現有的授權就足夠了，只需要將分散在各個 VM 中的 SQL Server instance 移除，並將資料庫統一集中即可。 這個想法其實很單純，但沒想到轉移 SharePoint 2003 的資料庫由 A Server to B Server 這麼麻煩。 由於我之前沒有嘗試過在 SharePoint 2003 上轉移 SQL Server 資料庫，所以我先去找了相關文獻，這才發現，在 SharePoint 2003 中，並不是改改 web.config 中的連線字串就結束了。參考連結如下： http://support.microsoft.com/kb/894164 上述技術文件大致上有幾個重要步驟： 1、使用 SharePoint 內建的工具進行備份 2、使用 SSMS 資料庫管理工具進行資料庫的備份 3、使用 SharePoint 組態設定網站，將原先的設定值全部砍掉 (這步驟有相當多細節) 4、還原使用 SharePoint 工具備份的資料 但是我一開始的第一個步驟就失敗了，如下圖： SITEDB (網站相關資料庫) 、 DOCLIB (文件資料庫) 我都成功備份，就 INDEX 備份失敗。我找了老半天找不出原因，但我不打算停止嘗試，我要把 SharePoint 產生的網站砍掉重建  = = 當然，在做之前，因為有 vmware ESXi 的虛擬化環境，所以一定要先做 snapshot ，至少失敗了還可以倒回來。 過程就不多說了，我嘗試的結果就是，SharePoint 所產生的備份檔無法重建網站 Orz… 事件檢視器的產生一堆錯誤 logs 我檢查過資料夾權限、執行 SQL Server 帳戶權限，但就是找不出為何會還原失敗的原因。試了 3、4 個小時後，我覺得這樣不行，趕緊使用 snapshot 還原，並找詢其它可用辦法。 過程中我覺得很納悶，為何組態設定網站的資料庫、內容資料庫都可以由組態網站中進行設定，但 SharePoint 的其它二個資料庫卻沒辦法，相關資料庫一共 4 個如下： PortalSiteName_prof PortalSiteName_serv PortalSiteName_site SharePoint 入口網站 Server 2003 組態資料庫。預設情況下，這個資料庫稱為 SPS_Config_db   我想了想，既然 portal 網站中所有的檔案都沒有連線字串相關資訊，那程式該怎麼知道資料庫是位於哪台 SQL Server ， 如果程式寫死又被 compile 成 .dll 檔的話我就認了。但既然不在檔案裡，那或許是在資料庫中。 或許是我運氣不錯，被我找到在組態資料庫中的 Database 資料庫中的  DatabaseServiceId 中。雖然說是一堆看不懂的數字，但是我們剛剛已經確定其中一個資料庫被我改了 SQL Server 的主機，所以應該是有 2 個一樣的，一個不一樣的字串，我們就 copy 那一個不同的字串，在 paste 到另外 2 個資料庫的欄位即可，如下是完成圖。   終於弄完了，想說下次應該碰不到了吧，但還是寫起來，避免自己忘掉。   可以躺平了……

之前在公司的測試機上的 SQL Server 過期了。                          (我的 SSMS 開不起來了)   也就是一開始我是安裝評估版，如果不趕緊處理，可是會影響正常服務的運作的 (尤其是在 production 的機器) 但是過期後，該如何打入序號呢? 其實步驟相當容易，如下解說： 1、首先在組態工具中點選安裝中心 (因為打上序號和安裝有關，所以選這個工具還挺直覺的)         2、點選維護 → 版本升級         3、在指定 product key 時，點選第 2 個選項 (而我之前是裝免費版本，所以會選第一個)         4、選擇之前安裝的 Instance name ，預設的是 MSSQLSQLSERVER         5、升級前檢查         6、確認升級         7、升級完畢           這邊有件事要特別注意：試用版只能選擇「企業版評估」、「Express」、「Express with Advanced Services」，但是選擇「企業版評估」的朋友們，如果日後升級為正版時，用「標準版」的話，是不被允許的喔。即使是一開始拿標準版來裝試用版時，也會發現只能選擇企業版做試用  orz………….. 這個不是很 ok 的邏輯，千萬不要選錯了，如果選錯了，會遭遇到下圖的窘境。即使想要強制升級，升級跑完後雖然是成功的，但 SSMS 一樣會開不起來喔。

今天一大清晨，致學老師來我們這邊進行最後的效能調校測試，剛好在中間空檔可以請教問題。( 致學老師人真是太好了，每次來做顧問，都一直被我問問題…… ) 今天談到了 SQL Server 如何來使用 Where ，歸納了幾點如下： 1、萬用字元請小心使用  ( 注意別將萬用字元放在搜尋字串的第 1 個字元 ) 首先假設我們的 table 是有建立索引的 舉例來說，使用 where columns like ‘AB%’ ，SQL Server 在接到指令會其實是會轉換成類似  columns >= AB and columns < AC 來進行搜尋，如此一來我們就可使用索引來大幅提故搜尋效率；但是，如果是使用 where columns like ‘%AB’ ， 那就抱歉了， SQL Server 的搜尋方式是 table scan ，有幾筆資料就要搜尋幾次 ( 試想有超過百萬筆資料的話…… ) 2、必須運算的欄位 ( 如果運算邏輯是一致的，請多加運算後欄位，並將該欄位建置索引 ) 同樣舉例來說，我們在資料庫中有一個欄位很常需要做 substring 。 只要一使用 substring 時，就會將該欄位的所有資料，一筆一筆的進行 substring 。 如果每次運算的邏輯是一樣，建議在 insert 資料時，也多 insert 一個運算後的欄位，在將該欄位建立索引，以後如果要搜尋欄位，便可利用索引大幅提升效率。 3、資料庫欄位中如有 status 、 flag  等資料時，擴大判斷數字 這不舉例應該很難理解唄 = = 假設我們有一個 table 是專門存放簡訊收發相關資訊，該 table 有一個欄位是 status ，而 status 有包含 ok (確實收到訊息)、 cn (無訊號)、 ck (使用者簡訊量爆掉) 、cx 、ci 等代表不同種類的回應訊息。 每個種類的回應訊息都有被賦予一個數字，該數字的欄位名稱是 status_number。 重點來了喔………..， 如果數字是連續的 !!!       ok = 1 、 cn = 2 、 …… 依此類推。   試問，如果以後又多了一種回應訊息是代表使用者有收到簡訊，假設是 kk = 6 。 問題來了，管理者想知道所有成功的回應訊息，那不就要 where status_number = 1 or 6 。這代表，table scan 又來了…… 所以可以將成功訊息擴大，例如，成功相關訊息是 1~50 ，失敗相關訊息是 51~100 。這樣搜尋語法可改為 where status_number < 50 ，就可以使用索引了。 ( 如果成功訊息大於 50 種的話，俺也認了………….. )

這次碰到一個無言的問題，害我花了 3 小時才想到解決方法… 先說明我的環境： windows server 2008 R2 SP1 SQL Server 2008 SCOM   當然，安裝 SCOM 前要安裝的相關元件我都知道 (下圖是成功之後的 check prerequisite)   但是，為什麼我的 Reporting Services 安裝一直無法通過檢查。我確定了我的 SQL Server 上有安裝： 1、Reporting Service 2、IIS 3、Reporting Service 已經正常啟動 +  已建立相關資料庫 +  已指定 Web Service 4、已裝上 SP1   其實，我的 SQL Server 是在安裝 SCOM 前就架設好的，那時我還沒安裝 Reporting Service ，就已經升級至 SP1 了的，而我誤以為升級 SP1 時，會連帶安裝所有的元件，其實不���。 所以我升級 SP1 時，其實只有升級到 SQL Engine 的部份，而後來安裝的 Reporting Service 必須重新再升級一次 SP1，這樣問題就解決了。

以下列出臨時可能會需要的系統資訊 1、列出 SQL Server 版本 (2005、2008 ……) SELECT SERVERPROPERTY('productversion')   2、列出 SQL Server 是使用什麼 Service Pack select SERVERPROPERTY ('productlevel')   3、列出 SQL Server 的版本 (標準、企業……) select SERVERPROPERTY ('edition')

以下是我第一次還原資料庫時，碰到的一些狀況，並且和大家分享。 遇到資料庫無法正確還原時，先檢查下列幾項要點： 目的地資料夾是否有權限 (SQL Server Engine 的執行帳戶必須在目的地資料庫有存取權)，記得在該資料夾加入執行 SQL Engine 的帳戶的 NTFS 權限，讓該帳戶有寫入的權限。   來源資料庫的原始位置是否和目的地資料夾相同。舉例來說，我原本在 A 電腦放資料庫在 D:\DATA\test.mdf ，但是在 B 電腦還原至 E:\DATA\test.mdf ，這時候就無法還原囉，錯誤訊息如下圖，此時只要使用 move to 的指令將還原位置變更即可。 Restore Database [your DB] From Disk = N’xxxxxx’ with Move ‘test_data’ to ‘E:\Data\Test.mdf’   另外，要注意Move 後所接的參數，不是來源資料庫的完整位置，而是它在資料庫中的邏輯名而已，如果忘記的話，可使用下面的指令來查詢。 Restore FILELISTONLY From Disk = N’xxxxxx’     資料庫使用權限問題。 如果有新的資料庫層級的使用者，要記得加入使用者的權限。(當然，如果該帳戶是 sysadmin 就沒有這個問題)