docker-compose安装SQL server数据库及相关运维脚本

docker-compose安装SQL server数据库及相关运维脚本

创建docker-compose.yml文件

version: '3'

services:
  sqlserver:
    image: mcr.microsoft.com/mssql/server:2019-latest
    environment:
      - ACCEPT_EULA=Y
      - SA_PASSWORD=password
      - TZ=Asia/Shanghai
    ports:
      - "1433:1433"
    volumes:
      - ./data/sqlserver/tmp:/data/tmp
      - ./data/sqlserver/data:/var/opt/mssql/data
      - ./data/sqlserver/log:/var/opt/mssql/log
      - ./data/sqlserver/secrets:/var/opt/mssql/secrets
      - ./data/sqlserver/backups:/var/opt/mssql/backups
    privileged: true
    restart: always
    networks:
      my_net:
        ipv4_address: 172.30.0.100
    deploy:
      resources:
        limits:
          memory: 2G
        reservations:
          memory: 2G

networks:
  my_net:
    driver: bridge
    ipam:
      config:
        - subnet: 172.30.0.0/16

启动sqlserver

mkdir -p ./data/sqlserver/tmp
mkdir -p ./data/sqlserver/data
mkdir -p ./data/sqlserver/log
mkdir -p ./data/sqlserver/secrets
mkdir -p ./data/sqlserver/backups
chmod -R 777 ./data/sqlserver/

docker-compose up -d

使用sqlcmd从sql脚本恢复

docker exec -it sqlserver bash
cd /opt/mssql-tools/bin
sqlcmd -S 127.0.0.1 -U username -P password -i path/database_name.sql [-d database_name]

使用sqlcmd从备份恢复

登录数据库

docker exec -it sqlserver bash
cd /opt/mssql-tools/bin
sqlcmd -S 127.0.0.1 -U username -P password   [-d database_name]

查看备份文件逻辑文件名LogicalName和物理文件名PhysicalName

如数据库备份文件：path/database_name_backup.bak

RESTORE FILELISTONLY FROM DISK = '数据库备份文件';
GO

开始备份

根据获取的逻辑文件名LogicalName和文件后缀构造RESTORE DATABASE语句，MOVE语句可能有多个，后缀名可能有mdf、ldf、ndf

RESTORE DATABASE 新数据库名称 FROM DISK = '数据库备份文件' WITH MOVE '逻辑文件名' TO '目录/新数据库名称.mdf', MOVE '逻辑文件名_log' TO '目录/新数据库名称_log.ldf', MOVE '其他数据和索引逻辑文件名(可能没有)' TO '目录/新数据库名称_0(从0开始).NDF';
GO

查询验证

SELECT name, state_desc FROM sys.databases WHERE name = '新数据库名称';
GO

使用sqlcmd查询

sqlcmd -S server_name -d database_name -U username -P password -Q "SELECT * FROM table_name"

使用sqlcmd导出

sqlcmd -S server_name -d database_name -U username -P password -Q "SELECT * FROM table_name" -o output_file_path

使用sqlcmd备份数据库

sqlcmd -S 服务器名 -U 用户名 -P 密码 -Q "BACKUP DATABASE 数据库名 TO DISK = '备份文件路径\备份文件名.bak' WITH FORMAT, INIT, NAME = '数据库备份名', SKIP, NOREWIND, NOUNLOAD, STATS = 10"

使用bcp导出

bcp "SELECT * FROM database_name.schema_name.table_name" queryout output_file_path -c -t, -S server_name -d database_name -U username -P password

使用bcp导入

bcp database_name.schema_name.table_name in input_file_path -c -t, -S server_name -d database_name -U username -P password

bcp命令

bcp 主要用于导出表数据到文件，以及从文件导入数据到表

• -c 表示使用字符类型数据。
• -t, 表示字段之间的分隔符是逗号。
• -S 是服务器名称。
• -d 是数据库名称。
• -U 和 -P 分别是用户名和密码。

手动创建数据库

USE master;
go
DECLARE @database_name NVARCHAR(50);
DECLARE @data_file NVARCHAR(50);
DECLARE @sql NVARCHAR(MAX);

