2025-08-11

Split and Merge Sheng-Bo MailStore for SQL DB

It's not a popular Software / Application.
You may not need it.

First : SplitDB.vbs (Run on MailStore Server)
=> Split DB by day with SplitDB.vbs

Second: MergeDB.vbs  (Run on MailSearch Server)
=> Merge DB to Quarter

SplitDB.vbs (Run on MailStore Server)

' on error resume next

SQLSourceServer = "MailStoreServerName"
SQLSourceUser = "sa"
SQLSourcePassword = "Password"
SQLSourceDB = "MailStore"
SourceDBRemainDays = 1
Dim SQLSourceConnString

DBPreFix = "AQ"

Dim SQLSourceConnection

Dim SQLString
Dim SQLRecordSet
SQLConnectionTimeout = 2
SQLCommandTimeOut = 600


Dim OriginalIDIndex
Dim OriginalIDArray()
OriginalIDIndex = -1

Dim YearNo
Dim MonthNo
Dim DayNo
Dim DBPath

DBPath = "D:\MailDB"

'--- RestartServices ---

RestartServices ".", "SQLSERVERAGENT,MSSQLSERVER"
Sub RestartServices(Computer, ServiceNames)
  Dim ServiceName, Counter, aServiceNames
  'Get the array of service names  
  aServiceNames = split(ServiceNames,",")
  
  'loop services from beginning, stop them 
  For Each ServiceName In aServiceNames 
StopService Computer, ServiceName, True
  Next 
  'loop services from end, start them 
  For Counter = ubound(aServiceNames) To 0 Step -1 
StartService Computer, aServiceNames(Counter), True 
  Next 
End Sub
Sub StopService(Computer, ServiceName, Wait)
  Dim cimv2, oService, Result
  'Get the WMI administration object    
  Set cimv2 = GetObject("winmgmts:{impersonationLevel=impersonate}!\\" & Computer & "\root\cimv2")
  'Get the service object
  Set oService = cimv2.Get("Win32_Service.Name='" & ServiceName & "'")
  
  'Check base properties
  If Not oService.Started Then
' the service is Not started
'wscript.echo "The service " & ServiceName & " is Not started"
exit Sub
  End If
  If Not oService.AcceptStop Then
' the service does Not accept stop command
'wscript.echo "The service " & ServiceName & " does Not accept stop command"
exit Sub
  End If
  
  ''wscript.echo oService.getobjecttext_
  'Stop the service
  Result  = oService.StopService
  If 0 <> Result Then
'wscript.echo "Stop " & ServiceName & " error: " & Result
exit Sub 
  End If 
  
  Do While oService.Started And Wait
'get the current service state
Set oService = cimv2.Get("Win32_Service.Name='" & ServiceName & "'")
'wscript.echo now, "StopService", ServiceName, oService.Started, oService.State, oService.Status
Wscript.Sleep 200
  Loop   
End Sub
Sub StartService(Computer, ServiceName, Wait)
  Dim cimv2, oService, Result
  'Get the WMI administration object    
  Set cimv2 = GetObject("winmgmts:{impersonationLevel=impersonate}!\\" & Computer & "\root\cimv2")
  'Get the service object
  Set oService = cimv2.Get("Win32_Service.Name='" & ServiceName & "'")
  
  'Path = "winmgmts:{impersonationLevel=impersonate}!\\" & Computer & _
  '  "\root\cimv2:Win32_Service.Name='" & ServiceName & "'" 
  'Get the WMI administration object of the service    
  'Set oService = GetObject(Path)
  'Check base properties
  If oService.Started Then
' the service is Not started
'wscript.echo "The service " & ServiceName & " is started."
exit Sub
  End If
  
  'Start the service
  Result = oService.StartService
  If 0 <> Result Then
'wscript.echo "Start " & ServiceName & " error:" & Result
exit Sub 
  End If 
  
  Do While InStr(1,oService.State,"running",1) = 0 And Wait 
'get the current service state
Set oService = cimv2.Get("Win32_Service.Name='" & ServiceName & "'")

'wscript.echo now, "StartService", ServiceName, oService.Started, oService.State, oService.Status
Wscript.Sleep 200
  Loop   
End Sub
'--- RestartServices ---

'Wscript.Sleep 30000

TableArray = Array ("MailAttachmentV4","MailInfoV4","MailRCPV4","MailToV4")

