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
Quote:
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$
Re: Dumping SQL data to a text file
Quote:
Originally posted here by ntsa
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 .
Actually, in the database itself, a Varchar(255) field will only contain one character if that's all you insert. Char(255) will fill the rest of the string with spaces. It will save space when you export it however, because even fields designated as variable character (varchar) will be flushed out.
Quote:
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.
There is an option to do this in MSSQL server as well. It's called Log shipping I believe, it's a form of replication common to all production database servers.
Very nice compact script though, from the looks of it. :)
