Sao lưu tất cả database của SQL Server

Đôi khi nhiều thứ dường như có vẻ phức tạp thì lại đơn giản hơn những gì bạn nghĩ và đây là sức mạnh của việc sử dụng câu lệnh T-SQL để thực hiện những công việc lặp đi lặp lại. Một trong những công việc này có lẽ là việc backup tất cả các cơ sở dữ liệu...

Đôi khi nhiều thứ dường như có vẻ phức tạp thì lại đơn giản hơn những gì bạn nghĩ và đây là sức mạnh của việc sử dụng câu lệnh T-SQL để thực hiện những công việc lặp đi lặp lại. Một trong những công việc này có lẽ là việc backup tất cả các cơ sở dữ liệu trong server của bạn. Điều này không có gì lớn nếu như bạn chỉ có một vài cơ sở dữ liệu, nhưng có những server chứa tới hàng trăm cơ sở dữ liệu trên một instance của SQL Server thì vấn đề bắt đầu nảy sinh. Bạn có thể sử dụng tiện ích Enterprise Manager để backup các cơ sở dữ liệu, hoặc thậm chí dùng Maintenance Plans để lên kế hoạch bảo trì, songT-SQL vẫn cho phép bạn thực hiện dễ dàng và nhanh chóng hơn nhiều.

Với việc sử dụng của T-SQL bạn có thể tạo ra các lệnh dự phòng của bạn và với việc sử dụng con trỏ, bạn có thể trỏ qua tất cả các cơ sở dữ liệu của bạn để backup chúng từng cái một. Đây là một quá trình thẳng tiến và bạn chỉ cần một số ít các lệnh để làm điều này.

Đây là script cho phép bạn sao lưu mỗi cơ sở dữ liệu trong instance SQL Server của bạn. Bạn cần thay đổi biến @path để dẫn tới thư mục dự phòng phù hợp và các tập tin sao lưu sẽ đưa vào tên có dạng "DBnameYYYDDMM.BAK".

DECLARE @name VARCHAR(50) -- database name  
DECLARE @path VARCHAR(256) -- path for backup files  
DECLARE @fileName VARCHAR(256) -- filename for backup  
DECLARE @fileDate VARCHAR(20) -- used for file name 
SET @path = 'C:\Backup\'  
SELECT @fileDate = CONVERT(VARCHAR(20),GETDATE(),112) 
DECLARE db_cursor CURSOR FOR  
SELECT name 
FROM master.dbo.sysdatabases 
WHERE name NOT IN ('master','model','msdb','tempdb')  
OPEN db_cursor   
FETCH NEXT FROM db_cursor INTO @name   
WHILE @@FETCH_STATUS = 0   
BEGIN   
       SET @fileName = @path + @name + '_' + @fileDate + '.BAK'  
       BACKUP DATABASE @name TO DISK = @fileName  
       FETCH NEXT FROM db_cursor INTO @name   
END   
CLOSE db_cursor   
DEALLOCATE db_cursor
Trong script này chúng ta bỏ qua các cơ sở dữ liệu hệ thống, nhưng những cơ sở dữ liệu này cũng có thể đưa vào dễ dàng như vậy. Bạn cũng có thể thay đổi scropt này thành một thủ tục được lưu trữ và truyền vào tên cơ sở dữ liệu hoặc nếu để là NULL thì nó sẽ sao lưu tất cả các cơ sở dữ liệu.

Thêm Dữ Liệu Mới Vào Bảng Dùng LEFT JOIN

Giả sử bạn có hai bảng TableA và TableB có cùng cấu trúc gồm 3 cột như sau: ID int primary key, Ten nvarchar(100), Diachi nvarchar(200); Giờ bạn muốn INSERT dữ liệu vào bảng TableA từ bảng TableB cho các dòng vốn chưa có trong bảng TableA. Bạn có thể làm thế này:

