2012年5月11日 星期五

SQL Server Express資料庫維護


由於Microsoft SQL Server Express 版本無法使用SQL Agent 排程,且SQL 2005 Server 會有4G 容量上限的限制,因此必須使用手動方式整理資料庫,或是使用此排程來操作,此設定需搭配Windows 排程來執行,當然也可以手動執行

介紹說明: 
一、將批次檔寫好放在固定位置不會被刪除 

二、將要執行的SQL Script 放置與批次檔同一個資料夾中,並確認是否正確

三、設定Windows 排程,執行批次檔
四、驗證排程是否可正常執行
五、排程1 「超過三十天的紀錄移轉至歷史紀錄」六、排程2 「將歷史記錄移到其他資料庫」


一、將批次檔寫好放在固定位置不會被刪除 

批次檔案內容說明如下
‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐
@echo off
"C:\Program Files\Microsoft SQL Server\90\Tools\Binn\sqlcmd.exe" ‐S 127.0.0.1 ‐Usa ‐Pkeepout ‐i
D:\backup_full.sql ‐o D:\backup_full.log
‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐
(1) 先檢查 sqlcmd.exe 程式放在哪個位置
(2) 再將程式路徑複製出來,如果路徑中有空格,需要在前後加雙引號。
(3) –S 後面請加上SQL Server 的IP 位置
(4) –U 後面請加上SQL Server 的帳號。
(5) –P 後面請加上SQL Server 該帳號的密碼。
(6) –i 後面為要執行的SQL Script 語法與存放路徑。
(7) –o 後面為執行後所產生的log 放置的地方與檔名。
*批次檔範例下載backup.zip (248 Byte ,下載:39次)
==========================================================  

二、將要執行的SQL Script 放置與批次檔同一個資料夾中,並確認是否正確  
註:文中所有之DB_Name,意指SmartIT之資料庫名稱,標記紅色字體為可變更。
說明: 
將需要執行的SQL Script 放入與批次檔同一個資料夾中,方便管理與維護。
每一個SQL Script 都建一個批次檔來執行,目的是希望每個排程都按照指定時間執行,並且不是每個排程都需要每天做,所以分開執行最好。
這裡建議幾個常用的Script,提供參考。
1、DB Backup 「資料庫單一備份」
-----------------------------------------DB 單一Backup-----------------------------------------
BACKUP DATABASE [DB_Name]
TO DISK = N'D:\SmartIT_DB_BAK\smartit_bk.bak' WITH INIT,NOUNLOAD,
NAME = N'smartit_bk',NOSKIP,STATS = 10,NOFORMAT
-----------------------------------------DB 單一Backup-----------------------------------------
*DB_Name,意指SmartIT資料庫名稱
*D:\SmartIT_DB_BAK\smartit_bk.bak,意指備份路徑及備份檔案名稱
2、清除交易紀錄檔
-----------------------------------------清除交易紀錄檔-----------------------------------------
use DB_Name
Backup Log  DB_Name with TRUNCATE_ONLY
dbcc shrinkfile ( DB_Name_log , 2)
-----------------------------------------清除交易紀錄檔-----------------------------------------
3、資料庫壓縮
-----------------------------------------資料庫壓縮-----------------------------------------
DBCC SHRINKDATABASE ( DB_Name, 10)
-----------------------------------------資料庫壓縮-----------------------------------------
4、DB Backup 「週備份七個檔案」
-----------------------------------------DB Backup 週備份-----------------------------------------
DECLARE @bkFileName nvarchar(255)
SET DATEFIRST 1
SELECT @bkFileName =
CASE DATEPART(dw, GETDATE())
WHEN 1 THEN N'D:\星期一.bak'
WHEN 2 THEN N'D:\星期二.bak'
WHEN 3 THEN N'D:\星期三.bak'
WHEN 4 THEN N'D:\星期四.bak'
WHEN 5 THEN N'D:\星期五.bak'
WHEN 6 THEN N'D:\星期六.bak'
WHEN 7 THEN N'D:\星期日.bak'
END
BACKUP DATABASE [DB_Name] TO DISK = @bkFileName WITH INIT,NOUNLOAD,NAME=N'SMARTIT 備份',NOSKIP,STATS=10,NOFORMAT
-----------------------------------------DB Backup 週備份-----------------------------------------
*DB_Name,意指SmartIT資料庫名稱
*D:\星期一.bak,意指備份路徑及備份檔案