Set SQLSourceConnection = CreateObject("ADODB.Connection")
SQLSourceConnString = "DRIVER={SQL Server};SERVER=" & SQLSourceServer & ";UID=" & SQLSourceUser & ";PWD=" & SQLSourcePassword & ";DATABASE=" & SQLSourceDB
SQLSourceConnection.ConnectionTimeout = SQLConnectionTimeout
SQLSourceConnection.CommandTimeOut = SQLCommandTimeOut
SQLSourceConnection.Open SQLSourceConnString
Set SQLRecordSet = CreateObject("ADODB.RecordSet")
SplitDate = ""
SQLString = "SELECT Top 1 dateadd(hh,+8,[MailDate]) as [MailDate] FROM [" & SQLSourceDB & "].[dbo].[MailInfoV4] where dateadd(hh,+8,[MailDate]) < '" & dateadd("d",(1-SourceDBRemainDays),date) & "' order by [MailDate]"
Set SQLRecordSet = SQLSourceConnection.Execute(SQLString, RecordsAffected)
If not SQLRecordSet.EoF then
SplitDate = SQLRecordSet.Fields("MailDate")
end if
SQLRecordSet.Close

if (SplitDate <> "") Then
YearNo = Year(SplitDate)
MonthNo = Month(SplitDate)
DayNo = Day(SplitDate)

YearString = CStr(YearNo)
MonthString = right("0" & MonthNo,2)
DayString = right("0" & DayNo,2)

StartDateString = YearString & "/" & MonthString & "/" & DayString
EndDateString = dateadd("d",+1,StartDateString)
TargetDBName = DBPreFix & YearString & MonthString & DayString
' 取出序號 OriginalID
Do
SQLString = "SELECT Top 50 [OriginalID] FROM [" & SQLSourceDB & "].[dbo].[MailInfoV4] where dateadd(hh,+8,[MailDate]) >= '" & StartDateString & " 00:00:00' and  dateadd(hh,+8,[MailDate]) < '" & EndDateString & " 00:00:00'"
Set SQLRecordSet = SQLSourceConnection.Execute(SQLString, RecordsAffected)
While not SQLRecordSet.EoF
OriginalIDIndex = OriginalIDIndex + 1
ReDim Preserve OriginalIDArray(OriginalIDIndex)
OriginalIDArray(OriginalIDIndex) = SQLRecordSet.Fields("OriginalID")
SQLRecordSet.MoveNext
Wend

if (OriginalIDIndex < 0) then
' 'wscript.echo "No Data for " & StartDateString
wscript.quit
end if

SQLProcessIDs = ""
for i = 0 to OriginalIDIndex
If (SQLProcessIDs <> "") Then
SQLProcessIDs = SQLProcessIDs & " or "
End If
SQLProcessIDs = SQLProcessIDs & " [OriginalID] = '" & OriginalIDArray(i) & "'"
next

' 產生複製命令

CheckDBLinkExist = "select [name] from sys.databases where [name] = '" & TargetDBName & "';"
Set SQLRecordSet = SQLSourceConnection.Execute(CheckDBLinkExist, RecordsAffected)
If SQLRecordSet.EoF Then
CreateDBAndTable(TargetDBName)
end if

for i = 0 to UBound(TableArray)

' 刪除因程式中斷而產生的重複項目
SQLString = ""
SQLString = SQLString & "Delete from [" & TargetDBName & "].[dbo].[" & TableArray(i) & "] where"
SQLString = SQLString & SQLProcessIDs & ";"
Set SQLRecordSet = SQLSourceConnection.Execute(SQLString, RecordsAffected)

' 複製資料
SQLString = ""
SQLString = SQLString & " Insert Into [" & TargetDBName & "].[dbo].[" & TableArray(i) & "]"
SQLString = SQLString & " Select * from [" & SQLSourceDB & "].[dbo].[" & TableArray(i) & "] where"
SQLString = SQLString & SQLProcessIDs & ";"
Set SQLRecordSet = SQLSourceConnection.Execute(SQLString, RecordsAffected)
next

SQLString = ""
for i = 0 to UBound(TableArray)
SQLString = SQLString & "Delete from [" & SQLSourceDB & "].[dbo].[" & TableArray(i) & "] where"
SQLString = SQLString & SQLProcessIDs & ";"
next

Set SQLRecordSet = SQLSourceConnection.Execute(SQLString, RecordsAffected)

if (OriginalIDIndex = 49) then
OriginalIDIndex = -1
end if

Loop while (OriginalIDIndex = -1)

' 壓縮資料庫縮小 DB 檔案
SQLString = ""
SQLString = SQLString & "ALTER DATABASE [N'" & SQLSourceDB & "'] SET RECOVERY SIMPLE;"
SQLString = SQLString & "DBCC SHRINKDATABASE(N'" & SQLSourceDB & "');"
SQLString = SQLString & "ALTER DATABASE [N'" & SQLSourceDB & "'] SET RECOVERY FULL;"
Set SQLRecordSet = SQLSourceConnection.Execute(SQLString, RecordsAffected)

Set WshShell = WScript.CreateObject("WScript.Shell")
Return = WshShell.Run(WScript.ScriptFullName, 0, False)

End If
wscript.quit

