數據庫維護

數據庫日常維護工作是系統管理員的重要職責。其內容主要包括以下幾個部分：

SYBASE 系統的備份與恢復機制保證了在系統失敗時重新獲取數據的可能性。SQL Server 提供了兩種不同類型的恢復機制：一類是系統自動完成的恢復，這種措施在每次系統啓動時都自動進行，保證了在系統癱瘓前完成的事務都寫到數據庫設備上，而未完成的事務都被回退；另一類是人工完成的恢復，這是通過 DUMP 和 LOAD 命令來執行人工備份和恢復工作。因此定期備份事務日誌和數據庫是一項十分重要的日常維護工作。

1、備份數據庫

每一個數據庫都應在創建之後卸出，從而提供一個裝入基點。在此之後按排定的時間週期表卸出。比如每週五卸出數據庫。對一般數據庫系統卸出數據庫週期建議為每週一次。

除了按計劃週期卸出數據庫之外，還需在每次運行沒有日誌的操作後卸出數據庫。例如：

·每次強制地運行了 DUMP TRAN WITH NO_LOG （因為數據庫的磁盤空溢出）；

·每次用 sp_dboption 允許 select into/bulkcopy 做快速拷貝，或用 SELECT INTO 命令創建一個永久性的表，或使用了 WRITETEXT 命令。

卸出數據庫的命令為：

DUMP DATABASE database_name

TO dump_device

database_name 是要卸出的數據庫名稱，dump_device 是卸出設備的名稱。用系統過程 sp_helpdevice 可以獲得設備的信息。

下面一條命令用來卸出數據庫 my_db ：

DUMP DATABASE my_db

TO db_bk_dev

2、備份事務日誌

如果事務日誌與數據庫放在同一個設備上，則事務日誌不應與數據庫分開備份。master 數據庫和小於 4M 的用户數據庫就是這種情況。一般數據庫系統的數據庫和日誌分別放在不同的設備上，因此，可以用 DUMP TRAN 命令單獨備份日誌。

備份事務日誌的週期直接影響數據的恢復程度，因此建議每天備份。

備份事務日誌的命令格式為：

DUMP TRANsaction database_name

[TO dump_device]

[WITH TRUNCATE_ONLY|WITH NO_LOG|WITH NO_TRUNCATE]

其中 database_name 是要備份事務的數據庫名稱，dump_device 是備份設備名稱，僅當包含了 WITH TRUNCATE_ONLY 或 WITH NO_LOG 子句時，才可以備份到設備。

注意：如果總是用 DUMP DATEBASE （備份數據庫及其日誌），而不用 DUMP TRAN ，事務日誌將不會刷新，而變得非常龐大。

對於 master 數據庫和小型數據庫每次運行 DUMP DATEBASE 之後應當運行 DUMP TRANsaction 命令刷新日誌 。

下面一條命令備份數據庫 db160 的事務日誌到備份設備上：

DUMP TRANsaction db160

TO db_log_bk_dev

WITH TRUNCATE_ONLY

3、備份數據庫及其日誌間的相互作用

在至少卸出一次數據庫前，卸出事務日誌是毫無意義的。顯示了備份數據庫及其日誌間的關係

如果在星期二下午5：01出現非硬件故障，需要做的所有工作是裝入磁帶5（參見下一節：數據恢復），由於磁帶5是下午5：00剛備份的，因此只有備份和裝入之間的一分鐘內的數據損失。

但是，如果在星期二下午4：49失效會怎麼樣呢？在這種情況下，要裝入磁帶1（在星期五下午5：00的卸出）。然後，依次裝入磁帶2，3以及4。這樣，系統將恢復到星期二上午10：00點的狀態，星期二的大部分工作丟失了。此例顯示了經常卸出事務的重要性。

如果用户數據庫存儲的設備失效，從而數據庫被破壞或不可存取，通過裝入最新的數據庫備份以及後來的事務日誌備份可以恢復數據庫。假設當前的事務日誌存在於一個並沒有毀壞的設備上，帶着 WITH NO_TRUNCATE 選項的 DUMP TRANsaction 命令卸出它。

要恢復數據庫按如下步驟去做：

1、如果日誌存在於一個分離的設備上，用帶着 NO_TRUNCATE 選項的 DUMP TRANsaction 命令卸出被毀壞的或者不可存取的用户數據庫事務日誌。

2、用下面的查詢檢查設備分配已毀壞數據庫的設備使用情況。必須為同一目的賦同樣的空間塊。

下面的查詢顯示了分配給數據庫 mydb 設備使用和尺寸情況：

SELECT segmap，size FROM sysusages

WHERE dbid =

（ SELECT dbid FROM sysdatabases WHERE name = “mydb”）

3、檢查查詢的輸出。在 segmap 列的 ‘3’代表數據分配，‘4’代表日誌分配。size 列代表 2K 數據塊的數目。注意此信息的次序、使用和尺寸部分。例如，輸出為：

segmapSize

--------------------

310240//實際尺寸為：20M

35120//實際尺寸為：10M

