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$