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 = '字段名' )