INSERT dbo.TableA(ID, Ten, Diachi)
SELECT b.ID, b.Ten, b.Diachi
FROM TableB b
LEFT JOIN TableA a ON a.ID = b.ID
WHERE a.ID IS NULL
Giải thích: Câu lệnh SELECT dùng LEFT JOIN từ TableB tới TableA với điều kiện WHERE a.ID IS NULL sẽ lọc ra các bản ghi trong TableB mà không có trong TableA. Kết quả của lệnh SELECT này được dùng để INSERT vào TableA. Vì SQL Server thực hiện câu lệnh theo tập, nên lệnh SELECT chỉ được thực hiện 1 lần và các bản ghi tìm được sẽ được dùng cho INSERT (không phải là SELECT được 1 bản ghi rồi INSERT, rồi quay lại SELECT tiếp bản ghi thứ hai rồi lại INSERT…).

Kiểm Tra Bản Ghi Tồn Tại Với IF EXISTS trong MSSQL

Trong một thủ tục, bạn muốn kiểm tra xem có bản ghi nào thỏa mãn một số điều kiện nhất định trong bảng hay không, và rẽ nhánh chương trình tùy theo kết quả kiểm tra. Bạn hãy làm thế này:

IF EXISTS(SELECT 1 FROM TblName WHERE ...)
BEGIN
...
END
ELSE
BEGIN
...
END
Đây là cách làm tối ưu, vì đoạn lệnh “EXISTS(SELECT 1 FROM TblName WHERE …)” sẽ dừng lại ngay sau khi tìm được bản ghi đầu tiên; “SELECT 1″ cũng tránh cho hệ thống khỏi phải đọc dữ liệu từ bảng. Một cách khác có thể làm là:
SELECT TOP 1 1 FROM TblName WHERE...
IF @@ROWCOUNT > 0
BEGIN
...
END
ELSE
BEGIN
...
END
Tuy nhiên các cách sau đây thì tuyệt đối nên tránh:

Cách 1: KHÔNG NÊN DÙNG

IF (SELECT COUNT(*) FROM TblName WHERE...) > 0
BEGIN
...
END
ELSE
BEGIN
...
END

 Cách 2: tệ hơn nữa, KHÔNG NÊN DÙNG

SELECT * FROM TblName WHERE...
IF @@ROWCOUNT > 0
BEGIN
...
END
ELSE
BEGIN
...
END
Lý do: Cả hai cách đều thực hiện các thao tác vượt quá cần thiết, chỉ cần bản ghi đầu tiên tìm được là đủ để chương trình đi tiếp được rồi. Việc đếm số bản ghi (cách 1) hoặc trả lại tất cả các bản ghi (cách 2) làm kéo dài thời gian thực hiện đoạn lệnh. Trong nhiều trường hợp, để tìm ra bản ghi đầu tiên có thể thực hiện rất nhanh, nhưng để tìm ra toàn bộ các bản ghi lại mất một thời gian gấp nhiều lần.

Các Kiểu Backup Trong SQL Server

Khái niệm backup (sao lưu) và restore (khôi phục) chắc hẳn đã quen thuộc đối với đa số chúng ta: bạn thường xuyên backup, ví dụ copy toàn bộ thư mục sang một thiết bị lưu trữ khác, để phòng khi gặp sự cố gây mất mát dữ liệu thì có thể copy ngược trở lại. Với database thì việc backup diễn ra có khác, khi hệ thống đang vận hành thì bạn không thể đơn giản copy các data file và log file vì chúng bị khóa hoàn toàn. Bạn phải dựa vào cơ chế backup của hệ QTCSDL. SQL Server cung cấp ba loại backup như sau:

· Full backup: backup toàn bộ dữ liệu tại thời điểm đó. Đây có lẽ là loại được dùng thường xuyên nhất.
· Differential backup: backup các trang dữ liệu mới được cập nhật kể từ lần full backup trước đó.
· Transaction log backup: backup các log record hiện có trong log file, nghĩa là nó sao lưu các hành động (các thao tác xảy ra đối với database) chứ không sao lưu dữ liệu. Đồng thời nó cũng cắt bỏ (truncate) log file, loại bỏ các log record vừa được backup ra khỏi log file. Vì thế khi thấy log file tăng quá lớn, có nhiều khả năng là bạn chưa từng backup transaction log bao giờ.

