Introduction
Repeatedly, over the last 8 years, I have needed to do the same thing - back up SQL Server (actually - it started with Sybase 11) to pure SQL. I started out using Perl - but here is a pure VBScript evolution of the technique I devised.
Let us get one thing straight here - this is not an official technique. If you are administering a SQL Server database and you use this technique - you are totally on your own. Yes - I know there are all sorts of official tools like bulk copy and data translation services. However, this technique is useful sometimes, and is flexible.
The idea is simple. Create a single SQL script that removes all the data from a database and then restores it all to a pre-recorded state. This boils down to a whole load of DELETE and INSERT statements. It is something that is done quite a bit in the MySQL world, but I have not seen it done in SQL Server land apart from by a loony like me!
So - what sort of thing do I use this for?
- Backing all the data up out of a SQL 2005 database and inserting it into a SQL Server 2000 database of the same structure.
- Backing up all the data from a test database, updating the database image from CVS (to get the latest sproc changes), and then resetting the data to that from the old image. This is kind of the whole point - this technique backs up the data, not the structure.
- Storing database data in CVS - the output of the script is text SQL.
- Backup and restore with extra logic (you can fiddle with the script to put COVERTS etc. to mess with data types and stuff).
- Overnight backup over a slow network. This one is risky - but as the SQL file compresses really small, it is sometimes an alternative to trying to back up to binary. I would not recommend it for a production system.
- Sending people the data from a database via email or FTP without them having to restore a binary image. You just say - unzip and run this script.
- I am sure there are others - but I have long since forgotten.
The Code
So here it is! I am 100% certain that there are many situations in which it will break. If you find one, let me know. If you find solutions, please let me know! I have done a walk through below of how it works.
Option Explicit
Const server = "localhost"
Const trusted = FALSE
Const userId = "deployview"
Const password = "deployview"
Const dataBase = "dv"
Const useUnicode = TRUE
Const fileName = "Data.sql"
Dim db,i,connectString,fields,rs
Const adOpenStatic = 3
Const adLockReadOnly = 1
Const adCmdText = 1
Const adUseClient = 3
Const adLockBatchOptimistic = 4
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"
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
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
While cnt
cnt = False
Dim cfind
For cfind = 0 To ubound(tables)
Dim child
child = tables(cfind)
For i = 0 To rc
If pc(i, 1) = child Then
Dim prnt
prnt = pc(i, 0)
Dim pfind
For pfind = 0 To ubound(tables)
If tables(pfind) = prnt Then
If pfind > cfind Then
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
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
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
Public Sub DumpDBDataToFile(ado, fileName, tabs,dataBase,useUnicode)
Dim trc
trc=0
Dim fs
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
ts.WriteLine "SET NOCOUNT ON"
ts.WriteLine "GO"
ts.WriteLine "USE " & dataBase
ts.WriteLine "GO"
ts.WriteLine "SET IMPLICIT_TRANSACTIONS OFF"
ts.WriteLine "GO"
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
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
For Each t In tabs
ado.Execute "BEGIN TRANSACTION"
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
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
if trc mod 1000 = 0 Then
WScript.Echo "Total row count=" & trc
End If
rec.MoveNext
Wend
End If
rec.Close
ts.WriteLine _
"IF OBJECTPROPERTY ( object_id('" & t & "'),'TableHasIdentity') = 1 " + _
"SET IDENTITY_INSERT " & t & " OFF "
ts.WriteLine "GO"
Next
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
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
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
Function DBEscapeDate(myDate)
DBEscapeDate=DBEscapeString(DateLong(myDate))
End Function
Function DBEscapeString(myString)
DBEscapeString="'" & Replace(myString,"'","''") & "'"
End Function
Function DBEscapeNumber(myNumber)
If NOT IsNumeric(myNumber) Then myNumber=0
myNumber=myNumber*1.0
DBEscapeNumber=Replace(myNumber & "","'","''")
End Function
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)
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
How it Works
Step one is just to log onto the database using ADODB. This connects to the server using the OLEDB database provider. I have set it so it can use a trusted (Windows authentication) or SQL Server authentication connection.
Next, all the activity goes to the function DumpDBDataToFile. This opens the file to which the SQL is to be written. A constant at the top of the script sets if this is to be written in Unicode or not. I would recommend Unicode as SQL Server supports Unicode data fields. After it has the output file sorted, it needs to start creating the SQL.
A lot of the development work I did on this surrounded speed. Not the speed of writing the SQL backup, but the speed of the resulting SQL reloading the database. I found that, counter to expectation, it worked faster if implicit transactions are turned off.
Before any Delete or Insert statements can be used reliably in the database restore, all triggers have to be turned off. By joining the sysobjects table to itself, it is possible to get all the active triggers and the tables on which they depend. The SQL to do this is in the script, but here it is again:
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
The script stores the trigger information so that it can create SQL to turn off all active triggers at the head of the output file and then turn them back on at the tail.
Once the triggers are all off, the script needs to write out the Delete statements to clear any data from the tables before new data is inserted. To do this, the dependency order of the tables needs to be worked out. This is done via sysobjects once again, in the function SortDepOrder. Once this is found, the Delete statements work in the opposite order to the Inserts.
Just to explain, the TABLOCKX on the DELETE statements just mean that the server will use a table lock straight away, and so saves a lot of lock escalation and performance issues. Chances are it may not have much effect most of the time; however, it appears to help sometimes. Any and all comments welcome on the subject.
Once all the Deletes are out the way, the Inserts which reload the data are created. The trick here is to know the format of each Insert statement. The field names can be acquired from the result set. Then, the way the data is added into the Insert statement is worked out from the data type of the elements in the fields of the recordset. The code below does that bit:
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
To be honest, this is probably the most important piece of the whole script. It is also where the script is going to fail for data types for which I have not yet accounted. DBEscapeNumber, DBEscapeString, BinToHex are all functions in the script which I have written to make string representations which can be put straight into SQL. They take care of things like date format and escaping of ' in CHAR/VARCHAR.
Running the Script
To run, set the constants at the top of the script and then run it from cscript, like this:
C:\Documents and Settings\user\Desktop>cscript DBBackup.vbs
The script will report its progress like this (from a real example):
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
So here are some extracts from a produced SQL file:
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
Note that the script turns IDENTITY_INSERT on and off to allow exact re-creation of the identity columns on the tables.
That's all Folks
This is a bit fast and brief. I hope to add more explanation in the future, and I will try to answer any questions asked.
For more stuff like this - see my blog index page: http://nerds-central.blogspot.com/2008/01/excel-vbscript-index-page.html.
