Dumping SQL Server database tables to CSV files

In case anyone finds this helpful. Exported/dumped CSV files are not "true" CSV files and need to be manually cleaned up to be "true" CSV files, but they're good enough to start. Useful for doing database diffs, or just some form of data backup.

Sample is designed to run locally on SQL Server host using integrated authentication and makes use of sqlcmd.exe tool. However, it can be modified to run remotely, use Windows/SQL Server account authentication, and could use another method or tool besides sqlcmd like osql.exe, etc.

 If WScript.Arguments.Count < 1 Then
     ShowUsage()
     WScript.Quit(0)
 End If
 
 Dim conn, dbName
 dbName = WScript.Arguments.Named.Item("database")
 
 Set conn = CreateObject("ADODB.Connection")
 conn.Open "Provider=sqloledb;" & _
            "Data Source=(local);" & _
            "Initial Catalog=" & dbName & ";" & _
            "Integrated Security=SSPI"
 
 Dim tbRs, tableQuery
 On Error Resume Next
 tableQuery = "SELECT TABLE_NAME " & _
              "FROM INFORMATION_SCHEMA.TABLES " & _
              "WHERE TABLE_TYPE = 'BASE TABLE' " & _
              "ORDER BY TABLE_NAME;"
 'WScript.Echo query
 Set tbRs = conn.Execute(tableQuery)
 Dim WshShell, tbCount
 set WshShell = CreateObject("wscript.Shell")
 tbCount = 0
 While Not tbRs.EOF
     'WScript.Echo tbRs("TABLE_NAME")
     WshShell.Run("sqlcmd -E -d "&dbName&" -Q ""SELECT * FROM "&tbRs("TABLE_NAME")&";"" -o "&tbRs("TABLE_NAME")&".csv -s "","" -W")
     tbCount = tbCount + 1
     tbRs.MoveNext
 Wend
 WScript.Echo "Total tables = " & tbCount
 Set tbRs = Nothing
 conn.Close
 Set conn = Nothing
 
 Sub ShowUsage
     WScript.Echo "Usage:"
     WScript.Echo ""
     WScript.Echo "    cscript DumpDbTables.vbs /database:DatabaseName"
     WScript.Echo ""
     WScript.Echo "    Dumps all tables to individual CSV files from selected database"
     WScript.Echo ""
 End Sub
 

Thanks for sharing. Is there a reason for the On Error Resume Next? More importantly, is there a reason for no matching On Error Goto 0?

I probably left that in there during debugging or what not. It should probably be removed, or one should add the error checking code in, as needed.