-
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.
-
June 18th, 2002, 08:09 PM
#2
whoa! eeeassy there. although i don't know SQL or even own a copy of WSH, this is still a billiant idea and i'll keep it in my archives untill i can put it to use. Great post
-
June 18th, 2002, 08:57 PM
#3
-
June 18th, 2002, 09:00 PM
#4
Re: Dumping SQL data to a text file
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.
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.
Chris Shepherd
The Nelson-Shepherd cutoff: The point at which you realise someone is an idiot while trying to help them.
\"Well as far as the spelling, I speak fluently both your native languages. Do you even can try spell mine ?\" -- Failed Insult
Is your whole family retarded, or did they just catch it from you?
-
June 18th, 2002, 09:00 PM
#5
Just for you jehnx (and anyone else who wants to read this offline) I've zipped the article above and the .vbs WSH script up together for you to download from here.
\"I may not agree with what you say, but I will defend to the death your right to say it.\"
Sir Winston Churchill.
-
June 18th, 2002, 09:35 PM
#6
Hehe, thanks a lot.
-
June 19th, 2002, 07:10 AM
#7
NTSA if you ever write a book, ill buy it. most of the samples of WSH ive come accross are pretty common and for the most part dull. they usually just put me to sleep before i can get anything out of them....thanks
Bukhari:V3B48N826 “The Prophet said, ‘Isn’t the witness of a woman equal to half of that of a man?’ The women said, ‘Yes.’ He said, ‘This is because of the deficiency of a woman’s mind.’”
-
January 6th, 2005, 01:55 AM
#8
Junior Member
Can someone tell me 2 things
1. How can I get this to work on an access 2000 database?
2. How can I get it to export to a .csv file for backup to another server?
I have a small database that we use for server stat collection that will become sql in the next 2, 3 month's but for now I need to find away to export a specific data from one table to a csv so I can send to another server for integration to a report server.
Any who this is the closes match I have been able to find.
Thanks
Darn it Jim I'm an Microsoft Engineer not a Developer
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
|
|