Một nguyên tắc chung để giảm bớt lượng dữ liệu mất mát khi có sự cố là tăng tần suất backup. Tuy nhiên với một database có dung lượng lớn và được cập nhật liên tục, thì việc thực hiện full backup với tần suất cao là không khả thi, vì nó dùng rất nhiều CPU và I/O. Nhờ có differential backup và transaction log backup, bạn có thể tạo lập các phương án sao lưu thích hợp, đảm bảo dữ liệu được backup thường xuyên hơn mà không chiếm nhiều tài nguyên của hệ thống. Ví dụ, bạn có thể thực hiện:

· Full backup: một lần mỗi ngày vào 2h sáng.

· Differential backup: vào các thời điểm 6h, 10h, 14h, 18h, 22h (5 lần/ngày).

· Transaction log backup: 15 phút một lần vào các thời điểm 5′, 20′, 35′, và 50′ của mỗi giờ (4 lần/giờ). Chọn thời điểm như vậy để đảm bảo nó xảy ra sau differential backup.

Lưu ý là differential backup luôn sao lưu các trang đã thay đổi kể từ lần full backup trước (trong ví dụ trên là các trang đã thay đổi kể từ 2h), chứ không phải từ lần differential backup trước đó. Vì thế bản backup lúc 10h sẽ bao gồm các trang lưu trong bản lúc 6h, bản 14h gồm các trang đã có trong bản 10h… Transaction log backup thì ngược lại, chỉ sao lưu các log record kể từ lần transaction log backup trước đó.

Giả sử database bị hỏng vào thời điểm 10h55′, bạn cần khôi phục lại database theo trình tự sau:

Bước 1. Khôi phục từ bản full backup gần với thời điểm có sự cố nhất (bản full backup lúc 2h).

Bước 2. Khôi phục từ bản differential backup gần với thời điểm có sự cố nhất (bản lúc 10h).

Bước 3. Khôi phục tất cả các transaction log backup kể từ sau lần diferential backup gần đây nhất, lần lượt theo trình tự thời gian. Đó là các bản tại các thời điểm 10h5′, 1oh20′, 10h35′, và 10h50′.

Bước 1 và 2 đưa database trở lại trạng thái giống như lúc 10h. Ở bước 3, với mỗi lần khôi phục transaction log thì các thao tác chứa trong đó được đem ra thực hiện lại trên database (gọi là log forwarding) và do đó đưa nó về trạng thái gần hơn thời điểm xảy ra sự cố. Như vậy sau khi hoàn tất khôi phục bốn bản transaction log backup thì database sẽ ở vào trạng thái giống như lúc 10h50′. Tuy nhiên các thay đổi diễn ra trong 5 phút sau đó (từ 10h50′ đến 10h55′) đã vĩnh viễn bị mất.

Trong trường hợp may mắn hơn, khi sự cố xảy ra mà log file vẫn còn nguyên vẹn, bạn sẽ có cơ hội đưa database trở lại trạng thái ngay trước khi có sự cố, và do đó không có mất mát dữ liệu. Việc đầu tiên bạn cần làm là thực hiện ngay transaction log backup (nên nhớ, không được vội vàng khôi phục từ bản full backup). Sau đó các bước tiếp theo sẽ tương tự như trên:

Bước 0.
Sao lưu transaction log.

Bước 1. Khôi phục từ full backup file của sáng sớm hôm đó.

Bước 2. Khôi phục từ differential backup file lúc 10h.

Bước 3. Khôi phục các transaction log backup file kể từ sau 10h, lần lượt theo trình tự thời gian: các bản backup vào lúc 10h5′, 1oh20′, 10h35′, 10h50′, và cuối cùng bản lúc 10h55′ (vừa thực hiện ở bước 0).

Trong bài sau tôi sẽ giới thiệu script thực hiện từng bước qua một ví dụ cụ thể.

Tạo Ràng Buộc Duy Nhất Bằng Filtered Index trong SQL server 2008