========================================================== 
三、 設定Windows 排程,執行批次檔 
設定Windows 排程希望能由系統每天自動處理,減少人工操作部分
(1)、開啟Windows 排程,選擇[新增排定的工作]啟動視窗後再選擇[下一步]

(2)、選擇要執行的程式後,點選[下一步]

(3)、選擇批次檔的名稱,在按下[開啟]

(4)確認排程名稱後,選擇執行方式,再按[下一步]

(5)確認執行時間後,在點選[下一步]

(6)輸入擁有可執行權限的帳號密碼後,點選[下一步]
(7)完成設定,點選[完成]按鈕

(8)設定完成後,開啟排程,並將設定頁面中,在?小時後停止排成的選項,取消勾選
完成後點選套用結束畫面


==========================================================  
四、 驗證排程是否可正常執行
完成排成設定後可按滑鼠右鍵,選擇[執行]來啟動排程,確認排程是否可執行完畢。

==========================================================   
五、排程1 「超過三十天的紀錄移轉至歷史紀錄」

-----------------------------------------移轉排程-----------------------------------------------

/* 1.網站稽核 */

DELETE FROM url_audit_history
WHERE  (url_audit_history_key IN
           (SELECT url_audit_key
            FROM  url_audit
            WHERE substring(audit_date, 1, 8) < Cast(CONVERT(Varchar(8), GetDate() -30, 112) AS varchar)))
INSERT INTO url_audit_history
SELECT  *
FROM    url_audit
WHERE (SUBSTRING(audit_date, 1, 8) < CAST(CONVERT(Varchar(8), GETDATE() -30,112) AS varchar))

delete from url_audit
where substring( audit_date, 1, 8) < Cast( Convert(Varchar(8), GetDate()-30,112) AS varchar)

/* 2.軟體變更 */
DELETE FROM software_change_history
WHERE (software_change_history_key IN
        (SELECT software_change_key
            FROM  software_change
            WHERE substring(change_date, 1, 8) < Cast(CONVERT(Varchar(8), 
                           GetDate() -30, 112) AS varchar)))
INSERT INTO software_change_history
SELECT   *
FROM    software_change
WHERE   (SUBSTRING(change_date, 1, 8) < CAST(CONVERT(Varchar(8), GETDATE() -30,112) AS varchar))

DELETE FROM software_change
WHERE   (SUBSTRING(change_date, 1, 8) < CAST(CONVERT(Varchar(8), GETDATE() -30,112) AS varchar))

/* 3.系統變更 */
DELETE FROM system_change_history
WHERE  (system_change_history_key IN
         (SELECT  system_change_key
           FROM  system_change
           WHERE  substring(audit_date, 1, 8) < Cast(CONVERT(Varchar(8), GetDate() - 30, 112) AS varchar)))
INSERT INTO system_change_history
SELECT   *
FROM    system_change
WHERE   (SUBSTRING(audit_date, 1, 8) < CAST(CONVERT(Varchar(8), GETDATE() - 30,112) AS varchar))
DELETE FROM system_change
WHERE   (SUBSTRING(audit_date, 1, 8) < CAST(CONVERT(Varchar(8), GETDATE() - 30,112) AS varchar))

/* 4.應用程式使用率 */
DELETE FROM appusage_history
WHERE (serial IN
      (SELECT serial
        FROM  appusage
        WHERE substring(uses_date, 1, 8) < Cast(CONVERT(Varchar(8), GetDate() - 30, 112) AS varchar)))
INSERT INTO appusage_history
SELECT         *
FROM    appusage
WHERE  (SUBSTRING(uses_date, 1, 8) < CAST(CONVERT(Varchar(8), GETDATE() - 30,112) AS varchar))
DELETE FROM appusage
WHERE  (SUBSTRING(uses_date, 1, 8) < CAST(CONVERT(Varchar(8), GETDATE() - 30,112) AS varchar))

/* 5.USB檔案稽核記錄  */
DELETE FROM files_audit_log_history
WHERE  (files_audit_log_history_key IN
       (SELECT  files_audit_log_key
         FROM  files_audit_log
         WHERE  substring(affect_date, 1, 8) < Cast(CONVERT(Varchar(8), GetDate()  - 30, 112) AS varchar)))