Function CreateDBAndTable(TargetDBName)
SQLString = "CREATE DATABASE [" & TargetDBName & "] ON PRIMARY"
SQLString = SQLString & " (NAME = N'" & TargetDBName & "', FILENAME = N'" & DBPath & "\" & TargetDBName & ".mdf' , SIZE = 5120KB , FILEGROWTH = 10%)"
SQLString = SQLString & " LOG ON (NAME = N'" & TargetDBName & "_log', FILENAME = N'" & DBPath & "\" & TargetDBName & "_log.ldf' , SIZE = 1024KB , FILEGROWTH = 10%)"
Set SQLRecordSet = SQLSourceConnection.Execute(SQLString, RecordsAffected)
SQLString = "CREATE TABLE [" & TargetDBName & "].[dbo].[MailAttachmentV4]("
SQLString = SQLString & " [OriginalID] [varchar](50) NOT NULL,"
SQLString = SQLString & " [FileName] [nvarchar](255) NOT NULL,"
SQLString = SQLString & " [Extension] [nvarchar](50) NOT NULL,"
SQLString = SQLString & " [Size] [bigint] NOT NULL,"
SQLString = SQLString & " CONSTRAINT [PK_MailAttachmentV4] PRIMARY KEY CLUSTERED ("
SQLString = SQLString & " [OriginalID] ASC,"
SQLString = SQLString & " [FileName] ASC) WITH ("
SQLString = SQLString & " PAD_INDEX = OFF,STATISTICS_NORECOMPUTE = OFF,"
SQLString = SQLString & " IGNORE_DUP_KEY = OFF,ALLOW_ROW_LOCKS = ON,"
SQLString = SQLString & " ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]) ON [PRIMARY]"
Set SQLRecordSet = SQLSourceConnection.Execute(SQLString, RecordsAffected)

SQLString = "CREATE TABLE [" & TargetDBName & "].[dbo].[MailInfoV4]("
SQLString = SQLString & " [OriginalID] [varchar](50) NOT NULL,"
SQLString = SQLString & " [OpDate] [datetime] NOT NULL,"
SQLString = SQLString & " [MailDate] [datetime] NOT NULL,"
SQLString = SQLString & " [MailFrom] [nvarchar](255) NOT NULL,"
SQLString = SQLString & " [Subject] [nvarchar](255) NOT NULL,"
SQLString = SQLString & " [MsgSize] [int] NOT NULL,"
SQLString = SQLString & " [ZipSize] [int] NOT NULL,"
SQLString = SQLString & " [MSG] [image] NOT NULL,"
SQLString = SQLString & " [RCP] [image] NOT NULL,"
SQLString = SQLString & " [CompressMode] [int] NOT NULL,"
SQLString = SQLString & " [Recipients] [nvarchar](max) NOT NULL,"
SQLString = SQLString & " [Attachments] [nvarchar](max) NOT NULL,"
SQLString = SQLString & " [Encoding] [int] NOT NULL,"
SQLString = SQLString & " CONSTRAINT [PK_MailInfoV4] PRIMARY KEY CLUSTERED ("
SQLString = SQLString & " [OriginalID] ASC) WITH ("
SQLString = SQLString & " PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF,"
SQLString = SQLString & " IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON,"
SQLString = SQLString & " ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]"
SQLString = SQLString & " ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]"
Set SQLRecordSet = SQLSourceConnection.Execute(SQLString, RecordsAffected)

SQLString = "CREATE TABLE [" & TargetDBName & "].[dbo].[MailRCPV4]("
SQLString = SQLString & " [OriginalID] [varchar](50) NOT NULL,"
SQLString = SQLString & " [MsgID] [varchar](50) NOT NULL,"
SQLString = SQLString & " [RCP] [image] NOT NULL,"
SQLString = SQLString & " [Encoding] [int] NOT NULL,"
SQLString = SQLString & " CONSTRAINT [PK_MailRCPV4] PRIMARY KEY CLUSTERED ("
SQLString = SQLString & " [OriginalID] ASC,"
SQLString = SQLString & " [MsgID] ASC) WITH ("
SQLString = SQLString & " PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF,"
SQLString = SQLString & " IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON,"
SQLString = SQLString & " ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]"
SQLString = SQLString & " ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]"
Set SQLRecordSet = SQLSourceConnection.Execute(SQLString, RecordsAffected)

SQLString = "CREATE TABLE [" & TargetDBName & "].[dbo].[MailToV4]("
SQLString = SQLString & " [OriginalID] [varchar](50) NOT NULL,"
SQLString = SQLString & " [MailTo] [nvarchar](255) NOT NULL,"
SQLString = SQLString & " CONSTRAINT [PK_MailToV4] PRIMARY KEY CLUSTERED ("
SQLString = SQLString & " [OriginalID] ASC,"
SQLString = SQLString & " [MailTo] ASC) WITH ("
SQLString = SQLString & " PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF,"
SQLString = SQLString & " IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON,"
SQLString = SQLString & " ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]) ON [PRIMARY]"
Set SQLRecordSet = SQLSourceConnection.Execute(SQLString, RecordsAffected)

