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))

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

建議設定方式：

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

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

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

說明：

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

-------------------------- 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，意指備份用的目的地資料庫名稱 

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

Posted in: 知識分享, SQLServer