[MS SQL Server] 還原資料庫備份到同一台主機

目前工作環境的MS SQL Server有做
每小時 交易記錄備份
每天 差異備份
每週 完整備份

先前有測試過將資料庫直接恢復到特定時間是沒有問題的，
不過今天卻小小卡關一下，因為常見的是原資料庫利用備份檔還原，
或者在其它資料庫主機做同樣資料庫名稱的備份還原動作！

但是如果是在同一台主機，要將原本備份的資料還原到另外一個資料庫呢？
假設原本資料庫叫DB_AA，想要在同主機上新建一個DB_BB，
並將之前DB_AA備份的檔案回存到DB_BB上

今天遇到的錯誤訊息是
"備份組包含現有的 'XXX' 資料庫以外的資料庫備份。"

[T-SQL] 表格欄位別名

我們常在抓取資料時，會針對欄位作別名的動作，
特別是有計算欄位的部分，常見的用法如下

select CorpNo as FieldNa
from Company

select FieldNa=CorpNo
from Company

今天看到一篇分享文，覺得這方法也不錯，順便筆記一下

select *
from (select CorpNo
           from Company) T (FieldNa)

參考自
[SQL] 衍伸資料表欄位別名

[PHP] PDO 連接 MSSQL EXEC Stored Proc無回傳值

萬事起頭難，最近一直在摸索php連接資料庫抓取資料的用法，

心想竟然要學，那就要用最新的PDO，但相對相關資訊較少，

昨天遇到一個問題，特別記錄一下。

[T-SQL] 月份第一天、月份最後一天

--月份第一天
select N'月份第一天'=DATEADD(MM,DATEDIFF(MM,0,GETDATE()),0) 

--月份最後一天
select N'月份最後一天'=DATEADD(DD,-1,DATEADD(MM,DATEDIFF(MM,0,GETDATE())+1,0))


--RESULT
月份第一天
-----------------------
2015-01-01 00:00:00.000


月份最後一天
-----------------------
2015-01-31 00:00:00.000

[MS SQL] MERGE 同步處理兩個資料表

今天研究了一下MS SQL SERVER 2008才新增的MERGE功能，
主要簡化了比對兩個表格差異處時的新增、修改、刪除動作，
以前需要分開撰寫INSERT、UPDATE、DELETE語法，
透過MERGE比對資料，根據設定更新資料！

[MS SQL] 在VIEW使用ORDER BY

透過VIEW查詢Table可以節省很多重複語法編寫的問題，
不過如果要在VIEW中使用ORDER BY，
建立VIEW會出現 下列錯問訊息
除非同時指定了 TOP 或 FOR XML，否則 ORDER BY 子句在檢視表、內嵌函數、衍生資料表、子查詢及通用資料表運算式中均為無效。

這時只要在SELECT後面加上TOP (100) PERCENT，即可排除錯誤！

SELECT TOP (100) PERCENT *
FROM TABLENAME
ORDER BY FIELDNAME

MSDN-CREATE VIEW (Transact-SQL)

[MS SQL] XML資料型態

之前寫過一篇 MS SQL 利用Trigger紀錄Table異動資料，
雖然可以記錄每次異動的資料，但缺點就是每個table欄位不同，
就要將每個要記錄的table分開新建tablex來記錄，感覺似乎也不太適合大量使用，
後來有用到XML STUFF，發現XML格式也可適用此問題，來達到記錄異動資料的需求，
好處就是不用考慮每個表格欄位數的多寡，一個欄位就可以達到記錄目的！

[MS SQL] 查詢包含特定字串之Proc、Function、View

--查詢包含特定字串之Proc、Function
DECLARE @TmpStr NVARCHAR(MAX)='Factory=@CorpNo'

SELECT ROUTINE_SCHEMA, ROUTINE_NAME, ROUTINE_TYPE, ROUTINE_DEFINITION
FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_DEFINITION LIKE '%'+@TmpStr+'%'
AND ROUTINE_TYPE ROUTINE_TYPE IN ('PROCEDURE','FUNCTION')

--查詢包含特定字串之View
SELECT TABLE_NAME,VIEW_DEFINITION
FROM INFORMATION_SCHEMA.VIEWS
WHERE VIEW_DEFINITION LIKE '%'+@TmpStr+'%'

參考自-青年廣論研討班-如何在SQL Server 資料庫中 找出符合你的關鍵字的Store Procedure

[Delphi][MS SQL] HOST_NAME()抓取主機名稱錯誤

前幾天寫了一個當登入失敗時，紀錄登入者的IP、主機名稱、時間...等，
今天去看LOG檔才發現，怎麼主機名稱都一樣 ，
明明在MS SQL Server Management Studio執行時都可以正常抓取，
後來才發現在Delphi內的ADO Connection設定連線時，
會將連線主機的名稱寫在Connection內的Workstation中，
而當我們使用T-SQL執行HOST_NAME()時，
反而直接抓取此參數資料，而非透過Server查詢，