End Function

MergeDB.vbs  (Run on MailSearch Server)

'on error resume next

SQLSourceServer = "MailStoreServerName"
SQLSourceUser = "sa"
SQLSourcePassword = "Password"
SQLSourcePath = "D:\MailDB\"
SQLSourceUNCPath = "\\MailStoreServerName\D$\MailDB\"
NextScript = "D:\NextScript.vbs"
'NextScript = ""
KillOtherWscriptEXE = 1

DBPreFix = "AQ"

Dim SQLSourceConnection
Dim SQLSourceConnString

SQLTargetServer = "MailSearchServerName"
SQLTargetUser = "sa"
SQLTargetPassword = "Password"
SQLTargetPath = "D:\MailDB\"

Dim SQLTargetConnection
Dim SQLTargetConnString

Dim SQLString
Dim SQLRecordSet
SQLConnectionTimeout = 2
SQLCommandTimeOut = 600

Dim SourceDBNameIndex
Dim SourceDBNameArray()
Dim SourceDBNameLenArray()
Dim SourceDBFilePathArray()
SourceDBNameIndex = -1

Dim ProcessDBDate
Dim ProcessDBMDF
Dim ProcessDBMDFPath
Dim ProcessDBLDF
Dim ProcessDBLDFPath
Dim ProcessDBQ

Dim OriginalIDIndex
Dim OriginalIDArray()
OriginalIDIndex = -1

' --- Kill Other wscript.exe
if (KillOtherWscriptEXE = 1) then
ProcessFileName = "wscript.exe"
Dim PIDsArray()
PIDsArrayIndex=0
CurrentProcessId = CurrProcessId
Set objWMIService = GetObject("winmgmts:{impersonationLevel=impersonate}!\\.\root\cimv2")
Set Processes = objWMIService.ExecQuery("Select * From Win32_Process where name='" & ProcessFileName & "'")
For Each Process in Processes
if (CurrentProcessId <> Process.ProcessID) then
Process.Terminate
end if
Next
end if
' --- Kill Other wscript.exe

TableArray = Array ("MailAttachmentV4","MailInfoV4","MailRCPV4","MailToV4")
Set SQLSourceConnection = CreateObject("ADODB.Connection")
SQLSourceConnString = "DRIVER={SQL Server};SERVER=" & SQLSourceServer & ";UID=" & SQLSourceUser & ";PWD=" & SQLSourcePassword & ";"
SQLSourceConnection.ConnectionTimeout = SQLConnectionTimeout
SQLSourceConnection.CommandTimeOut = SQLCommandTimeOut
SQLSourceConnection.Open SQLSourceConnString

Set SQLTargetConnection = CreateObject("ADODB.Connection")
SQLTargetConnString = "DRIVER={SQL Server};SERVER=" & SQLTargetServer & ";UID=" & SQLTargetUser & ";PWD=" & SQLTargetPassword & ";"
SQLTargetConnection.ConnectionTimeout = SQLConnectionTimeout
SQLTargetConnection.CommandTimeOut = SQLCommandTimeOut
SQLTargetConnection.Open SQLTargetConnString

Set SQLRecordSet = CreateObject("ADODB.RecordSet")
Set fso = CreateObject("Scripting.FileSystemObject")

ProcessDBDate = ""
ProcessDBMDF = ""
ProcessDBLDF = ""
ProcessDBQ = ""

' 檢查是否有未完成的 DB 合併
if (ProcessDBMDF = "") Then

ProcessDBMDF = ""
SQLString = "SELECT [Name] FROM sys.master_files Where [Name] like '" & DBPreFix & "%' order by [Name]"

