Searching a SQL Server Database on macOS or Linux

Sometimes you might need to search a Microsoft SQL database – even on macOS or Linux. To help you get started, this step-by-step guide explains how to use Docker to set up a SQL Server instance and restore a backup.

Variables Overview

Name	Value	Description
`MSSQL_SA_PASSWORD`	`ASDfer123!`	Password for the SQL Admin user (SA)
`Backup`	`asd.bak`	Name of the backup file
`DB`	`XY`	Name of the database to be restored

Password Requirements

Ensure that your SA password meets the following requirements to avoid errors:

At least 8 characters long
Includes characters from at least 3 of the 4 character groups:
- Uppercase letters
- Lowercase letters
- Numbers
- Special characters

Setting Up the Environment

Start the SQL Server container:

docker pull mcr.microsoft.com/mssql/server:2022-CU13-ubuntu-22.04
docker run -e 'ACCEPT_EULA=Y' -e 'MSSQL_SA_PASSWORD=ASDfer123!' --name 'mssql' -p 1401:1433 -v mssqldata:/var/opt/mssql -d mcr.microsoft.com/mssql/server:2022-CU13-ubuntu-22.04

Copy the backup file into the container:

docker exec -it mssql mkdir /var/opt/mssql/backup
docker cp asd.bak mssql:/var/opt/mssql/backup

Restoring the Backup

Inspect the backup file:

docker exec -it mssql /opt/mssql-tools/bin/sqlcmd -S localhost -U SA -P 'ASDfer123!' -Q 'RESTORE FILELISTONLY FROM DISK = "/var/opt/mssql/backup/asd.bak"' | tr -s ' ' | cut -d ' ' -f 1-2

Example output:

LogicalName PhysicalName
------------------------
XY         C:\directory\subdirectory\XYZ
XY_log     C:\directory\subdirectory\XYZ

Fix file permissions (if needed):

docker exec -u 0 mssql chown mssql:mssql /var/opt/mssql/backup/asd.bak

Restore the database:

docker exec -it mssql /opt/mssql-tools/bin/sqlcmd -S localhost -U SA -P 'ASDfer123!' \
   -Q 'RESTORE DATABASE XY FROM DISK = "/var/opt/mssql/backup/asd.bak" WITH MOVE "XY" TO "/var/opt/mssql/data/XY.mdf", MOVE "XY_log" TO "/var/opt/mssql/data/XY_log.ndf"'

Accessing the Database

You can now access the restored database using a tool like DBeaver.

Variable	Value
URL	`jdbc:sqlserver://;serverName=localhost;port=1401;databaseName=master`
Host	`localhost`
Database	`master`
Username	`sa`
Password	`ASDfer123!`

Searching the Database

If you want to search the database for a specific string, you can use the following stored procedure. It scans all tables for the provided value:

CREATE PROC [dbo].[SearchAllTablesByRecord]
(
    @SearchStr NVARCHAR(100)
)
AS
BEGIN
    SET NOCOUNT ON

    DECLARE @TableName NVARCHAR(256), @SearchStr2 NVARCHAR(110)
    DECLARE @ColumnList NVARCHAR(2048), @ColumnJoin NVARCHAR(2048)
    SET @TableName = ''
    SET @SearchStr2 = QUOTENAME('%' + @SearchStr + '%','''')

    WHILE @TableName IS NOT NULL
    BEGIN
        SET @TableName =
        (
            SELECT MIN(QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME))
            FROM INFORMATION_SCHEMA.TABLES
            WHERE TABLE_TYPE = 'BASE TABLE'
              AND QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME) > @TableName
              AND OBJECTPROPERTY(OBJECT_ID(QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME)), 'IsMSShipped') = 0
        )

        PRINT @TableName -- Progress indicator

        SET @ColumnList =
        (
            SELECT STRING_AGG(QUOTENAME(COLUMN_NAME),',')
            FROM INFORMATION_SCHEMA.COLUMNS
            WHERE TABLE_SCHEMA = PARSENAME(@TableName, 2)
              AND TABLE_NAME = PARSENAME(@TableName, 1)
              AND DATA_TYPE IN ('char', 'varchar', 'nchar', 'nvarchar')
        )
        SET @ColumnJoin = REPLACE(@ColumnList, ',', '+')

        IF @ColumnList <> ''
            EXEC
            (
                'SELECT ''' + @TableName + ''' AS [Table],' + @ColumnList + '
                 FROM ' + @TableName + ' (NOLOCK) WHERE ' + @ColumnJoin + ' LIKE ' + @SearchStr2
            )
    END
END

Usage of the procedure:

EXEC [dbo].[SearchAllTablesByRecord] 'Hackerman strikes again';