Dump SQL tables to text files for comparison

Kun slutbruger-hjælpen er for tiden oversat til dansk. Hjælp til installation og administration af Zylinc-løsninger er for tiden på engelsk.

Dette emne er fortrinsvis for administratorer og/eller folk som bestyrer en Zylinc-løsning

Dumping SQL database tables to text files can sometimes be useful, for example if:

  • You want to look inside tables and compare rows for a user that gets an error with another user that doesn't get the error
  • You want to compare two dumps to detect changes made between two points in time

You can use SQL queries or SQL table text file dumps when:

  • You know where to find the database, for example, the SQL server hostname and optionally a port number or instance name

  • You have a user name and password that allows you to make SQL queries in the database

  • You have an installation of SQL Server Management Studio, for example on a Zylinc Windows Application Server or on your laptop

SQL queries:

  • Require that you know which tables that contain the data you need to look at, or which stored procedure to call to return the data

  • require that you know the relations between tables, for example primary keys, foreign keys and join tables

SQL table text file dumps:

  • Let you save a text file that contains all rows and columns of all tables within a database. This method is ...

    • Simple and easy to use, compared with SQL profiler or SQL queries

    • Efficient for small databases

    • Efficient when you are the only user on a database

    • Efficient in a personal environment, a test environment, or in a new environment that you're installing

  • To create SQL table text file dumps, you’ll need a third-party tool or script, for example the PowerShell script available in the following

  • You can create more than one file at different points in time, and use a text file comparison tool to track changes between the files over time

  • Use the search function in a text editor to search for values across all columns and rows in all tables, without detailed knowledge about table names and table relations

  • Useful when you need to learn (or reverse engineer) a relation database. For example, you can do the following:

    1. Create a dump file (file 1).
    2. Change something in the system, for example, create a new user, or make changes to an existing user.
    3. Create another dump file (file 2).
    4. Use a file comparison tool to compare file 1 and file 2. You can now track all changes that has happened in all tables between the two points in time.

  • Useful when you need to solve support issues. For example, you can easily identify which tables contain certain information. With that information available, you can then look inside those tables and compare rows for, for example, a user that gets an error with another user that doesn't get the error.

Make SQL table text file dumps with PowerShell and SQL Server Management Studio

Copy the PowerShell code into a new script pane in Windows PowerShell ISE:

  1. On the computer with SQL Server Management Studio in the Windows Start menu, select Windows PowerShell, right-click Windows PowerShell ISE, and select More > Run as administrator

  2. In the Windows PowerShell ISE menu, select View, and make sure that Show Script Pane is selected.

  3. Make a copy of the following PowerShell code, and in the Windows PowerShell ISE window, right-click inside the Untitled1.ps1 script pane, and select Paste.

Before you run the script, make the following changes to the PowerShell code:

  1. On line 1:

    $SQLServerInstance = 'winappserver.domain.local,1433'

    Change winappserver.domain.local,1433 to the hostname and port number of your SQL Server.

  2. On line 2:

    $SQLDataBase = 'ZyDB'

    Change ZyDB to the name of the database that you want to dump into a text file.

  3. On line 3:

    $SQLUser = 'ZyUser'

    Change ZyUser to the user name that you want to use to connect to the database.

  4. On line 4:

    $SQLPassword = 'password'

    Change password to the password for the user from the previous step

Run the PowerShell script:

  • In the Windows PowerShell ISE window, press F5 to Run Script

A folder will open in Windows explorer. That folder will contain a text file that is a SQL table text file dump of your database.

You can now change something in your database, and the run the script again. You can then use a text file comparison tool, for example Winmerge, to compare the two files.