Set SQLRecordSet = SQLTargetConnection.Execute(SQLString, RecordsAffected)
if not SQLRecordSet.EoF then
ProcessDBMDF = SQLRecordSet.Fields("Name")
ProcessDBLDF = ProcessDBMDF & "_log"
ProcessDBQ = Mid(ProcessDBMDF,2,5) & SelectQ(CInt(Mid(ProcessDBMDF,7,2)))
SQLRecordSet.Close
else
ProcessDBDate = ""
ProcessDBMDF = ""
ProcessDBLDF = ""
ProcessDBQ = ""
Set objSQLTargetPath = fso.GetFolder(SQLTargetPath)
Set objSQLTargetPathFiles = objSQLTargetPath.Files
For Each File In objSQLTargetPathFiles
if (Mid(File.Name,1,2) = DBPreFix) then
if (ProcessDBDate = "") then
if (Mid(File.Name,11,4) = ".mdf") then
ProcessDBDate = Mid(File.Name,3,8)
ProcessDBMDF = Replace(File.Name,".mdf","")
ProcessDBQ = Mid(File.Name,2,5) & SelectQ(CInt(Mid(File.Name,7,2)))
end if
else
if (Mid(File.Name,11,9) = "_log.ldf") then
if (Mid(File.Name,3,8) = ProcessDBDate) then
ProcessDBLDF = Replace(File.Name,".ldf","")
exit for
end if
end if
end if
end if
Next
' 掛上新 DB
if ( (ProcessDBMDF <> "") and (ProcessDBLDF <> "") ) Then
SQLString = ""
SQLString = SQLString & " CREATE DATABASE [" & ProcessDBMDF & "] ON ( FILENAME = N'" & SQLTargetPath & ProcessDBMDF & ".mdf" & "' ),"
SQLString = SQLString & " ( FILENAME = N'" & SQLTargetPath & ProcessDBLDF & ".ldf" & "' ) FOR ATTACH;"
Set SQLRecordSet = SQLTargetConnection.Execute(SQLString, RecordsAffected)
end if
end if
end if

if (ProcessDBMDF = "") Then

SQLString = "SELECT [Name], Len([Name]) as [NameLen], [Physical_Name] AS [FilePath] FROM sys.master_files Where [Name] like '" & DBPreFix & "%' order by [Name]"
Set SQLRecordSet = SQLSourceConnection.Execute(SQLString, RecordsAffected)
while not SQLRecordSet.EoF
SourceDBNameIndex = SourceDBNameIndex + 1
ReDim Preserve SourceDBNameArray(SourceDBNameIndex)
ReDim Preserve SourceDBNameLenArray(SourceDBNameIndex)
ReDim Preserve SourceDBFilePathArray(SourceDBNameIndex)
SourceDBNameArray(SourceDBNameIndex) = SQLRecordSet.Fields("Name")
SourceDBNameLenArray(SourceDBNameIndex) = SQLRecordSet.Fields("NameLen")
SourceDBFilePathArray(SourceDBNameIndex) = SQLRecordSet.Fields("FilePath")
SQLRecordSet.MoveNext
wend
SQLRecordSet.Close

ProcessDBDate = ""
ProcessDBMDF = ""
ProcessDBMDFPath = ""
ProcessDBLDF = ""
ProcessDBLDFPath = ""
ProcessDBQ = ""
for i = 0 to SourceDBNameIndex
if (Mid(SourceDBNameArray(i),1,2) = DBPreFix) then
if (ProcessDBDate = "") then
if (SourceDBNameLenArray(i) = 10) then
ProcessDBDate = Mid(SourceDBNameArray(i),3,8)
ProcessDBMDF = SourceDBNameArray(i)
ProcessDBMDFPath = SourceDBFilePathArray(i)
ProcessDBQ = Mid(SourceDBNameArray(i),2,5) & SelectQ(CInt(Mid(SourceDBNameArray(i),7,2)))
end if
else
if ( (SourceDBNameLenArray(i) = 14) and (Mid(SourceDBNameArray(i),11,4) = "_log") ) then
if (Mid(SourceDBNameArray(i),3,8) = ProcessDBDate) then
ProcessDBLDF = SourceDBNameArray(i)
ProcessDBLDFPath = SourceDBFilePathArray(i)
exit for
end if
end if
end if
end if
next
if ( (ProcessDBMDF <> "") and (ProcessDBLDF <> "") ) Then
SQLString = "SET NOCOUNT ON; USE [master];"
SQLString = SQLString & " ALTER DATABASE [" & ProcessDBMDF & "] SET  SINGLE_USER WITH ROLLBACK IMMEDIATE;"
SQLString = SQLString & " EXEC master.dbo.sp_detach_db @dbname = N'" & ProcessDBMDF & "', @keepfulltextindexfile=N'true';"
Set SQLRecordSet = SQLSourceConnection.Execute(SQLString, RecordsAffected)
fso.CopyFile SQLSourceUNCPath & Replace(ProcessDBMDFPath,SQLSourcePath,""), SQLTargetPath & ProcessDBMDF & ".mdf"
fso.CopyFile SQLSourceUNCPath & Replace(ProcessDBLDFPath,SQLSourcePath,""), SQLTargetPath & ProcessDBLDF & ".ldf"
fso.MoveFile SQLSourceUNCPath & Replace(ProcessDBMDFPath,SQLSourcePath,""), SQLSourceUNCPath & "Done_" & Replace(ProcessDBMDFPath,SQLSourcePath,"")
fso.MoveFile SQLSourceUNCPath & Replace(ProcessDBLDFPath,SQLSourcePath,""), SQLSourceUNCPath & "Done_" & Replace(ProcessDBLDFPath,SQLSourcePath,"")

