matheusr/mssql Docker 镜像 - 轩辕镜像 | Docker 镜像高效稳定拉取服务

How to use this Image

docker run -e "SA_PASSWORD=your_password" -e "WORKDIR=/vol" -e "JSONFILE=attach_db.json" -e "SQLSCRIPT=attach_db.sql" -p 1433:1433 -v "$(pwd):/vol" -d matheusr/mssql:latest

Arguments

SA_PASSWORD = Defines the password for user as
WORKDIR = Set the working folder, must be the same folder as mounted.
JSONFILE = specify the name of the json file which contains the database names and DB file paths to attach.
SQLSCRIPT = specify the name of the SQL server script

Constraint: The VOLUME (-v) mounted shall be the same path as arg WORKDIR.

.dockerfile:

FROM mcr.microsoft.com/mssql/server:2019-latest
USER root
LABEL Matheus Roedel
WORKDIR /
COPY ["entrypoint.sh", "restore_db.sh", "/usr/local/bin/"]
VOLUME /vol
ENV ACCEPT_EULA=Y
ENV SA_PASSWORD=Service01
ENV WORKDIR=/vol
ENV JSONFILE=attach_db.json
ENV SQLSCRIPT=attach_db.sql

#Grant permissions for scripts to be executable
RUN chmod +x /usr/local/bin/entrypoint.sh
RUN chmod +x /usr/local/bin/restore_db.sh

ENTRYPOINT /usr/local/bin/entrypoint.sh
EXPOSE 1433

Files content sample:

JSONFILE:

[ {"id": "RXIMS_60", "files": { "mdf": "dbfiles/RXIMS_60.mdf", "ldf": "dbfiles/RXIMS_60_1.LDF" } }, {"id": "CXIMS_60", "files": { "mdf": "dbfiles/CXIMS_60.mdf", "ldf": "dbfiles/CXIMS_60_1.LDF" } } ]

SQLSCRIPT:

DECLARE @JSON varchar(max)

SELECT @JSON = BulkColumn FROM OPENROWSET (BULK N'$(workdir)/$(jsonfile)', SINGLE_CLOB) as j

IF (ISJSON(@JSON)=1) BEGIN PRINT ' ---------------->>>>>> Valid JSON <<<<<<--------------------------' DECLARE @SQL VARCHAR(MAX); DECLARE @DATABASE NVARCHAR(30); DECLARE @MDF NVARCHAR(50); DECLARE @LDF NVARCHAR(50); DECLARE @ROWCOUNT INT = (SELECT count(*) FROM OPENJSON(@json))

DECLARE @i INT = 0
WHILE (@i < @ROWCOUNT) 
BEGIN 
	SET @DATABASE = JSON_VALUE(@json,CONCAT('$[',@i,'].id'));
	SET @MDF = JSON_VALUE(@json,CONCAT('$[',@i,'].files.mdf'));
	SET @LDF = JSON_VALUE(@json,CONCAT('$[',@i,'].files.ldf'));

	SET @sql = 'IF EXISTS (SELECT 1 FROM SYS.DATABASES WHERE NAME = ''' + @DATABASE  + ''') BEGIN EXEC sp_detach_db [' + @DATABASE + '] END; '
	set @sql = @sql + 'CREATE DATABASE [' + @DATABASE + '] ON	(FILENAME = N''$(workdir)/' + @MDF + '''), (FILENAME = N''$(workdir)/' + @LDF + ''') FOR ATTACH;'

	EXEC sp_sqlexec @Sql
	PRINT 'COMMAND EXECUTED: ' + @SQL

	SET @i = @i + 1;
END

END ELSE BEGIN PRINT 'JSON File is invalid'; END

Requirements

This image requires Docker Engine 1.8+ in any of their supported platforms. At least 2GB of RAM (3.25 GB prior to 2017-CU2). Make sure to assign enough memory to the Docker VM if you're running on Docker for Mac or Windows.