Dumping SQL data to a text file

[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 .
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!

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$

[5150]

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

[jethro]

Woah! Cool post ntsa!

[chsh]

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.

[ntsa]

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.

[jehnx]

Hehe, thanks a lot.

[Tedob1]

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

[tward3017]

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