' 掛上新 DB
SQLString = ""
SQLString = SQLString & " CREATE DATABASE [" & ProcessDBMDF & "] ON ( FILENAME = N'" & SQLTargetPath & ProcessDBMDF & ".mdf" & "' ),"
SQLString = SQLString & " ( FILENAME = N'" & SQLTargetPath & ProcessDBLDF & ".ldf" & "' ) FOR ATTACH;"
Set SQLRecordSet = SQLTargetConnection.Execute(SQLString, RecordsAffected)
End If
end if

' 檢查來源端是否有卸載的 DB
if (ProcessDBMDF = "") Then
Set objSQLSourcePath = fso.GetFolder(SQLSourceUNCPath)
Set objSQLSourcePathFiles = objSQLSourcePath.Files
For Each File In objSQLSourcePathFiles
if (Mid(File.Name,1,2) = DBPreFix) then
if (ProcessDBDate = "") then
if (Mid(File.Name,11,4) = ".mdf") then
ProcessDBDate = Mid(File.Name,3,8)
ProcessDBMDF = Replace(File.Name,".mdf","")
ProcessDBQ = Mid(File.Name,2,5) & SelectQ(CInt(Mid(File.Name,7,2)))
end if
else
if (Mid(File.Name,11,9) = "_log.ldf") then
if (Mid(File.Name,3,8) = ProcessDBDate) then
ProcessDBLDF = Replace(File.Name,".ldf","")
exit for
end if
end if
end if
end if
Next
' 複製卸載的 DB
if ( (ProcessDBMDF <> "") and (ProcessDBLDF <> "") ) Then
fso.CopyFile SQLSourceUNCPath & ProcessDBMDF & ".mdf", SQLTargetPath & ProcessDBMDF & ".mdf"
fso.CopyFile SQLSourceUNCPath & ProcessDBLDF & ".ldf", SQLTargetPath & ProcessDBLDF & ".ldf"
fso.MoveFile SQLSourceUNCPath & ProcessDBMDF & ".mdf", SQLSourceUNCPath & "Done_" & ProcessDBMDF & ".mdf"
fso.MoveFile SQLSourceUNCPath & ProcessDBLDF & ".ldf", SQLSourceUNCPath & "Done_" & ProcessDBLDF & ".ldf"

' 掛上新 DB
SQLString = ""
SQLString = SQLString & " CREATE DATABASE [" & ProcessDBMDF & "] ON ( FILENAME = N'" & SQLTargetPath & ProcessDBMDF & ".mdf" & "' ),"
SQLString = SQLString & " ( FILENAME = N'" & SQLTargetPath & ProcessDBLDF & ".ldf" & "' ) FOR ATTACH;"
Set SQLRecordSet = SQLTargetConnection.Execute(SQLString, RecordsAffected)
end if
end if
if ( (ProcessDBMDF <> "") and (ProcessDBLDF <> "") ) Then
MergeDBProcess()
End If

if (NextScript <> "") then

WScript.Sleep 5000
Set WshShell = CreateObject( "WScript.Shell" )
RunNext = WshShell.Run(NextScript, , False)
end if

wscript.quit

Function MergeDBProcess()
' 檢查 Q DB 是否存在
SQLString = "select [name] from sys.databases where [name] = '" & ProcessDBQ & "';"
Set SQLRecordSet = SQLTargetConnection.Execute(SQLString, RecordsAffected)
If SQLRecordSet.EoF Then

' 檢查是否有 Q 檔案可以掛上
If ( (fso.FileExists(SQLTargetPath & ProcessDBQ & ".mdf")) and (fso.FileExists(SQLTargetPath & ProcessDBQ & "_log.ldf")) ) Then
SQLString = "SET NOCOUNT ON;"
SQLString = SQLString & " CREATE DATABASE [" & ProcessDBQ & "] ON ( FILENAME = N'" & SQLTargetPath & ProcessDBQ & ".mdf" & "' ),"
SQLString = SQLString & " ( FILENAME = N'" & SQLTargetPath & ProcessDBQ & "_log.ldf" & "' ) FOR ATTACH;"
Set SQLRecordSet = SQLTargetConnection.Execute(SQLString, RecordsAffected)
else
CreateDB = CreateDBAndTable(SQLTargetPath,ProcessDBQ)
End if
end if

' 取出新 DB 序號 OriginalID
Do
SQLString = "SELECT Top 50 [OriginalID] FROM [" & ProcessDBMDF & "].[dbo].[MailInfoV4]"
Set SQLRecordSet = SQLTargetConnection.Execute(SQLString, RecordsAffected)
While not SQLRecordSet.EoF
OriginalIDIndex = OriginalIDIndex + 1
ReDim Preserve OriginalIDArray(OriginalIDIndex)
OriginalIDArray(OriginalIDIndex) = SQLRecordSet.Fields("OriginalID")
SQLRecordSet.MoveNext
Wend
SQLProcessIDs = ""
for i = 0 to OriginalIDIndex
If (SQLProcessIDs <> "") Then
SQLProcessIDs = SQLProcessIDs & " or "
End If
SQLProcessIDs = SQLProcessIDs & " [OriginalID] = '" & OriginalIDArray(i) & "'"
next
If (SQLProcessIDs <> "") Then
for i = 0 to UBound(TableArray)