--数据库名
SET @database_name = '数据库名';
--数据文件存放路径，该路径必须存在。
--路径中不允许使用空格等字符，建议使用全英文，
--如  D:\SQLSERVER\DATA
SET @data_file = '数据文件存放路径';

SET @sql = 
'CREATE DATABASE' + QUOTENAME(@database_name) + ';' +
'ALTER DATABASE ' + @database_name + ' ADD FILEGROUP [NNC_DATA01];' + 
'ALTER DATABASE ' + @database_name + ' ADD FILE(NAME = nnc_data01 , FILENAME = ' + '''' + @data_file + '\'  + @database_name + '_nnc_data01_Data.NDF '+''''+', SIZE = 500, FILEGROWTH = 200) TO FILEGROUP [NNC_DATA01]' +

'ALTER DATABASE ' + @database_name + ' ADD FILEGROUP [NNC_DATA02];' + 
'ALTER DATABASE ' + @database_name + ' ADD FILE(NAME = nnc_data02 , FILENAME = ' + '''' + @data_file + '\'  + @database_name + '_nnc_data02_Data.NDF '+''''+', SIZE = 500, FILEGROWTH = 200) TO FILEGROUP [NNC_DATA02]' +

'ALTER DATABASE ' + @database_name + ' ADD FILEGROUP [NNC_DATA03];' + 
'ALTER DATABASE ' + @database_name + ' ADD FILE(NAME = nnc_data03 , FILENAME = ' + '''' + @data_file + '\'  + @database_name + '_nnc_data03_Data.NDF '+''''+', SIZE = 500, FILEGROWTH = 200) TO FILEGROUP [NNC_DATA03]' +

'ALTER DATABASE ' + @database_name + ' ADD FILEGROUP [NNC_INDEX01];' + 
'ALTER DATABASE ' + @database_name + ' ADD FILE(NAME = nnc_index01 , FILENAME = ' + '''' + @data_file + '\'  + @database_name + '_nnc_index01_Data.NDF '+''''+', SIZE = 500, FILEGROWTH = 200) TO FILEGROUP [NNC_INDEX01]' +

'ALTER DATABASE ' + @database_name + ' ADD FILEGROUP [NNC_INDEX02];' + 
'ALTER DATABASE ' + @database_name + ' ADD FILE(NAME = nnc_index02 , FILENAME = ' + '''' + @data_file + '\'  + @database_name + '_nnc_index02_Data.NDF '+''''+', SIZE = 500, FILEGROWTH = 200) TO FILEGROUP [NNC_INDEX02]' +

'ALTER DATABASE ' + @database_name + ' ADD FILEGROUP [NNC_INDEX03];' + 
'ALTER DATABASE ' + @database_name + ' ADD FILE(NAME = nnc_index03 , FILENAME = ' + '''' + @data_file + '\'  + @database_name + '_nnc_index03_Data.NDF '+''''+', SIZE = 500, FILEGROWTH = 200) TO FILEGROUP [NNC_INDEX03]' 
EXEC sp_executesql @sql;
go
--切换数据库
use 数据库名;
go
--启用快照隔离
declare @exec_stmt nvarchar(4000) select @exec_stmt='ALTER DATABASE '+db_name()+' set read_committed_snapshot ON ' exec (@exec_stmt)
go
--禁用并行
sp_configure 'show advanced options',1 
go 
reconfigure with override 
go 
sp_configure 'max degree of parallelism',1 
go 
reconfigure with override 
go

查询数据库表

select* from sysobjects where xtype='U'

C = CHECK 约束
D = 默认值或 DEFAULT 约束
F = FOREIGN KEY 约束
L = 日志
FN = 标量函数
IF = 内嵌表函数
P = 存储过程
PK = PRIMARY KEY 约束（类型是 K）
RF = 复制筛选存储过程
S = 系统表
TF = 表函数
TR = 触发器
U = 用户表
UQ = UNIQUE 约束（类型是 K）
V = 视图
X = 扩展存储过程

根据列名查询数据库表

SELECT name FROM sysobjects WHERE id IN (SELECT id FROM syscolumns WHERE name = '字段名' )