VBScript 将 SQL Server 数据备份到纯 SQL
4.85/5 (8投票s)
一个 VBScript，用于创建 Delete 和 Insert 语句来备份 SQL Server 中的数据。
引言
在过去 8 年里，我反复需要做同一件事——将 SQL Server（实际上，一开始是 Sybase 11）备份到纯 SQL。我最初使用的是 Perl，但这里是我设计的技术的一种纯 VBScript 实现。
这里有一点要说清楚——这不是一种官方技术。如果您正在管理 SQL Server 数据库并使用此技术——您将完全自负风险。是的——我知道有各种官方工具，如 BULK COPY 和数据转换服务。然而，这项技术有时很有用，而且很灵活。
这个想法很简单。创建一个单一的 SQL 脚本，该脚本会删除数据库中的所有数据，然后将其全部恢复到预先录制的状态。这归结为大量的 DELETE 和 INSERT 语句。这在 MySQL 世界中很常见，但在 SQL Server 领域我除了像我这样奇怪的人之外，还没见过有人这样做！
那么——我用它来做什么呢？
- 将 SQL 2005 数据库中的所有数据备份出来，并将其插入到具有相同结构的 SQL Server 2000 数据库中。
- 备份测试数据库中的所有数据，从 CVS 更新数据库映像（以获取最新的 sproc 更改），然后将数据重置为旧映像中的数据。这基本上就是重点——这项技术备份的是数据，*而不是*结构。
- 将数据库数据存储在 CVS 中——脚本的输出是文本 SQL。
- 使用额外的逻辑进行备份和恢复（您可以修改脚本以添加 COVERTS 等来处理数据类型和内容）。
- 通过慢速网络进行夜间备份。这有风险——但由于 SQL 文件压缩后体积非常小，有时可以作为二进制备份的替代方案。我不推荐在生产系统中使用。
- 通过电子邮件或 FTP 将数据库数据发送给他人，而无需他们恢复二进制映像。您只需说——解压并运行此脚本。
- 我确定还有其他用途——但我早已忘记了。
代码
所以，来了！我 100% 确定在许多情况下它会失效。如果您发现一个，请告诉我。如果您找到解决方案，也请告诉我！*下面我将详细介绍它的工作原理。*
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' Copyright (c) 2008, Dr Alexander J. Turner
' All rights reserved.
'
' Redistribution and use in source and binary forms, with or without
' modification, are permitted provided that the following conditions are met:
' ' Redistributions of source code must retain the above copyright
' notice, this list of conditions and the following disclaimer.
' ' Redistributions in binary form must reproduce the above copyright
' notice, this list of conditions and the following disclaimer in the
' documentation and/or other materials provided with the distribution.
' ' Neither the name of the nor the
' names of its contributors may be used to endorse or promote products
' derived from this software without specific prior written permission.
'
' THIS SOFTWARE IS PROVIDED BY ``AS IS'' AND ANY
' EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED
' WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE ARE
' DISCLAIMED. IN NO EVENT SHALL BE LIABLE FOR ANY
' DIRECT, INDIRECT, INCIDENTAL, SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES
' (INCLUDING, BUT NOT LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES;
' LOSS OF USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND
' ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR TORT
' (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE USE OF THIS
' SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Option Explicit
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' Configure this script then run from CMD using cscript
'
' Use the constants below to configure the script
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' Database server name
Const server = "localhost"
' Use trusted (windows authenitcation) or standard (SQL Server authentication)
Const trusted = FALSE
' Database user name - not needed for trusted connection
Const userId = "deployview"
' Database password - not needed for trusted connection
Const password = "deployview"
' Database
Const dataBase = "dv"
' Set to true to create a unicode SQL File (safest)
' and false for an asci one (asci will loose data if you have
' unicode fields in the db)
Const useUnicode = TRUE
' Set the name of the created file
Const fileName = "Data.sql"
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' DO NOT EDIT BELOW THIS LINE UNLESS YOU WANT TO ENCHANCE/CHANGE
' THE FUNCTIONALLITY OF THE SCRIPT
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' Variables used in the script
Dim db,i,connectString,fields,rs
' Userful ADODB constants
Const adOpenStatic = 3
Const adLockReadOnly = 1
Const adCmdText = 1
Const adUseClient = 3
Const adLockBatchOptimistic = 4
' SQL that is used to get important info
Dim GetTriggers,GetUserTables_SQLServer,GetKeyOrder
GetTriggers = "SELECT spus.name + '.' + sp.name, s.name FROM " & _
" sysobjects s inner join sysobjects" & _
" sp on s.parent_obj = sp.id inner join sysusers spus on sp.uid = spus.uid " & _
" WHERE s.xtype='TR' AND OBJECTPROPERTY(s.[id], 'ExecIsTriggerDisabled')=0"
GetUserTables_SQLServer = "SELECT usrs.name + '.' + obs.name 'Full Name' FROM " & _
" sysobjects obs, sysusers usrs WHERE obs.xtype = 'U' AND obs.uid = usrs.uid "
GetKeyOrder = "SELECT usrs1.name + '.' + o.name , usrs2.name + '.' + oo.name FROM " & _
" sysobjects o, sysforeignkeys f ,sysobjects oo,sysusers usrs1,sysusers usrs2 " & _
" WHERE o.id = f.rkeyid AND oo.id = f.fkeyid AND usrs1.uid=o.uid AND usrs2.uid=oo.uid"
' Connect to the db
If trusted Then
connectString="Provider=SQLNCLI;Server=" & server & _
";Database=" & dataBase & ";Trusted_Connection=yes;"
Else
connectString="Provider=SQLNCLI;Server=" & server & _
";Database=" & dataBase & ";Uid=" & _
userId & ";Pwd=" & password & ";"
End If
Set db = CreateObject("ADODB.Connection")
db.Open connectString
db.Execute "USE " + dataBase
DumpDBDataToFile db,fileName,GetUserTables(db),dataBase,useUnicode
WScript.Echo "All done"
WScript.Quit
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' Pass in a connection and an array of table names
' and it will sort the tables names into dependency order.
' IE if table B depends on table A then A will be earlier in
' the list than B. Again, if B m->1 A, then A comes first
' in the list.
'
' Author: Alexander J Turner - 1 May 2008
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Public Sub SortDepOrder(ado,tables)
Dim recset
Set recset = GetDisconRS(ado,GetKeyOrder)
Dim inpa
Dim rc
Dim i
i = 0
rc = recset.RecordCount
Dim pc()
ReDim pc(rc, 2)
recset.MoveFirst
While Not recset.EOF
pc(i, 0) = recset.fields(0)
pc(i, 1) = recset.fields(1)
recset.MoveNext
i = i + 1
Wend
recset.Close
Dim cnt
cnt = True
' Keep sorting until no changes are made
While cnt
cnt = False
Dim cfind
' scan over all elements
For cfind = 0 To ubound(tables)
Dim child
child = tables(cfind)
' see if the current element is a reference child
For i = 0 To rc
' if we find a child find the parent
If pc(i, 1) = child Then
' found child
' so get parent
Dim prnt
prnt = pc(i, 0)
Dim pfind
' loop over the whole input looking for the parent
For pfind = 0 To ubound(tables)
' if we find it
If tables(pfind) = prnt Then
' and it is after the child, swap
If pfind > cfind Then
' parent lower than child swap
Dim tmp
tmp = tables(pfind)
tables(pfind) = tables(cfind)
tables(cfind) = tmp
WScript.Echo tables(pfind) & " X " & tables(cfind)
cnt = True
End If
End If
Next
End If
Next
Next
Wend
End Sub
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' Pass an database connection and get an array of all the user
' tables
'
' Author: Alexander J Turner - 1 May 2008
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Public Function GetUserTables(ado)
Dim tabs(),ntab
ado.Execute "BEGIN TRANSACTION"
Dim recset
Set recset = GetDisconRS(ado,GetUserTables_SQLServer)
recset.MoveFirst
ntab=0
While Not recset.EOF
ntab=ntab+1
recset.MoveNext
Wend
recset.MoveFirst
redim tabs(ntab-1)
ntab=0
While Not recset.EOF
tabs(ntab)= recset.fields(0).value
recset.MoveNext
ntab=ntab+1
Wend
recset.Close
ado.Execute "COMMIT"
GetUserTables = tabs
Exit Function
End Function
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' Pass an database connection and get an array of all the enabled user
' table triggers as TABLE,TRIGGER strings
'
' Author: Alexander J Turner - 1 May 2008
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Public Function GetUserTriggers(ado)
Dim trigs(),ntrig
ado.Execute "BEGIN TRANSACTION"
Dim recset
Set recset = GetDisconRS(ado,GetTriggers)
recset.MoveFirst
ntrig=0
While Not recset.EOF
ntrig=ntrig+1
recset.MoveNext
Wend
recset.MoveFirst
redim trigs(ntrig-1)
ntrig=0
While Not recset.EOF
trigs(ntrig)= recset.fields(0).value & "," & recset.fields(1)
recset.MoveNext
ntrig=ntrig+1
Wend
recset.Close
ado.Execute "COMMIT"
GetUserTriggers = trigs
Exit Function
End Function
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' This function writes SQL to restore all the data into a set of tables
' without changing the structure - IE a data only backup of the tables
' in pure SQL (ie loads of delete and insert statements).
'
' Parameters:
' ado - a ADODB database connection objects
' fileName - the file to which to write the SQL
' tabs - a list of tables owner.name (like dbo.mytab)
' dataBase - the name of the database the tables are in
' userUnicode - is the file to be unicode (recommended)
'
' Author: Alexander J Turner - 1 May 2008
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Public Sub DumpDBDataToFile(ado, fileName, tabs,dataBase,useUnicode)
Dim trc
trc=0
Dim fs
' Open the output file and select the chosen format
Set fs = CreateObject("Scripting.FileSystemObject")
Dim ts
If useUnicode Then
Set ts = fs.OpenTextFile(fileName, 2, True,-1)
Else
Set ts = fs.OpenTextFile(fileName, 2, True)
End If
Dim t,tt
Dim rec
Dim c
Dim trigs
' Putting no count in the output script makes it run faster
ts.WriteLine "SET NOCOUNT ON"
ts.WriteLine "GO"
ts.WriteLine "USE " & dataBase
ts.WriteLine "GO"
' I had trouble with transactions, though under some conditions
' running with transactions was faster, often the transactions are
' so large that SQL Server 'jams up' and takes ages (even hours) to
' recover - this way is safer!
ts.WriteLine "SET IMPLICIT_TRANSACTIONS OFF"
ts.WriteLine "GO"
' It is important to turn off all enabled triggers else the db will
' be updating as it is loading and so all sorts of problems will ensue
trigs=GetUserTriggers(ado)
For Each t In trigs
t=Split(t,",")
For Each tt In tabs
If UCase(Trim(tt))= UCase(Trim(t(0))) Then
WScript.Echo "Disabling trigger: " & t(1) & " on " & t(0)
ts.WriteLine "ALTER TABLE " & t(0) & " DISABLE TRIGGER " & t(1)
ts.WriteLine "GO"
Exit For
End If
Next
Next
' sort the dependency order so that deletes and inserts will fit
' with FK restraints. There might be a way of turning off the restraints
' but this works as well.
WScript.Echo "Sorting table order"
SortDepOrder ado, tabs
For c = ubound(tabs) To 0 Step -1
ts.WriteLine "DELETE FROM " & tabs(c) & " WITH (TABLOCKX) "
ts.WriteLine "GO"
Next
' Now we write out the inserts to restore the data. The tables are
' loaded in the opposite order to that in which they are deleted from
For Each t In tabs
ado.Execute "BEGIN TRANSACTION"
' This allows insertion into identity columns
ts.WriteLine _
"IF OBJECTPROPERTY ( object_id('" & t & "'),'TableHasIdentity') = 1 " + _
"SET IDENTITY_INSERT " & t & " ON "
ts.WriteLine "GO"
Set rec = GetDisconRS(ado,"SELECT * FROM " & t)
Dim sql
Dim sql1
Dim first
first = True
If Not rec.EOF Then
rec.MoveFirst
While Not rec.EOF
Dim i
If first Then
sql1 = "INSERT INTO " & t & " ("
For i = 0 To rec.fields.count - 1
If i > 0 Then sql1 = sql1 + ","
sql1 = sql1 + rec.fields(i).name
Next
sql1 = sql1 + ") VALUES ("
first = False
WScript.Echo "Dumping " & t
End If
sql = sql1
Dim vt
Dim f
' Use the returning data type to work out how to escape the SQL
' this is far from perfect, I am sure that some translations
' will not work properly, but for now it seems to work on the DBs
' I am working with
For i = 0 To rec.fields.count - 1
f = rec.fields(i).value
vt = varType(f)
If vt = 1 Then
f = "Null"
ElseIf vt = 2 Or vt = 3 Or vt = 4 Or vt = 5 Or vt = 6 Or vt = 14 Then
f = DBEscapeNumber(CStr(f))
ElseIf vt = 11 Then
If vt Then
f = "1"
Else
f = "0"
End If
ElseIf vt = 8 Then
f = DBEscapeString(CStr(f))
ElseIf vt = 7 Then
f = DBEscapeDate(CStr(f))
ElseIf vt = 17 Then
f = "0x" + Right( "0" & Hex(f),2)
ElseIf vt = 8209 Then
f = "0x" + BinToHex(f)
Else
WScript.Echo "Could not reformat", "Table=" & _
t & " Col=" & rec.fields(i).name & " vt=" & vt
WScript.Quit
End If
If i > 0 Then sql = sql + ","
sql = sql + f
Next
sql = sql + ")"
ts.WriteLine sql
ts.WriteLine "GO"
trc=trc+1
' I like to see some record of what is going on
if trc mod 1000 = 0 Then
WScript.Echo "Total row count=" & trc
End If
rec.MoveNext
Wend
End If
rec.Close
' Turn back on normal identity rules
' It would be better to check if identity insert was on before we
' turned it off - this way we might turn it off when it is supposed to
' on for the DBs normal function. I should fix this some time soon
ts.WriteLine _
"IF OBJECTPROPERTY ( object_id('" & t & "'),'TableHasIdentity') = 1 " + _
"SET IDENTITY_INSERT " & t & " OFF "
ts.WriteLine "GO"
Next
' Turn back on triggers
For Each t In trigs
t=Split(t,",")
For Each tt In tabs
If UCase(Trim(tt))= UCase(Trim(t(0))) Then
WScript.Echo "Enabling trigger: " & t(1) & " on " & t(0)
ts.WriteLine "ALTER TABLE " & t(0) & " ENABLE TRIGGER " & t(1)
ts.WriteLine "GO"
Exit For
End If
Next
Next
ts.Close
End Sub
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' This function returns a disconnected RS
' given a connection to the db and some SQL
'
' Author: Alexander J Turner - 1 May 2008
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Function GetDisconRS(ado,sql)
Dim recset
Set recset = CreateObject("ADODB.Recordset")
recset.CursorLocation = adUseClient
recset.CursorType = adOpenStatic
recset.LockType = adLockBatchOptimistic
recset.Open sql, ado, , , adCmdText
Set recset.ActiveConnection = Nothing
Set GetDisconRS = recset
End Function
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' Given a variable of type Date returns a variable of type String in
' long date format in English. For example a Date 01/01/2008 will
' become "1 January 2008". If that passed variable is a String, not
' a Date, then the results will still be a long date if VBScript can
' parse the passed String as a Date. However, the Date created will
' be dependent upon the local in which VBScript is running.
'
' Author: Alexander J Turner - 12 Feb 2008
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Function DateLong(myDate)
Dim months
months=Split("january,february,march,april,may,june,july,august," & _
"september,october,november,december",",")
DateLong= _
DatePart("D",mydate) & " " & _
months( DatePart("M",myDate)-1) & " " & _
DatePart("YYYY",mydate)
End Function
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' Given any variable, will return a String which is safe for direct
' inclusion in an SQL Server SQL Statement. E.g. 01/01/2008 will
' result in '1 January 2008'. Note that the ' marks are included imn
' the returned String.
'
' Author: Alexander J Turner - 12 Feb 2008
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Function DBEscapeDate(myDate)
' The full String escape should never be required but it is here
' to ensure that a malevalent injection cannot cause
' commands to be passed via a Date field
DBEscapeDate=DBEscapeString(DateLong(myDate))
End Function
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' Given any variable, will return a String which is safe for direct
' inclusion in an SQL Server SQL Statement.
' Note that the ' marks are included in the returned String.
'
' Author: Alexander J Turner - 12 Feb 2008
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Function DBEscapeString(myString)
DBEscapeString="'" & Replace(myString,"'","''") & "'"
End Function
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' Given any variable, will return a Number which is safe for direct
' inclusion in an SQL Server SQL Statement. Note than non numeric
' values will be converted to 0.
'
' Author: Alexander J Turner - 12 Feb 2008
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Function DBEscapeNumber(myNumber)
If NOT IsNumeric(myNumber) Then myNumber=0
myNumber=myNumber*1.0
DBEscapeNumber=Replace(myNumber & "","'","''")
End Function
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' Pass in an array of numbers (byte or between 0 and 255)
' and get out a string of hex representing the same numbers
'
' Author: Alexander J Turner - 1 May 2008
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Function BinToHex(data)
Dim ret
Dim l
Dim i
Dim lb
Dim h
Dim d
Dim o
lb = LBound(data) - 1
l = UBound(data) - LBound(data) + 1
ret = String(l * 2, "0")
Redim o(l-1)
' Use arrays and join as just adding to the end of a
' string scales badly as the length of the string increases
For i = 1 To l
d = 255 and ascb(midb(data,i,1))
If d > 15 Then
o(i-1) = Hex(d)
Else
o(i-1) = "0" + Hex(d)
End If
Next
BinToHex = Join(o,"")
End Function
工作原理
第一步是使用 ADODB 登录数据库。它使用 OLEDB 数据库提供程序连接到服务器。我已经设置好了，可以使用受信任的（Windows 身份验证）或 SQL Server 身份验证连接。
接下来，所有活动都转到函数 DumpDBDataToFile。该函数打开要将 SQL 写入的文件。脚本顶部的常量设置是否以 Unicode 格式写入。我推荐 Unicode，因为 SQL Server 支持 Unicode 数据字段。在整理好输出文件后，它需要开始创建 SQL。
我在此方面所做的许多开发工作都围绕着速度。不是 SQL 备份写入的速度，而是生成的 SQL 重新加载数据库的速度。我发现，与预期相反，关闭隐式事务会运行得更快。
在数据库恢复中可靠地使用 Delete 或 Insert 语句之前，必须关闭所有触发器。通过将 sysobjects 表与自身连接，可以获取所有活动触发器及其所依赖的表。脚本中包含执行此操作的 SQL，但这里再次提供：
SELECT spus.name + '.' + sp.name, s.name
FROM sysobjects s inner join sysobjects sp on
s.parent_obj = sp.id inner join sysusers spus on sp.uid = spus.uid
WHERE s.xtype='TR' AND OBJECTPROPERTY(s.[id], 'ExecIsTriggerDisabled')=0
脚本存储触发器信息，以便在输出文件的头部创建 SQL 来关闭所有活动触发器，然后在尾部重新打开它们。
触发器全部关闭后，脚本需要写出 Delete 语句，以清除表中的任何数据，然后再插入新数据。为此，需要确定表的依赖顺序。这再次通过 sysobjects 完成，在函数 SortDepOrder 中。一旦找到这个顺序，Delete 语句的执行顺序将与 Insert 语句的执行顺序相反。
需要说明的是，DELETE 语句上的 TABLOCKX 意味着服务器将立即使用表锁，从而节省大量的锁升级和性能问题。很可能在大多数情况下它不会有太大影响；然而，有时它似乎有帮助。关于这一点，欢迎提出任何意见。
一旦所有 Delete 语句都处理完毕，就会创建重新加载数据的 Insert 语句。这里的技巧是知道每个 Insert 语句的格式。字段名称可以从结果集中获取。然后，数据添加到 Insert 语句的方式是根据记录集中字段元素的 数据类型 来确定的。下面的代码就是完成这一部分：
For i = 0 To rec.fields.count - 1
f = rec.fields(i).value
vt = varType(f)
If vt = 1 Then
f = "Null"
ElseIf vt = 2 Or vt = 3 Or vt = 4 Or vt = 5 Or vt = 6 Or vt = 14 Then
f = DBEscapeNumber(CStr(f))
ElseIf vt = 11 Then
If vt Then
f = "1"
Else
f = "0"
End If
ElseIf vt = 8 Then
f = DBEscapeString(CStr(f))
ElseIf vt = 7 Then
f = DBEscapeDate(CStr(f))
ElseIf vt = 17 Then
f = "0x" + Right( "0" & Hex(f),2)
ElseIf vt = 8209 Then
f = "0x" + BinToHex(f)
Else
WScript.Echo "Could not reformat", "Table=" & _
t & " Col=" & rec.fields(i).name & " vt=" & vt
WScript.Quit
End If
If i > 0 Then sql = sql + ","
sql = sql + f
Next
说实话，这可能是整个脚本中最重要的部分。这也是脚本在处理我尚未处理的数据类型时会失败的地方。DBEscapeNumber、DBEscapeString、BinToHex 都是我在脚本中编写的函数，用于创建可以直接放入 SQL 的字符串表示。它们会处理诸如日期格式和 CHAR/VARCHAR 中 ' 的转义等问题。
运行脚本
要运行，请设置脚本顶部的常量，然后像这样使用 cscript 运行它：
C:\Documents and Settings\user\Desktop>cscript DBBackup.vbs
脚本将像这样报告其进度（来自真实示例）：
Microsoft (R) Windows Script Host Version 5.6
Copyright (C) Microsoft Corporation 1996-2001. All rights reserved.
Disabling trigger: Trig_ChckBusnUnitProjSlots on dbo.T_ProjBusnUnit
Disabling trigger: TRG_ChckSlotStat on dbo.T_Slot
Disabling trigger: TRIG_BusnUnitUpdate on dbo.T_BusnUnit
Disabling trigger: Trig_ChckLocnSlots on dbo.T_Locn
Disabling trigger: TRIG_LocnUpdate on dbo.T_Locn
Disabling trigger: Trig_ChckLocnProjSlots on dbo.T_LocnProj
Disabling trigger: TRIG_MchnPropTypeUC on dbo.T_MchnPrpsType
Disabling trigger: TRIG_UpdtSlotSlotHist1 on dbo.T_Slot
Disabling trigger: TRIG_UpdtSlotSlotHist2 on dbo.T_SlotStatHist
Disabling trigger: TRIG_UserNameChck on dbo.T_User
Disabling trigger: Trig_ChckUserBusnUnitSlots on dbo.T_UserBusnUnit
Sorting table order
dbo.T_Bndl X dbo.T_Proj
dbo.T_BndlDepl X dbo.T_Depl
dbo.T_BndlPrps X dbo.T_Bndl
dbo.T_BndlSlot X dbo.T_Slot
dbo.T_BndlTypeBULocn X dbo.T_BusnUnit
dbo.T_BusnUnit X dbo.T_Locn
dbo.T_BndlTypeMchn X dbo.T_Mchn
dbo.T_BndlTypeSlotCata X dbo.T_SlotCata
dbo.T_BndlTypeBULocn X dbo.T_BusnUnit
dbo.T_DyncExpn X dbo.T_DyncExpnType
dbo.T_RedyTrak X dbo.T_TrakStat
dbo.T_TrakStat X dbo.T_TrakStep
dbo.T_TrakStep X dbo.T_User
dbo.T_SlotEvntTrig X dbo.T_SlotStatType
dbo.T_SlotHistData X dbo.T_SlotHistDataType
dbo.T_SlotHistDataType X dbo.T_SlotStatHist
dbo.T_SlotHistData X dbo.T_SlotHistDataType
dbo.T_RedyTrak X dbo.T_TrakStat
dbo.T_TrakStat X dbo.T_TrakStep
dbo.T_RedyTrak X dbo.T_TrakStat
dbo.T_UserEvnt X dbo.T_UserEvntStat
dbo.T_UserEvntStat X dbo.T_UserEvntType
dbo.T_UserEvnt X dbo.T_UserEvntStat
dbo.T_Slot X dbo.T_Locn
dbo.T_Locn X dbo.T_SlotCata
dbo.T_Slot X dbo.T_Locn
dbo.T_Mchn X dbo.T_User
dbo.T_BusnUnit X dbo.T_BusnUnitType
dbo.T_BndlTypeMchn X dbo.T_Mchn
dbo.T_SlotCata X dbo.T_Actn
dbo.T_Locn X dbo.T_Lang
dbo.T_Lang X dbo.T_LocnType
dbo.T_User X dbo.T_Locn
dbo.T_Slot X dbo.T_SlotCata
dbo.T_MchnSlot X dbo.T_Slot
dbo.T_Locn X dbo.T_Lang
dbo.T_User X dbo.T_Locn
Dumping dbo.T_Proj
Dumping dbo.T_Depl
Dumping dbo.T_Bndl
Dumping dbo.T_Actn
Dumping dbo.T_BndlType
Dumping dbo.T_LocnType
Dumping dbo.T_Lang
Dumping dbo.T_BndlTypePrps
Dumping dbo.T_SlotCata
Dumping dbo.T_BrknData
Dumping dbo.T_BusnUnitType
Dumping dbo.T_BusnUnitPrnt
Dumping dbo.T_BusnUnitTree
Dumping dbo.T_BusnUnit
Dumping dbo.T_CodeBook
Total row count=1000
Total row count=2000
...
Dumping dbo.T_ActnSubActn
Enabling trigger: Trig_ChckBusnUnitProjSlots on dbo.T_ProjBusnUnit
Enabling trigger: TRG_ChckSlotStat on dbo.T_Slot
Enabling trigger: TRIG_BusnUnitUpdate on dbo.T_BusnUnit
Enabling trigger: Trig_ChckLocnSlots on dbo.T_Locn
Enabling trigger: TRIG_LocnUpdate on dbo.T_Locn
Enabling trigger: Trig_ChckLocnProjSlots on dbo.T_LocnProj
Enabling trigger: TRIG_MchnPropTypeUC on dbo.T_MchnPrpsType
Enabling trigger: TRIG_UpdtSlotSlotHist1 on dbo.T_Slot
Enabling trigger: TRIG_UpdtSlotSlotHist2 on dbo.T_SlotStatHist
Enabling trigger: TRIG_UserNameChck on dbo.T_User
Enabling trigger: Trig_ChckUserBusnUnitSlots on dbo.T_UserBusnUnit
All done
所以这里是一些生成的 SQL 文件中的摘录：
SET NOCOUNT ON
GO
USE dv
GO
SET IMPLICIT_TRANSACTIONS OFF
GO
ALTER TABLE dbo.T_ProjBusnUnit DISABLE TRIGGER Trig_ChckBusnUnitProjSlots
GO
ALTER TABLE dbo.T_Slot DISABLE TRIGGER TRG_ChckSlotStat
GO
ALTER TABLE dbo.T_BusnUnit DISABLE TRIGGER TRIG_BusnUnitUpdate
GO
ALTER TABLE dbo.T_Locn DISABLE TRIGGER Trig_ChckLocnSlots
GO
...
DELETE FROM dbo.T_AuthRuleDlig WITH (TABLOCKX)
GO
DELETE FROM dbo.T_AuthRuleBU WITH (TABLOCKX)
GO
DELETE FROM dbo.T_AuthRule WITH (TABLOCKX)
GO
DELETE FROM dbo.T_ActnSubActn WITH (TABLOCKX)
GO
...
IF OBJECTPROPERTY ( object_id('dbo.T_CodeBook'),'TableHasIdentity') = 1
SET IDENTITY_INSERT dbo.T_CodeBook ON
INSERT INTO dbo.T_CodeBook (Sequ,Valu) VALUES (559,0xF5B2AADA26EAA6CE)
GO
INSERT INTO dbo.T_CodeBook (Sequ,Valu) VALUES (560,0xEDAC5014F0F50B0D)
GO
...
IF OBJECTPROPERTY ( object_id('dbo.T_MchnPrps'),'TableHasIdentity') = 1
SET IDENTITY_INSERT dbo.T_MchnPrps ON
GO
INSERT INTO dbo.T_MchnPrps (MchnId,Sequ,Type,Data,Prnt)
VALUES (1,1,1,'RbsManuf1',1)
GO
INSERT INTO dbo.T_MchnPrps (MchnId,Sequ,Type,Data,Prnt)
VALUES (1,2,2,'IBM T42',1)
GO
请注意，脚本会打开和关闭 IDENTITY_INSERT，以允许精确地重新创建表上的标识列。
就到这里，各位
这有点快而且简略。我希望将来能添加更多解释，并且我会尽量回答任何问题。
有关更多此类内容，请参阅我的博客索引页：http://nerds-central.blogspot.com/2008/01/excel-vbscript-index-page.html。