-
June 18th, 2002, 12:25 PM
#1
Dumping SQL data to a text file
I noticed this morning that I now have only one tutorial on the front page of the tutorials form. This, /me thinks, is a state of affairs that cannot be allowed to continue (j/k - autobanning means that there seems to be a whole lot more people posting to this forum these days, which, on average, is probably a good thing). So I thought I'd have another dig in my big bag of goodies for you're viewing pleasure.
Objectives
I wanted to write a WSH script that dynamically generated SQL scripts containing the data from a given database, or from a given table within a database, formated as SQL INSERT statements. Why would I want to do this? I had a couple or reasons.
1...I wanted to have a backup of the data that I could edit and run from within isql
2...MSSQL Backups store all the information within a field, so a field defined a a VarChar(255) will store 255 characters, even if the field only contains 1 character. By using trim statements this script reduces the footprint size of a backup. My 417Meg database compresses down to a 44meg backup using MSSQL, but with my script I can get that down to 17mg. I can even zip the generated SQL script file and get the whole database down to under 2 meg. Over a dial-up thats a big difference .
3...I often want to syncronise a specific table between our production and staging servers so the functionallity that this script offers, in that it can get snapshots of a single table that I can run in iSql, is quite useful.
4...The more swanky features of MSSQL in terms of replication and syncronisation are unreliable over a dial-up. If you lose your connection mid-stream you can end up having to reboot. (Good old M$ -eh ) Having done a backup with my WSH script I can get at it with nothing more comlex than FTP.
Notes
<NOTE>I'm not reccomending this as an alternative to regular backups.</NOTE>. You should still continue to do a full backup every week and differential backups on a daily basis. You should also set the expiration of the full backups to a miniumn of three weeks (or three full backups) so that you get proper grandfather, father, son historical information.
That said this is a quick and dirty approach that I have found really useful for syncronisation between production and staging servers. You'll need replica tables on your production and staging server. If you don't already have the db/table created on your stanging server you can use the 'Generate SQL scripts' option in the MSSQL Enterprise Manger. It is a good idea to keep an updated copy of these scripts for each of your databases for disaster recovery.
Utilisation
Usage: dbbup <--Server|/s Name of server>
<--Database|/d Name of Database>
<--UserName|/u Username for Database>
[--Password|/p Password Database]
[--Table|/t Name of table]
[--Verbose|/v]
[--Help|/?]>
> filename.txt
Examples:
Whole db to screen:
dbbup /s ServerName /d DatabaseName /u MyUsername /p MyPassword
db to file:
dbbup /s ServerName /d DatabaseName /u MyUsername /p MyPassword > filename.txt
Specific Table:
dbbup /s ServerName /d DatabaseName /u MyUsername /p MyPassword /t MyTable
The code!
Code:
Option Explicit
'On Error Resume Next
Dim Server,Database,UserName,Password,Table
Dim oArgs, ArgNum
Dim verbose
dim cn,constr,sqlq,rscolumns,rstables
verbose = false
password = ""
table = ""
WScript.Echo "--SQL Server data recovery script."
WScript.Echo "--(c)2001 Simon Barnett."
WScript.Echo ""
Set oArgs = WScript.Arguments
ArgNum = 0
While ArgNum < oArgs.Count
Select Case LCase(oArgs(ArgNum))
Case "--Server","/s":
ArgNum = ArgNum + 1
Server = oArgs(ArgNum)
Case "--Database","/d":
ArgNum = ArgNum + 1
Database = oArgs(ArgNum)
Case "--UserName","/u":
ArgNum = ArgNum + 1
UserName = oArgs(ArgNum)
Case "--Password","/p":
ArgNum = ArgNum + 1
Password = oArgs(ArgNum)
Case "--Table","/t":
ArgNum = ArgNum + 1
Table = oArgs(ArgNum)
Case "--help","/?":
Call DisplayUsage
Case "--verbose", "/v":
verbose = true
Case Else:
WScript.Echo "Unknown argument "& oArgs(ArgNum)
Call DisplayUsage
End Select
ArgNum = ArgNum + 1
Wend
If (Server = "") Or (Database = "") Or (UserName = "") Then
if (Server = "") then
WScript.Echo "Missing Name of server"
end if
if (Database = "") then
WScript.Echo "Missing Name of Database"
end if
if (UserName = "") then
WScript.Echo "Missing Username for Database"
end if
wscript.echo ""
Call DisplayUsage
WScript.Quit(1)
End If
Set cn = CreateObject("adodb.connection")
ConStr = "PROVIDER=SQLOLEDB;DATA_SOURCE="& Server & _
";UID="& username &";PWD="& password &";DATABASE="& database
trace constr
cn.Open ConStr
select case table
case ""
sqlq = "select * from sysobjects where xtype ='u' and status > -1"
set rsTables = cn.execute(sqlq)
while not rsTables.eof
trace rsTables.fields("name")
tabledata(rsTables.fields("name"))
rsTables.movenext
wend
set rsTables = nothing
case else
tabledata(table)
end select
cn.close
'Functions------------------------------------------------------------>
sub TableData(p_table)
dim params,param,type_name,cast,paramcount
dim i,addline,rslines
paramcount = -1
wscript.echo "--------------------------------------------------->"
wscript.echo "--Table Name: " & p_Table
wscript.echo "--Backup Date: " & date()
wscript.echo "--------------------------------------------------->"
wscript.echo ""
sqlq = "EXEC sp_columns @table_name = " & p_table
set rsColumns = cn.execute(sqlq)
while not rsColumns.eof
params = params & rsColumns.fields("COLUMN_NAME") & ", "
type_name = type_name & rsColumns.fields("type_name") & ","
trace " " & rsColumns.fields("COLUMN_NAME")
rsColumns.movenext
paramcount = paramcount + 1
wend
params = left(params,len(params)-2)
param = split(replace(params," ",""),",")
type_name = left(type_name,len(type_name)-1)
cast = split(type_name,",")
sqlq = "select * from " & p_table
set rslines = cn.execute(sqlq)
while not rslines.eof
addline = "insert into "&_
p_table &_
" ("
for i = 0 to paramcount
if rslines.fields(param(i)) & "" <> "" then
addline = addline & param(i)
if i < paramcount then
addline = addline & ", "
end if
end if
next
if right(addline,1) = ", " then addline = left(addline,len(addline)-2)
addline = addline & ") Values ("
for i = 0 to paramcount
if rslines.fields(param(i)) & "" <> "" then
addline = addline & typecast(trim(rslines.fields(param(i))),cast(i))
if i < paramcount then
addline = addline & ", "
end if
end if
next
if right(addline,1) = ", " then addline = left(addline,len(addline)-2)
addline = addline & ");"
wscript.echo addline
rslines.movenext
wend
wscript.echo ""
wscript.echo "-- End of Table " & p_table
wscript.echo "--------------------------------------------->"
wscript.echo
wscript.echo ""
end sub
function typecast(p_param,p_cast)
select case p_cast
case "int identity","int","binary","decimal","float","int":
typecast = p_param
CASE "datetime"
typecast = day(p_param) & "/" & month(p_param) & "/" & year(p_param)
case "bit"
select case p_param
case true
typecast = 1
case else
typecast = 0
end select
case else
typecast = "'" & p_param & "'"
end select
if p_param & "" = "" then typecast = "NULL"
end function
' Display the usage message
Sub DisplayUsage
WScript.Echo "Usage: dbbup <--Server|/s Name of server>"
WScript.Echo " <--Database|/d Name of Database>"
WScript.Echo " <--UserName|/u Username for Database>"
WScript.Echo " [--Password|/p Password Database]"
WScript.Echo " [--Table|/t Name of table]"
WScript.Echo " [--Verbose|/v]"
WScript.Echo " [--Help|/?]>"
WScript.Echo " > filename.txt"
WScript.Echo ""
WScript.Echo "Examples:"
WScript.Echo "Whole db to screen:" & _
"dbbup /s ServerName /d DatabaseName /u MyUsername /p MyPassword"
WScript.Echo "db to file:" & _
"dbbup /s ServerName /d DatabaseName /u MyUsername /p MyPassword > filename.txt"
WScript.Echo "Specific Table:" & _
"dbbup /s ServerName /d DatabaseName /u MyUsername /p MyPassword /t MyTable"
WScript.Echo ""
WScript.Quit (1)
End Sub
Sub Display(Msg)
WScript.Echo Now & ". Error Code: " & Hex(Err) & " - " & Msg
End Sub
Sub Trace(Msg)
if verbose = true then
WScript.Echo Now & " : " & Msg
end if
End Sub
If you found this article useful see these other tutorials also by ntsa:
Search Engine submission 'exploit'
Forced shutdown of a remote nt/2k server
Securing an installation of IIS 4. (No, seriously)
Remote DSN Connections, using WinAPIs and the registry
Scripting Internet Connections Under Window$
\"I may not agree with what you say, but I will defend to the death your right to say it.\"
Sir Winston Churchill.
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
|