' 刪除因程式中斷而產生的重複項目
SQLString = ""
SQLString = SQLString & "Delete from [" & ProcessDBQ & "].[dbo].[" & TableArray(i) & "] where"
SQLString = SQLString & SQLProcessIDs & ";"
Set SQLRecordSet = SQLTargetConnection.Execute(SQLString, RecordsAffected)

' 複製資料
SQLString = ""
SQLString = SQLString & " Insert Into [" & ProcessDBQ & "].[dbo].[" & TableArray(i) & "]"
SQLString = SQLString & " Select * from [" & ProcessDBMDF & "].[dbo].[" & TableArray(i) & "] where"
SQLString = SQLString & SQLProcessIDs & ";"
Set SQLRecordSet = SQLTargetConnection.Execute(SQLString, RecordsAffected)
next

SQLString = ""
for i = 0 to UBound(TableArray)
SQLString = SQLString & "Delete from [" & ProcessDBMDF & "].[dbo].[" & TableArray(i) & "] where"
SQLString = SQLString & SQLProcessIDs & ";"
next

Set SQLRecordSet = SQLTargetConnection.Execute(SQLString, RecordsAffected)
else
OriginalIDIndex = 0
End If
if (OriginalIDIndex = 49) then
OriginalIDIndex = -1
end if

Loop while (OriginalIDIndex = -1)

SQLString = "SET NOCOUNT ON;"
SQLString = SQLString & " ALTER DATABASE [" & ProcessDBMDF & "] SET  SINGLE_USER WITH ROLLBACK IMMEDIATE;"
SQLString = SQLString & " EXEC master.dbo.sp_detach_db @dbname = N'" & ProcessDBMDF & "', @keepfulltextindexfile=N'true';"
Set SQLRecordSet = SQLTargetConnection.Execute(SQLString, RecordsAffected)
fso.DeleteFile(SQLTargetPath & ProcessDBMDF & ".mdf")
fso.DeleteFile(SQLTargetPath & ProcessDBLDF & ".ldf")
fso.DeleteFile(SQLSourceUNCPath & "Done_" & ProcessDBMDF & ".mdf")
fso.DeleteFile(SQLSourceUNCPath & "Done_" & ProcessDBLDF & ".ldf")

' 再次執行本程式繼續合併
Set WshShell = WScript.CreateObject("WScript.Shell")
Return = WshShell.Run(WScript.ScriptFullName, 0, False)
wscript.quit

End Function

Function SelectQ(Month)
Select case Month
Case 1 SelectQ = 1
Case 2 SelectQ = 1
Case 3 SelectQ = 1
Case 4 SelectQ = 2
Case 5 SelectQ = 2
Case 6 SelectQ = 2
Case 7 SelectQ = 3
Case 8 SelectQ = 3
Case 9 SelectQ = 3
Case 10 SelectQ = 4
Case 11 SelectQ = 4
Case 12 SelectQ = 4
end Select
End Function

Function CreateDBAndTable(DBPath,TargetDBName)
SQLString = "CREATE DATABASE [" & TargetDBName & "] ON PRIMARY"
SQLString = SQLString & " (NAME = N'" & TargetDBName & "', FILENAME = N'" & DBPath & "\" & TargetDBName & ".mdf' , SIZE = 5120KB , FILEGROWTH = 10%)"
SQLString = SQLString & " LOG ON (NAME = N'" & TargetDBName & "_log', FILENAME = N'" & DBPath & "\" & TargetDBName & "_log.ldf' , SIZE = 1024KB , FILEGROWTH = 10%)"
Set SQLRecordSet = SQLTargetConnection.Execute(SQLString, RecordsAffected)
SQLString = "ALTER DATABASE [" & TargetDBName & "] SET RECOVERY SIMPLE;"
Set SQLRecordSet = SQLTargetConnection.Execute(SQLString, RecordsAffected)
SQLString = "CREATE TABLE [" & TargetDBName & "].[dbo].[MailAttachmentV4]("
SQLString = SQLString & " [OriginalID] [varchar](50) NOT NULL,"
SQLString = SQLString & " [FileName] [nvarchar](255) NOT NULL,"
SQLString = SQLString & " [Extension] [nvarchar](50) NOT NULL,"
SQLString = SQLString & " [Size] [bigint] NOT NULL,"
SQLString = SQLString & " CONSTRAINT [PK_MailAttachmentV4] PRIMARY KEY CLUSTERED ("
SQLString = SQLString & " [OriginalID] ASC,"
SQLString = SQLString & " [FileName] ASC) WITH ("
SQLString = SQLString & " PAD_INDEX = OFF,STATISTICS_NORECOMPUTE = OFF,"
SQLString = SQLString & " IGNORE_DUP_KEY = OFF,ALLOW_ROW_LOCKS = ON,"
SQLString = SQLString & " ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]) ON [PRIMARY]"
Set SQLRecordSet = SQLTargetConnection.Execute(SQLString, RecordsAffected)

