Dumping SQL data to a text file
Results 1 to 8 of 8

Thread: Dumping SQL data to a text file

  1. #1
    Senior Member
    Join Date
    Apr 2002
    Posts
    324

    Exclamation 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.

  2. #2
    Banned
    Join Date
    Nov 2001
    Posts
    188
    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

  3. #3
    Senior Member
    Join Date
    Nov 2001
    Location
    Ireland
    Posts
    735
    Woah! Cool post ntsa!

  4. #4
    Senior Member
    Join Date
    Nov 2001
    Posts
    1,255

    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?

  5. #5
    Senior Member
    Join Date
    Apr 2002
    Posts
    324
    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.

  6. #6
    Senior Member
    Join Date
    Dec 2001
    Posts
    884
    Hehe, thanks a lot.

  7. #7
    Senior Member
    Join Date
    Nov 2001
    Posts
    4,786
    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.’”

  8. #8
    Junior Member
    Join Date
    Jan 2005
    Posts
    1
    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
  •  

 Security News

     Patches

       Security Trends

         How-To

           Buying Guides