Ràng buộc duy nhất (unique constraint) được xây dựng trong SQL Server hơi khác so với định nghĩa trong lý thuyết CSDL. Theo định nghĩa (và cũng được qui định trong chuẩn ANSI) thì ràng buộc duy nhất yêu cầu các giá trị phải khác nhau trừ khi chúng là NULL.

Ví dụ nếu một bảng có 1000 bản ghi, và trường C có 800 bản ghi có các giá trị khác nhau, còn ở 200 bản ghi còn lại đều là NULL, thì vẫn thỏa mãn ràng buộc duy nhất.
SQL Server cũng yêu cầu các giá trị phải khác nhau nhưng đồng thời chỉ cho phép tối đa một giá trị NULL. Nói cách khác là nó coi NULL như một giá trị thông thường và hai bản ghi cùng NULL sẽ bị coi là trùng nhau:

CREATE TABLE dbo.CaSy(
CaSy_ID INT IDENTITY PRIMARY KEY,
Ten NVARCHAR(100),
Email VARCHAR(100),
CONSTRAINT UC_CaSy UNIQUE (Email)
)
GO
INSERT INTO dbo.CaSy VALUES(N'Thái Thanh','thanh@thaithanh.com')
INSERT INTO dbo.CaSy VALUES(N'Khánh Ly',NULL)
INSERT INTO dbo.CaSy VALUES(N'Trần Thái Hòa',NULL)

Lệnh tạo bảng và hai lệnh INSERT đầu diễn ra bình thường, nhưng lệnh INSERT thứ ba thì gây ra lỗi sau:

Msg 2627, Level 14, State 2, Line 1
Violation of UNIQUE KEY constraint 'UC_CaSy'. Cannot insert duplicate key in object 'CaSy'.
The statement has been terminated.

Như vậy là SQL Server từ chối thêm một giá trị NULL thứ hai vào cột “Email” vì nó có ràng buộc duy nhất. Điều này thật khó thuyết phục, vì NULL không phải là một giá trị để có thể so sánh được (ví dụ mệnh đề WHERE colname = @Val khi @Val là NULL sẽ không trả về bản ghi nào). Tôi cho rằng đây thuộc về lỗi nhiều hơn là một đặc tính của SQL Server.

Vậy để tạo một ràng buộc duy nhất với cách ứng xử giống như định nghĩa thông thường thì phải làm thế nào? Nếu bạn dùng bản 2005 trở về trước, có một vài cách có thể làm nhưng đều rườm rà:

- Dùng trigger: mỗi khi có INSERT hoặc UPDATE thì kiểm tra xem giá trị mới đã có trong bảng chưa, và chỉ cho phép thực hiện nếu chưa xuất hiện.

- Tạo một ràng buộc CHECK dbo.CountCol(Email) < 2, trong đó dbo.CountCol(@Email) là hàm UDF (bạn cần phải viết) trả về số lần xuất hiện của giá trị @Email trong cột Email.

- Cách làm “chuẩn” nhất có lẽ là tạo một indexed view từ trường Email với điều kiện WHERE Email IS NOT NULL. Tuy nhiên vẫn rườm rà, khi cần tìm kiếm trên trường Email bạn lại phải nhớ tìm trên view này, hoặc phải tạo một index cho trường Email trên bảng CaSy.

Từ bản SQL Server 2008 bạn có thể dùng tính năng filtered index để áp đặt ràng buộc này. Filtered index về bản chất là một non-clustered index nhưng cho phép chọn ra các bản ghi cần được index qua mệnh đề WHERE. Do vậy bạn có thể tạo một index như sau:

CREATE UNIQUE INDEX idx_CaSyEmail ON dbo.CaSy(Email)
WHERE Email IS NOT NULL

Lệnh trên tạo một unique index trên trường Email, nhưng chỉ các bản ghi có Email không NULL mới được index. Vì thế các Email NULL có thể được thêm vào bảng thoải mái, nhưng Email không NULL nếu bị lặp khi thêm vào sẽ bị index này chặn lại, do dó tính duy nhất được đảm bảo.