SQL Server 利用交易紀錄拯救資料

今天出了一個大包,大概是從寫程式以來最大的問題了。

查詢備份檔案

要利用交易紀錄拯救資料,首先必須要有一個完整的備份 bak ,再來需要有從完整備份開始的不中斷交易紀錄,這樣就可以利用來拯救交易紀錄期間所有資料庫異動。

SELECT 
a.[database_name] as '資料庫名稱',
f.physical_device_name as '備份位置',
CASE a.[type]
WHEN 'D' THEN N'資料庫'
WHEN 'I' THEN N'差異資料庫'
WHEN 'L' THEN N'紀錄'
WHEN 'F' THEN N'檔案或檔案群組'
WHEN 'G' THEN N'差異檔案'
WHEN 'P' THEN N'部分'
WHEN 'Q' THEN N'差異部分'
ELSE N'NULL'
END as '備份類型',
a.[name] as '備份組的名稱',
a.[first_lsn] as '備份組中第一個或最舊的記錄序號',
a.[last_lsn] as '備份組之後下一個記錄的記錄序號',
a.[database_backup_lsn] as '最近的完整資料庫備份之記錄序號',
a.[differential_base_lsn] as '差異備份的基底 LSN',
a.[backup_finish_date] as '備份作業完成的日期和時間',
a.[backup_size] as '備份組的大小 (以位元組為單位)'

FROM
msdb.dbo.backupmediafamily f
INNER JOIN msdb..backupset a ON f.media_set_id = f.media_set_id
INNER JOIN master..sysdatabases b ON
a.database_name COLLATE DATABASE_DEFAULT =
b.name COLLATE DATABASE_DEFAULT
where
a.database_name = 'DatabaseName'
ORDER BY
a.database_name, a.backup_finish_date

在結果之中會顯示每一次備份的紀錄,還有記錄到的紀錄序號,紀錄序號一定要連續不中斷的,一中斷只能說事情大條了。

還原資料庫

接下來我們利用 bak 以及  trn 檔案來還原資料庫,先還原 bak 的完整備份

image

image

先別急著按下確定,這時候我們點下指令碼,把介面替你做掉的 T-SQL 撈出來看看,應該會類似下面這段。

RESTORE DATABASE [Test20120401] 
FROM DISK = N'C:2012_20120309.bak' WITH FILE = 1,
MOVE N'test2012' TO N'C:Program FilesMicrosoft SQL ServerMSSQL10_50.MSSQLSERVERMSSQLDATATest20120401.mdf',
MOVE N'test2012_log' TO N'C:Program FilesMicrosoft SQL ServerMSSQL10_50.MSSQLSERVERMSSQLDATATest20120401_1.ldf',
NORECOVERY, NOUNLOAD, STATS = 10
GO

接下來還原 trn 的部分
image
選擇至某個時間點一樣點下指令碼,接下來讓兩段指令碼合併

RESTORE DATABASE [Test2012] 
FROM DISK = N'C:2012_20120309.bak' WITH FILE = 1,
MOVE N'test2012' TO N'C:Program FilesMicrosoft SQL ServerMSSQL10_50.MSSQLSERVERMSSQLDATATest20120401.mdf',
MOVE N'test2012_log' TO N'C:Program FilesMicrosoft SQL ServerMSSQL10_50.MSSQLSERVERMSSQLDATATest20120401_1.ldf',
NORECOVERY, NOUNLOAD, STATS = 10
GO
RESTORE LOG [Test2012]
FROM DISK = N'C:2012_20120309120405_190000_2012_TRN.trn' WITH FILE = 1,
NORECOVERY, NOUNLOAD, STATS = 10,
STOPAT = N'2012-04-03T22:40:00'
GO

藉由這種方式,我們可以更改變數還有還原時間點還原出不同時間點的資料庫

image

比較資料庫差異性

再來就是利用工具比較還原的資料庫,我們就可以找到每一段區間中間的變動,新增或是刪除的資料列。在這邊我用的工具是 Visual Studio 的 Data Compare

Data > Data Compare > New Data Comparison

image

在這邊的可以選擇來源資料庫跟比較資料庫,Only in Source 或是 Only in Target 也可以單純過濾說被刪除/被新增的資料,選擇完比較的兩邊之後就可以直接開始比較的動作。

image

可以看到我這邊指定了 Only in Source ,各有兩個 Table 少了 20 跟 42 筆資料,點到那一個 Table 的時候下方就會列出差異性的資料,或是選擇上方 Export To Editor 可以匯出將資料塞回 Table 的 T-SQL

image

總結

當然系統安安穩穩的不需要做還原最好,但是誰也不知道哪一天會出大災難,這次的事件真的是學了一次教訓。

EntityFramework 在比較 null 跟 int 的差異性

如果說今天有一個欄位在資料庫中被設計成可為 null 的 int ,在使用 Linq 語法上會變得比較麻煩。

  • EntityFramework 4.3.1

資料庫的設計

dbo.category

Id Name ParentId
1 Computer NULL
2 TV NULL
3 Laptop 1
4 Desktop 1

在這邊的需求是當參數 ParentId 傳進來的的時候,要能夠找到對應的資料。

利用 == 比較子

public ViewResult Index(int? parentId)
{
var categories = db.Categories
.Where(it => it.ParentId == parentId);
return View(categories);
}

在這邊比較的時候會產生一個問題,EntityFramework 在解析 Linq 語法成為 T-SQl 的時候會將 == 解析為 = ,所以當傳入的 parentId 為 null 的時候,這邊比較的語法會是

WHERE ParentId = NULL