[MS SQL] 查詢TABLE相關資訊

MS SQL Server內建sp_name可查詢表格的結構，
但是如果表格內有欄位描述時，使用sp_name是無法顯示出來，

[MS SQL] 搬移MS SQL Server 資料庫MDF檔

因為沒有經驗所以當初MS SQL Server上線時，皆以預設值安裝，
也就是說，資料庫檔MDF和交易記錄檔LOG，皆存放於系統槽內，
最近發現C槽的空間已經快要被用滿了，後來一查才發現，
原來是資料庫MDF檔，隨著上線時間時間逐漸膨脹，
今天以卸離、添加的方式搬移資料庫檔案，以釋放系統槽空間，步驟相當簡單。

[MS SQL] 修改使用中資料庫的定序

--修改前將資料庫設定為單一使用者模式(SINGLE_USER)
ALTER DATABASE DBName 
SET SINGLE_USER 
WITH ROLLBACK IMMEDIATE

--修改資料庫定序
ALTER DATABASE DBName COLLATE Chinese_PRC_Stroke_CI_AS

--修改後將資料庫改回允許有權限之帳號存取模式(MULTI_USER) 
ALTER DATABASE DBName 
SET MULTI_USER

參考自
MSDN-ALTER DATABASE SET 選項 (Transact-SQL)
MSDN-將資料庫設定為單一使用者模式

[MS SQL] 字串(前中後)含有空白字元問題 LEN()、DATALENGTH()

今天在做字串資料處理時，跑處來的結果一直有問題，後來逐步測試才發現，
寫了那麼久的程式，我竟然沒有注意到，如果使用LEN()函數，
空白字元如果位於字串的後方，計算字串長度時，是會省略不計算的，

select '前面空白'=CONVERT(VARCHAR,LEN(' 123ab')),
  '中間空白'=CONVERT(VARCHAR,LEN('12 3ab')),
  '後面空白'=CONVERT(VARCHAR,LEN('123ab '))

/*----------------------------------結果----------------------------------*/
前面空白                           中間空白                           後面空白
------------------------------ ------------------------------ ------------------------------
6                              6                              5

這次需要改用DATALENGTH()計算，才可以正確抓取字串長度！

select '前面空白'=CONVERT(VARCHAR,DATALENGTH(' 123ab')),
  '中間空白'=CONVERT(VARCHAR,DATALENGTH('12 3ab')),
  '後面空白'=CONVERT(VARCHAR,DATALENGTH('123ab '))

/*----------------------------------結果----------------------------------*/
前面空白                           中間空白                           後面空白
------------------------------ ------------------------------ ------------------------------
6                              6                              6

但是有一點需要注意，DATALENGTH()是計算位元組，
當資料型態是NCHAR()、NVARCHAR()時，取得的長度會不一樣！

select 'VARCHAR'=CONVERT(VARCHAR,DATALENGTH(CONVERT(VARCHAR,'2238# '))),
  'NVARCHAR'=CONVERT(VARCHAR,DATALENGTH(CONVERT(NVARCHAR,'2238# '))),
  'NCHAR'=CONVERT(VARCHAR,DATALENGTH(CONVERT(NCHAR,'2238# ')))

/*----------------------------------結果----------------------------------*/
VARCHAR                        NVARCHAR                       NCHAR
------------------------------ ------------------------------ ------------------------------
6                              12                             60

[MS SQL] 快速刪除資料庫中所有表格內資料

最近公司SQL Server在做移轉主機動作，因為牽涉到降版(2008 R2 Express -> 2008)的問題，
無法直接備份還原資料庫到新主機，所以一直在測試複製數據的方式，
一開始很笨的每次都將資料庫整個刪掉再透過語法重新建立表格，
因為懶的慢慢測試，後來還是google到了方法～

MS SQL 使用sp_send_dbmail發送-HTML格式郵件

透過MS SQL的sp_send_dbmail可直接將查詢出的資料以HTML郵件格式發出
範例如下

--建立測試表格/資料
IF (select OBJECT_ID('tempdb..#Mail_Tmp')) IS NOT NULL DROP TABLE #Mail_Tmp
CREATE TABLE #Mail_Tmp (AccNo VARCHAR(10),
      EMail VARCHAR(MAX))
 
INSERT INTO #Mail_Tmp VALUES ('dean','dean@gmail.com')
INSERT INTO #Mail_Tmp VALUES ('phoebe','phoebe@gmail.com')