45120//實際尺寸為：10M

31024//實際尺寸為：2M

42048//實際尺寸為：4M

4、用 DROP DATABASE 命令刪除毀壞設備上的數據庫。如果系統報錯，用DBCC DBREPAIR 命令的 DROPDB 選項。

5、刪除數據庫後，用 sp_dropdevice 刪除毀壞了的設備。

6、用 DISK INIT 初始化新的數據庫設備。

7、重建數據庫。用 CREATE DATABASE 命令從老的 sysusages 表拷貝所有的行，幷包含第一邏輯設備。

對上例，命令為：

CREATE DATABASE mydb

ON datadev1=20，datadev2=10

LOG ON logdev1=10

8、用 ALTER DATABASE 命令重建其餘入口。在此例中，在datadev1上分配更多的空間，命令為：

ALTER DATABASE mydb ON datadev1=2[page]

9、用 LOAD DATABASE 重新裝入數據庫，然後用 LOAD TRAN 裝入前面卸出的日誌。

LOAD DATABASE 命令語法是：

LOAD DATABASE database_name

FROM dump_device

LOAD TRANsaction 命令的語法是：

LOAD TRANsaction database_name

FROM dump_device

卸出數據庫和事務日誌的缺省權限歸數據庫所有者，且可以傳遞給其他用户；裝載數據庫和事務的權限也歸數據庫所有者，但不能傳遞。

系統維護人員的另一個日常事務是為用户創建新的信息表，併為之授權。創建表以及為表授權的方法已經在講過，在此只將有關命令語法寫出來。

·創建表的命令為：

CREATE TABLE table_name

（ column_1 datatype [NULL | NOT NULL | IDENTITY]，column_2 ……）

go

ALTER TABLE table_name

ADD PRIMARY KEY （column_list）

go

·刪除表的命令格式為：

DROP TABLE table_name

go

·為表授權的命令格式為：

GRANT {ALL|permission_list}

ON table_name TO user_name

go

·收回權限的命令格式為

REVOKE {ALL|permission_list}

ON table_name FROM user_name

go

系統管理員的另一項日常工作是監視系統運行情況，及時處理系統錯誤。主要有以下幾個方面：

1、監視當前用户以及進程的信息

使用系統過程：sp_who

説明：該命令顯示當前系統所有註冊用户及進程信息，如下表是某系統的信息。

SpidStatusLoginamehostnameblkdbnamecmd

---------------------------------------------------------------

1RunningSascosysv0MasterSELECT

2SleepingNULL0MasterNETWORK HANDLE

3SleepingNULL0MasterDEADLOCK TUNE

4SleepingNULL0MasterMIRROR HANDLER

5SleepingNULL0MasterHOUSEKEEPER

6SleepingNULL0MasterCHECKPOINT SLEEP

從左向右依次顯示：進程號、當前狀態、註冊用户名、主機名、佔用塊數、數據庫名以及當前命令。

如果監視時發現進程總數接近最大連接數（用系統過程：sp_configure “user conn” 查看）時，應下掉不活動或無關進程，以保證系統正常運做；另外亦可監視非法用户或用户使用不屬於自己使用範圍的數據庫等情況。

2、監視目標占用空間情況

使用系統過程：sp_spaceused

説明：該過程顯示行數、數據頁數以及當前數據庫中由某個目標或所有目標所佔用的空間。如下表是某數據庫日誌表的信息：

NameRow_totalreserveddataIndex_sizeunused

------------------------------------------------------------

SyslogsNot avail32KB32KB0KBNot avail

日常要監視的主要目標有：用户數據庫、數據庫日誌表（syslogs）以及計費原始數據表等。如果發現佔用空間過大，對日誌表要進行轉儲；對其他目標則應擴充空間或清楚垃圾數據。

3、監視 SQL Server 統計數字

使用系統過程：sp_monitor

説明：sp_monitor 顯示SQL Server 的歷史統計數字，下表是某系統的統計數字：

Last_runCurrent_runSeconds

---------------------------------------------------------------

May 13 2000 1:27PMMay 13 2000 3:01PM5678

CPU_busyIO_busyIdle

---------------------------------------------------------------

16(6)-0%0(0)-0%5727(5672)-99%

Packets_receivedPackets_sentPacket_errors

---------------------------------------------------------------

21(17)100(97)0(0)

Total_readTotal_writeTotal_errorsConnections

--------------------------------------------------------

785(366)311(113)0(0)3(2)

上表依次給出該系統本次運行統計的上一次時間、本次時間、間隔秒數、CPU佔用、IO佔用、收發包情況、系統讀入寫出情況等信息

為保證系統數據的安全，系統管理員必須依據系統的實際情況，執行一系列的安全保障措施。其中，週期性的更改用户口令是比較常用且十分有效的措施。

更改用户口令是通過調用系統過程sp_password 來實現的。Sp_password 的語法為：

sp_password caller_password,new_password [,loginame]

其中caller_password 是登錄口令（老口令），new_password是新口令，loginame是登錄名稱。