null 的比較這樣是錯誤的,要找到 null 的資料應該是利用 is null 來當條件。

  • 傳入資料是 null > 沒有找到 ParentId = null 資料
  • 傳入資料不是 null > 可以正確找到資料

利用 Equals 比較子

public ViewResult Index(int? parentId)
{
var categories = db.Categories
.Where(it => it.ParentId.Equals(parentId));
return View(categories);
}

如果是傳入為 null 的時候也要能比較出 null 的資料,這時候用 Equals 當作運算子,則在解析成為 T-SQL 的時候成為

WHERE ParentId IS NULL

這樣就可以比較 null 的資料了,但是問題並非這麼單純,因為當傳入參數不為 null 的時候,會產生一個錯誤

Unable to create a constant value of type 'System.Object'. Only primitive 
types or enumeration types are supported in this context.

  • 傳入資料是 null > 可以正確找到資料
  • 傳入資料不是 null > 產生 Unable to create a constant value of type ‘System.Object’ Exception

整合兩邊的方法

在這個情況之下這個查詢勢必需要先判斷再送進 SQL 查詢的動作了

public ViewResult Index(int? parentId)
{
if (parentId.HasValue)
{
var categories = db.Categories
.Where(it => it.ParentId == parentId)
.ToList();
}
else
{
var categories = db.Categories
.Where(it => it.ParentId.Equals(parentId))
.ToList();
}
return View(categories);
}

先判斷傳入參數是否為 null 再根據不同情況做查詢

另一種選擇的解決方法

或著是不想要寫兩段 Linq 的也可以考慮

public ViewResult Index(int? parentId = 0)
{
var categories = db.Categories
.Where(it => (it.ParentId.HasValue ? it.ParentId : 0) == parentId)
.ToList();

return View(categories);
}

在這邊的三元替換轉換成 T-SQL 會是

WHERE (CASE WHEN (ParentId IS NOT NULL) THEN ParentId ELSE 0 END) = @p__linq__0


differences between int32.Equals int? == int? for linq

SQL Schema 文件產生器 – Script_to_generate_DB_Document

在專案的時候,常常會需要交付一些 schema 的文件,包括備註還有資料型態之類的,之前都是有手動去整理文件,很浪費時間又沒有意義,而且如果欄位長度或是名稱有修改也沒有辦法整合,手動去整理文件一直是我覺得很麻煩的一塊。如果說有辦法在DB建立好的時候,文件就一併產生了,這不是很令人開心的一件事嗎。

Script_to_generate_DB_Document

在 CodeProject 找到 Script_to_generate_DB_Document 這個工具,他可以查詢資料庫直接輸出 Html 頁面。

image

SQL 裡面似乎沒有預設對 Table 註解的欄位,他是抓取 Table 擴充屬性裡面的 MS_Description 這個自訂欄位

image

雖然說這個工具只有產生 Html 的格式,不過可以參考一些抓取屬性的語法,改寫成輸出 Word 文件或是其他必須交付的格式。

Reference

Document SQL Server 2000/2005 Database

http://www.codeproject.com/KB/database/SQL_DB_DOCUMENTATION.aspx?msg=2424192

Datetime Format in T-SQL

在寫 T-SQL 的時候常常用到日期轉換的部分,格式一大堆。我個人偏好的yyyy-MM-dd也不知道為什麼總是被網路上眾多文章排擠,找了半天又不想用字串去拆,或是把時間部分拿掉,就是想要他一出來就是我要的樣子。

convert(nvarchar , getdate(), FormatCode)

後來終於忍不住自己用 T-SQL 產出代碼編號跟結果的 Table 。

0    08 26 2011  4:24PM
1 08/26/11
2 11.08.26
3 26/08/11
4 26.08.11
5 26-08-11
6 26 08 11
7 08 26, 11
8 16:24:18
9 08 26 2011 4:24:18:313PM
10 08-26-11
11 11/08/26
12 110826
13 26 08 2011 16:24:18:313
14 16:24:18:313
20 2011-08-26 16:24:18
21 2011-08-26 16:24:18.313
22 08/26/11 4:24:18 PM
23 2011-08-26
24 16:24:18
25 2011-08-26 16:24:18.313
100 08 26 2011 4:24PM
101 08/26/2011
102 2011.08.26
103 26/08/2011
104 26.08.2011
105 26-08-2011
106 26 08 2011
107 08 26, 2011
108 16:24:18
109 08 26 2011 4:24:18:330PM
110 08-26-2011
111 2011/08/26
112 20110826
113 26 08 2011 16:24:18:330
114 16:24:18:330
120 2011-08-26 16:24:18
121 2011-08-26 16:24:18.330
126 2011-08-26T16:24:18.330
127 2011-08-26T16:24:18.330
130 27 ????? 1432 4:24:18:330PM
131 27/09/1432 4:24:18:330PM

下面是用來產出 Table 的 T-SQL (執行環境是 sql2005)

DECLARE @TmpTable TABLE (
number int,
result varchar(100)
)
declare @start int
declare @end int
set @start = 0
set @end = 200
while @start < @end
begin
BEGIN try
declare @result nvarchar(100)
select @result = convert(nvarchar , getdate(), @start)
insert @TmpTable(number,result) values(@start,@result)
end try
Begin Catch
End Catch
set @start = @start + 1
end
select * from @TmpTable

Reference

建立#TempTable與Declare @TempTable有何差別

http://social.msdn.microsoft.com/Forums/zh-TW/240/thread/850f5bf2-79b5-4f32-ba40-0bba2db1e929

How to format datetime & date in Sql Server 2005

http://anubhavg.wordpress.com/2009/06/11/how-to-format-datetime-date-in-sql-server-2005/