--抓取資料
IF EXISTS (select * from #Mail_Tmp)
BEGIN
 DECLARE @tableHTML  NVARCHAR(MAX) ;
 SET @tableHTML =
  N'<H1>用戶郵件帳號明細</H1>' +
  N'<table border="1">' +
  N'<th>使用者</th>'+
  N'<th>email</th>'+  
  N'</tr>' +
  CAST ( (select td=AccNo,'',
      td=EMail,''
    from #Mail_Tmp
      FOR XML PATH('tr'), TYPE
  ) AS NVARCHAR(MAX) ) +
  N'</table>'+
  N'此郵件由系統發出'
 
 EXEC msdb.dbo.sp_send_dbmail
  @profile_name='ERP_DB',--Database Mail設定檔名稱
  @recipients='dean@gmail.com',--收件者
  @copy_recipients='',--CC
  @subject='用戶郵件帳號明細',--主旨
  @body=@tableHTML,
  @body_format=HTML--使用HTML格式
END

收到郵件結果如下

參考自 MSDN-sp_send_dbmail

MS SQL Server 使用sp_send_dbmail 發送查詢結果郵件

無法查看此摘要。請 按這裡查看文章。

stored procedure IF ELSE SELECT INTO 出現 [訊息2714 層級16] 錯誤訊息

--範例
--建立暫存表
IF UPPER(@ServerNa) = 'SERVAER_A'
BEGIN
 IF (SELECT OBJECT_ID('tempdb..#STUL')) IS NOT NULL DROP TABLE #STUL
 select DISTINCT CorpNo,IONo into #STUL
 from [SERVAER_A].InnovaERP.dbo.SysTableUpdateLog
 where PrgType=@PrgType
END
ELSE IF UPPER(@ServerNa) = 'SERVAER_B'
BEGIN
 IF (SELECT OBJECT_ID('tempdb..#STUL')) IS NOT NULL DROP TABLE #STUL
 select DISTINCT CorpNo,IONo into #STUL
 from [SERVAER_B].InnovaERP.dbo.SysTableUpdateLog
 where PrgType=@PrgType
END

--錯誤訊息
訊息 2714，層級 16，狀態 1，程序 SP_XXX，行 24
資料庫中已經有一個名為 '#STUL' 的物件。

通常在不同的IF ELSE判斷子句中，應該可以視為獨立的事件，
不知為什麼MS SQL在stored procedure中，
使用IF ELSE針對不同的條件SELECT INTO到同樣的表格，會有這樣的錯誤訊息，
只好改寫語法，於判斷式前先將暫存表建立，再於子句中INSERT INTO即可

--建立暫存表
IF (SELECT OBJECT_ID('tempdb..#STUL')) IS NOT NULL DROP TABLE #STUL
SELECT TOP 1 CorpNo,IONo
  INTO #STUL
FROM SysTableUpdateLog

IF UPPER(@ServerNa) = 'SERVAER_A'
BEGIN
 INSERT INTO #STUL
 select DISTINCT CorpNo,IONo
 from [SERVAER_A].InnovaERP.dbo.SysTableUpdateLog
 where PrgType=@PrgType
END
ELSE IF UPPER(@ServerNa) = 'SERVAER_B'
BEGIN
 INSERT INTO #STUL 
 select DISTINCT CorpNo,IONo
 from [SERVAER_B].InnovaERP.dbo.SysTableUpdateLog
 where PrgType=@PrgType
END

T-SQL 比較 ROW_NUMBER()、RANK()、DENSE_RANK()、NTILE() 次序函數差異

MS SQL中提供ROW_NUMBER()、RANK()、DENSE_RANK()、NTILE()四個次序函數供資料排序使用
簡單說明如下：
ROW_NUMBER()：資料列以流水編排序
RANK()：資料列依ORDER BY欄位排名
DENSE_RANK()：同RANK()，但不跳號
NTILE()：資料列依傳入參數作為分組組數分組排名

tempdb 置於RAMDISK提升速度

現在電腦的記憶體價格已不是高不可攀，
新的電腦隨便也裝個8G的RAM來用用，
一部份還可以切做RAMDISK做暫存檔存取使用，
公司SQL主機已經五年了，雖然還沒有故障的跡象，
不過最近還是請購了一台新的，將舊有的退下來當作備援機，
公司小，很多事情都得自己摸索，調教SQL Server也不例外，
這次更換主機，特地將RAM裝到16G，
主要想要將其中的12G做RMADISK存放tempdb使用，
想要提升IO的速度，不過這樣也僅限於tempdb的部分，
之前Google網路上也沒有太多相關的文章，
所以特別寫了這篇記錄一下安裝過程需注意的部分，

1.RAMDISK格式需為NTFS，使用FAT32 SQL Server會啟動失敗
2.重開機tempdb因存於RAMDISK會被清空重新初始化檔案，不影響使用
3.tempdb數量與CPU核心數一致，4核心即切為4個mdf

目前主機已上線一個月，使用一切正常，
不過建立暫存表的速度提升相當多，將近縮短了一倍的時間！

Stored Procedure 解密

公司同事可能是怕人笑他寫的太差所以不給看...(誤
所以都將自己寫的stored procedure
使用WITH ENCRYPTION進行加密，
導致休假時根本無法幫他排除問題，
後來參考了RiCo大的文章，
安裝SQL Decryptor直接登入後，即可察看加密過的內容

軟體備份

參考自：RiCo技術農場-[SQL SERVER][Tools] SQL 解密軟體-Free