SQLString = "CREATE TABLE [" & TargetDBName & "].[dbo].[MailInfoV4]("
SQLString = SQLString & " [OriginalID] [varchar](50) NOT NULL,"
SQLString = SQLString & " [OpDate] [datetime] NOT NULL,"
SQLString = SQLString & " [MailDate] [datetime] NOT NULL,"
SQLString = SQLString & " [MailFrom] [nvarchar](255) NOT NULL,"
SQLString = SQLString & " [Subject] [nvarchar](255) NOT NULL,"
SQLString = SQLString & " [MsgSize] [int] NOT NULL,"
SQLString = SQLString & " [ZipSize] [int] NOT NULL,"
SQLString = SQLString & " [MSG] [image] NOT NULL,"
SQLString = SQLString & " [RCP] [image] NOT NULL,"
SQLString = SQLString & " [CompressMode] [int] NOT NULL,"
SQLString = SQLString & " [Recipients] [nvarchar](max) NOT NULL,"
SQLString = SQLString & " [Attachments] [nvarchar](max) NOT NULL,"
SQLString = SQLString & " [Encoding] [int] NOT NULL,"
SQLString = SQLString & " CONSTRAINT [PK_MailInfoV4] PRIMARY KEY CLUSTERED ("
SQLString = SQLString & " [OriginalID] ASC) WITH ("
SQLString = SQLString & " PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF,"
SQLString = SQLString & " IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON,"
SQLString = SQLString & " ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]"
SQLString = SQLString & " ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]"
Set SQLRecordSet = SQLTargetConnection.Execute(SQLString, RecordsAffected)

SQLString = "CREATE TABLE [" & TargetDBName & "].[dbo].[MailRCPV4]("
SQLString = SQLString & " [OriginalID] [varchar](50) NOT NULL,"
SQLString = SQLString & " [MsgID] [varchar](50) NOT NULL,"
SQLString = SQLString & " [RCP] [image] NOT NULL,"
SQLString = SQLString & " [Encoding] [int] NOT NULL,"
SQLString = SQLString & " CONSTRAINT [PK_MailRCPV4] PRIMARY KEY CLUSTERED ("
SQLString = SQLString & " [OriginalID] ASC,"
SQLString = SQLString & " [MsgID] ASC) WITH ("
SQLString = SQLString & " PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF,"
SQLString = SQLString & " IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON,"
SQLString = SQLString & " ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]"
SQLString = SQLString & " ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]"
Set SQLRecordSet = SQLTargetConnection.Execute(SQLString, RecordsAffected)

SQLString = "CREATE TABLE [" & TargetDBName & "].[dbo].[MailToV4]("
SQLString = SQLString & " [OriginalID] [varchar](50) NOT NULL,"
SQLString = SQLString & " [MailTo] [nvarchar](255) NOT NULL,"
SQLString = SQLString & " CONSTRAINT [PK_MailToV4] PRIMARY KEY CLUSTERED ("
SQLString = SQLString & " [OriginalID] ASC,"
SQLString = SQLString & " [MailTo] ASC) WITH ("
SQLString = SQLString & " PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF,"
SQLString = SQLString & " IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON,"
SQLString = SQLString & " ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]) ON [PRIMARY]"
Set SQLRecordSet = SQLTargetConnection.Execute(SQLString, RecordsAffected)

End Function

Function CurrProcessId
Dim oShell, sCmd, oWMI, oChldPrcs, oCols, lOut
lOut = 0
Set oShell  = CreateObject("WScript.Shell")
Set oWMI    = GetObject(_
"winmgmts:{impersonationLevel=impersonate}!\\.\root\cimv2")
sCmd = "/K " & Left(CreateObject("Scriptlet.TypeLib").Guid, 38)
oShell.Run "%comspec% " & sCmd, 0
WScript.Sleep 100 'For healthier skin, get some sleep
Set oChldPrcs = oWMI.ExecQuery(_
"Select * From Win32_Process Where CommandLine Like '%" & sCmd & "'",,32)
For Each oCols In oChldPrcs
lOut = oCols.ParentProcessId 'get parent
oCols.Terminate 'process terminated
Exit For
Next
CurrProcessId = lOut
End Function


Posted by at
Labels: ,

沒有留言:

張貼留言

訂閱： 張貼留言 (Atom)