Pages

2016-04-10

資料庫好幫手SqlDbUtility工具

SQL Server雖然已是一個很友善操作的資料庫(以下簡稱DB),不過其管理工具SQL Server Management Studio(簡稱SSMS)裏的某些Task操作流程過於冗長,我將其比較常用的功能寫成一個Console獨立程式,它可被第三方陰影備份工具或自動化批次檔呼叫使用,比起受限在SSMS的排程工具,多了一些寬廣的選擇。

App Usage

以下介紹它的指令功能 (請使用*.bat批次檔來執行它,省得每次重複打字)

DB終止使用中連線Kill
SSMS裏很多動作在未強制踢出使用連線,是無法進行操作的,此工具會將該DB所有的SPID (Server Process ID)強制踢除,以進行後續工作。
App.exe -d tempdb -x killspid

指定Config內ConnectionString的Key值
為了在同一個bat批次檔執行不同的DB連線,可以在App.config內設定不同的connectionStrings值,再利用-k參數來指定DB連線字串的KeyName,預設值為Default。
App.exe -d tempdb -x killspid -k "Local"
ConnString


DB離線Offline
要替換不同版本的DB內容,不須Detach/Attach資料庫,只需把DB執行離線Offline動作,其MDF/LDF資料檔就不會呈現In Used,可以直接蓋檔。然而,在SSMO下若該DB有人連線使用中,離線指令會失敗,因此本工具會先踢出該DB連線ID, 再執行離線指令。
App.exe -d tempdb -x offline

DB上線Online
未online的DB無法後續操作,因此替換MDF檔案後可啟用。
App.exe -d tempdb -x online

DB備份Backup
自從知道offline DB能換MDF檔後,已很少使用Backup指令了。輸出檔名支援NOW時間格式字串。若未指明路徑,會在Working Directory下建立以DB+日期為名的備份檔。
App.exe -d tempdb -x backup -f "D:\db_{0:yyyyMMddHHmmss}.bak"

DB還原Restore
此指令是以覆蓋Replace方式還原DB,其bak中記錄的MDF/LDF路徑,因此要注意這些細節。若是重要的DB還原,還是透過SSMS工具比較清楚安全。
App.exe -d tempdb -x restore -f "D:\db_{0:yyyyMMddHHmmss}.bak"

DB壓縮Shrink
對DB進行最小資料壓縮,保留可用空間0%。
App.exe -d tempdb -x shrinkdb

DB清空表格內資料Truncate
清除DB內所有Table裏的資料內容,個人喜歡用Truncate Table指令,因為它會Reseed Identity欄位。單一表格時清除資料,可能因為正規化Constraint限制而無法執行,因此提供了一道清除DB內所有表格資料的強制指令,它會避開Constraint/Trigger等限制。
App.exe -d tempdb -x truncateTable -t TABLE_NAME  // 依正規化清除單表資料
App.exe -d tempdb -x truncateTable -t *   // 避開正規化清除所有表格資料


SQL Script檔案執行
執行SQL Script檔案內容,不回傳結果。
App.exe -d tempdb -x sqlExec -f "D:\in.sql"

SQL Script檔案查詢
執行SQL Script檔案內容,並將查詢結果顯示console上,也可以把結果輸出至CSV檔案。
App.exe -d tempdb -x sqlQuery -f "D:\in.sql"
App.exe -d tempdb -x sqlQuery -f "D:\in.sql" > "D:\out.csv"

image

清除表格/欄位的註解資訊Description
Table欄位有附上註解是好事,尤其在EF架構裏能被引入,對於開發階段很有幫助。若在發行Client單機板程序時,可以清除這些註記,達到保護用途。
App.exe -d tempdb -x clearDesc

重置表格內Identity欄位值為目前最大值
Table的Identity欄位值若想重置(Reseed)為目前欄位的最大值,可以使用這指令來重設,例如Seed值為1000,但實際最大值為50,經過重置後Seed值會變成50。
App.exe -d tempdb -x identMax -t "Table1" -c "Column1"

刪除表格 DropTable

支援刪除多個Table表格(支援*號),可鎖定在某schema owner上。
App.exe -d tempdb -x dropTable -t "a*" [-s "SchemaName"]
Changed database context to 'tempdb'.
DROP TABLE [dbo].[a1]... [OK]
DROP TABLE [dbo].[a2]... [OK]
DROP TABLE [dbo].[a3]... [OK]
Total 3 tables has dropped.


修改表格Schema擁有人
修改多個Table表格(支援*號)的Schema Owner,方便調測用戶權限。
App.exe -d tempdb –x altTableSchema -t "Table1*" -s "OldName,NewName"

取得目前Client連線數量

App.exe -x cltConnCount
3

取得目前Client連線狀態
App.exe -x cltConnStatus
Status      Count    AppName
sleeping    1        Microsoft SQL Server Management Studio
sleeping    1        Microsoft SQL Server Management Studio - Query
running     1        SqlDbUtility


查看SQL Server引擎版本

App.exe -x version
Microsoft SQL Server 2014 - 12.0.4100.1 (X64)
        Apr 20 2015 17:29:27
        Copyright (c) Microsoft Corporation
        Enterprise Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1) (Hypervisor)


檢查資料庫完整性CheckDb
檢查訊息可在Console呈現,也可寫入文字檔案。
App.exe -d tempdb -x checkDb > “D:\health.txt”



更新記錄:

2016/03/26 Version History:

  • 新增「刪除表格DropTable、修改表格Schema」等2道Table操作指令。
  • 新增「讀取Client連線數量/狀態, 查看SQL Server版本, 檢查CheckDb完整性」等4道DB資訊指令。
  • 調整指令說明及語法範例的顯示,指令改斷義首字大寫說明,但大小寫不影響執行結果。

    2016/01/05 Version History:

  • 調整執行指令的參數為x,取代之前的c值。
  • 調整命令「shink」為「shinkdb」。
  • 調整c參數意義為Column Name。
  • 調整restore命令,加入執行前先踢除使用中連線,並能讀取日期格式化字串檔名。
  • 新增指定app.config內的connectioin string鍵值(-k參數)。
  • 新增s參數為讀取相關string字串(未來用途)
  • 新增重置Reseed表格Identity值為目前的最大值的新命令(identmax)。 

    2014/08/23 Version History:

  • 初版Release,下載含本文Help說明件.
  • 發佈前因停電導致程式源檔損毀,無奈下重寫主程序,也相較之前版本增加了4道指令。

     

    程式下載:(.NET 4.0 Required, Portable, Size: 140KB)
    [百度雲盤: SqlDbUtility_20160326.rar]

  • Post a Comment