MSSQL: Export a single table in CSV format

Sometimes, development may request a database to be cloned in-house to look at certain data. Most of the times, the administrator will clone the database to an internal lab where only a single table of interest to be looked at. This is quite waste of the time to backup hundred of Gigs of the database, bandwidth is transferring them, a large amount of space taken to store the database backup, later transfer it to an internal lab and restore it to a SQL Server instance. Storage usage to look at a single table is taken 4 times the size of the database from backup, storage, transfer and restored.

The administrator can easily achieve this in no time by exporting a single copy of the table in CSV. Development may likey not even needing the SQL Server to look at the table since CSV can be opened with Excel spreadsheet.

Importing it back to a SQL Server works the same way. For a smaller table, an administrator can simply perform a query and copy the result out from the output.

sqlcmd that works the same too if the user already knows what s/he wants especially the separator.

sqlcmd -S . -d VCDB_2016 -E -s "^" -W -Q "select * from vpx_event" > E:\vpx_version.csv

MSSQL: Setup SQL Server with None Default Port

Not many sites talk about this and providing screenshots on how to configure and enable non default Port for SQL Server.

Step 1: Configure the SQL Server Network Configuration to enable TCP/IP and specify a non-default port.

Step 2: Enable The TCP/IP

Step 2b: In my example, I am pretending 1433 is a non-default port. If the user desire to change it to anything else, click on the IP Address tab and scroll ALL THE WAY DOWN and change the port of 1433 to something else.

Step 3: Once completed, the service needs to be restarted.

Step 4:  Start Menu - type "ODBC" . Fill in the information on the first page and click Next to the end, then click Finish. At the Finish page, click on Test Connection.

Step 4a: In order to connect SQL Server database with a non-default port. It can simply be done through simply appending the port number after the Server Name with a "," in between.

Test the connections. If the connection is successful, the user will be prompt with a successful page. Otherwise, it will be a Test Failed page.

Test Failed if steps are incorrect.

Other steps may be necessary depending on what you do

- Enable SQL Server Browser for automatic discovery.

- Enable firewall for non default port on the Windows Server

- Enable remote admin
https://docs.microsoft.com/en-us/sql/database-engine/configure-windows/remote-admin-connections-server-configuration-option?view=sql-server-2017

sp_configure 'remote admin connections', 1; GO RECONFIGURE; GO