INSERT INTO files_audit_log_history
SELECT   *
FROM    files_audit_log
WHERE  (SUBSTRING(affect_date, 1, 8) < CAST(CONVERT(Varchar(8), GETDATE() - 30,112) AS varchar))
DELETE FROM files_audit_log
WHERE  (SUBSTRING(affect_date, 1, 8) < CAST(CONVERT(Varchar(8), GETDATE() - 30,112) AS varchar))

/* 6.管理端稽核記錄  */
DELETE FROM manage_change_log_history
WHERE (manage_change_log_history_key IN
        (SELECT    manage_change_key
         FROM     manage_change
          WHERE   substring(inventory_change_date, 1, 8)  < Cast(CONVERT(Varchar(8), GetDate() - 30, 112) AS varchar)))
INSERT INTO manage_change_log_history
SELECT  *
FROM     manage_change
WHERE  (SUBSTRING(inventory_change_date, 1, 8) < CAST(CONVERT(Varchar(8), GETDATE() - 30, 112) AS varchar))
DELETE FROM manage_change
WHERE  (SUBSTRING(inventory_change_date, 1, 8) < CAST(CONVERT(Varchar(8), GETDATE() - 30, 112) AS varchar))

/* 7.印表機列印記錄 */
DELETE FROM print_log_history
WHERE  (print_log_history_key IN
       (SELECT  print_log_key
        FROM    print_log
        WHERE  substring(print_time, 1, 8) < Cast(CONVERT(Varchar(8), GetDate()  - 30, 112) AS varchar)))
INSERT INTO print_log_history
SELECT   *
FROM     print_log
WHERE   (SUBSTRING(print_time, 1, 8) < CAST(CONVERT(Varchar(8), GETDATE() - 30, 112)  AS varchar))
DELETE FROM print_log
WHERE   (SUBSTRING(print_time, 1, 8) < CAST(CONVERT(Varchar(8), GETDATE() - 30, 112)  AS varchar))

/* 8.系統稽核記錄 */
DELETE FROM system_audit_log_history
WHERE   (system_audit_log_history_key IN
        (SELECT   system_audit_log_key
         FROM     system_audit_log
          WHERE   substring(audit_date, 1, 8) < Cast(CONVERT(Varchar(8), GetDate()   - 30, 112) AS varchar)))
INSERT INTO system_audit_log_history
SELECT   *
FROM    system_audit_log
WHERE   (SUBSTRING(audit_date, 1, 8) < CAST(CONVERT(Varchar(8), GETDATE() - 30,  112) AS varchar))
DELETE FROM system_audit_log
WHERE   (SUBSTRING(audit_date, 1, 8) < CAST(CONVERT(Varchar(8), GETDATE() - 30,  112) AS varchar))

-----------------------------------------移轉排程-----------------------------------------------

建議設定方式:
資料庫移轉排程通常為開啟多項稽核時進行設定。大量時則可設定一個月一次,在週六或週日執行比較不影響效能。

========================================================== 
六、排程2 「將歷史紀錄移到其他資料庫」

說明:
使用下列指令可以將歷史紀錄移轉至其他資料庫做資料存放

-------------------------- DB 資料移轉-----------------------------
/* 1.網站稽核 */
DELETE FROM  Backup_DB_Name.dbo.url_audit_history  --目的DB
WHERE  ( Backup_DB_Name.dbo.url_audit_history.url_audit_history_key IN  --目的DB比對值
           (SELECT  DB_Name.dbo.url_audit_history.url_audit_history_key  --來源DB比對值
            FROM   DB_Name.dbo.url_audit_history ))  --來源DB
INSERT INTO  Backup_DB_Name.dbo.url_audit_history  --目的DB
SELECT  * FROM     Backup_DB_Name.dbo.url_audit_history  --來源DB
DELETE FROM  Backup_DB_Name.dbo.url_audit_history  --來源DB

/* 2.應用程式使用率  */
DELETE FROM  Backup_DB_Name.dbo.appusage_history  --目的DB
WHERE ( Backup_DB_Name.dbo.appusage_history.serial IN  --目的DB比對值
      (SELECT  DB_Name.dbo.appusage_history.serial  --來源DB比對值
        FROM   DB_Name.dbo.appusage_history))  --來源DB
INSERT INTO  Backup_DB_Name .dbo.appusage_history  --目的DB
SELECT * FROM  DB_Name .dbo.appusage_history  --來源DB
DELETE FROM  DB_Name .dbo.appusage_history  --來源DB

/* 3.印表機列印記錄    */
DELETE FROM  Backup_DB_Name.dbo.print_log_history  --目的DB
WHERE  ( Backup_DB_Name.dbo.print_log_history.print_log_history_key IN  --目的DB比對值
       (SELECT   DB_Name.dbo.print_log_history.print_log_history_key  --來源DB比對值
        FROM     DB_Name.dbo.print_log_history ))  --來源DB
INSERT INTO  Backup_DB_Name.dbo.print_log_history  --目的DB
SELECT  * FROM   DB_Name.dbo.print_log_history  --來源DB
DELETE FROM  DB_Name.dbo.print_log_history  --來源DB

/* 4.軟體變更  */
DELETE FROM  Backup_DB_Name.dbo.software_change_history  --目的DB
WHERE ( Backup_DB_Name.dbo.software_change_history.software_change_history_key IN  --目的DB比對值
        (SELECT  DB_Name.dbo.software_change_history.software_change_history_key  --來源DB比對值
            FROM   DB_Name.dbo.software_change_history))  --來源DB
INSERT INTO  Backup_DB_Name.dbo.software_change_history  --目的DB
SELECT * FROM  DB_Name.dbo.software_change_history  --來源DB
DELETE FROM  DB_Name.dbo.software_change_history  --來源DB

/* 5.系統變更  */
DELETE FROM  Backup_DB_Name.dbo.system_change_history  --目的DB
WHERE  ( Backup_DB_Name.dbo.system_change_history.system_change_history_key IN  --目的DB比對值
         (SELECT  DB_Name.dbo.system_change_history.system_change_history_key  --來源DB比對值
           FROM   DB_Name.dbo.system_change_history))  --來源DB
INSERT INTO  Backup_DB_Name.dbo.system_change_history  --目的DB
SELECT * FROM  DB_Name.dbo.system_change_history  --來源DB
DELETE FROM  DB_Name.dbo.system_change_history  --來源DB

/* 6.管理端稽核記錄   */
DELETE FROM  Backup_DB_Name.dbo.manage_change_log_history  --目的DB
WHERE ( Backup_DB_Name.dbo.manage_change_log_history.manage_change_log_history_key IN  --目的DB比對值
        (SELECT   DB_Name.dbo.manage_change_log_history.manage_change_log_history_key  --來源DB比對值
         FROM    DB_Name.dbo.manage_change_log_history))  --來源DB
INSERT INTO  Backup_DB_Name.dbo.manage_change_log_history  --目的DB
SELECT * FROM  DB_Name.dbo.manage_change_log_history  --來源DB
DELETE FROM  DB_Name.dbo.manage_change_log_history  --來源DB

/* 7.即時通訊稽核 */
DELETE FROM  Backup_DB_Name.dbo.imlog_history  --目的DB
WHERE  ( Backup_DB_Name.dbo.imlog_history.serial IN  --目的DB比對值
           (SELECT  DB_Name.dbo.imlog_history.serial  --來源DB比對值
            FROM   DB_Name.dbo.imlog_history ))  --來源DB
INSERT INTO  Backup_DB_Name.dbo.imlog_history  --目的DB
SELECT  * FROM   DB_Name.dbo.imlog_history  --來源DB
DELETE FROM  DB_Name.dbo.imlog_history  --來源DB

/* 8.USB檔案稽核 */
DELETE FROM  Backup_DB_Name.dbo.files_audit_log_history  --目的DB
WHERE  ( Backup_DB_Name.dbo.files_audit_log_history.files_audit_log_history_key IN  --目的DB比對值
       (SELECT   DB_Name.dbo.files_audit_log_history.files_audit_log_history_key  --來源DB比對值
         FROM    DB_Name.dbo.files_audit_log_history ))     --來源DB
INSERT INTO  Backup_DB_Name.dbo.files_audit_log_history --目的DB
SELECT * FROM   DB_Name.dbo.files_audit_log_history   --來源DB
DELETE FROM   DB_Name.dbo.files_audit_log_history    --來源DB 

-------------------------- DB 資料移轉-----------------------------
*DB_Name,意指SmartIT來源資料庫名稱*Backup_DB_Name,意指備份用的目的地資料庫名稱 

==========================================================

0 意見:

張貼留言

 
Design by Free WordPress Themes | Bloggerized by Lasantha - Premium Blogger Themes | Web Hosting Bluehost