连接数据库实例

本地连接基本命令

sqlcmd -S localhost -U sa -P '<YourPassword>'

其中-P可以不加，会提示手动输入密码。

如出现如下报错：

Sqlcmd: Error: Microsoft ODBC Driver 18 for SQL Server : SSL Provider: [error:1416F086:SSL routines:tls_process_server_certificate:certificate verify failed:self signed certificate].
Sqlcmd: Error: Microsoft ODBC Driver 18 for SQL Server : Client unable to establish connection. For solutions related to encryption errors, see https://go.microsoft.com/fwlink/?linkid=2226722.

可以加参数-C跳过验证，等同于ADO.NET 选项TRUSTSERVERCERTIFICATE = true

常用：

cd /opt/mssql-tools18/bin
./sqlcmd -S localhost -U sa -C

若要使 sqlcmd 和 bcp 能从交互式/非登录会话的 bash shell 进行访问，请使用下列命令修改 ~/.bashrc 文件中的 PATH：

echo 'export PATH="$PATH:/opt/mssql-tools18/bin"' >> ~/.bashrc
source ~/.bashrc

常用操作

新建库

CREATE DATABASE TestDB;
go

显示库

1> select name,database_id from sys.databases; 
2> go 

删除库

1> drop database testDB; 
2> go 

更改数据库排序规则

ALTER   DATABASE   db01  COLLATE   Chinese_PRC_CI_AS;

退出命令行

QUIT

参考：

https://linuxcpp.0voice.com/?id=128858

查看版本：

sqlcmd -S localhost -E
SELECT @@VERSION;
GO

配合crontab执行定时任务

在express版本中无法使用SQLServer Agent进行定时作业，因此需要用到crontab。
首先在业务库创建全备存储过程（简化版）：

USE [creta2]
GO

/****** Object:  StoredProcedure [dbo].[DB_Backup_Full]    Script Date: 2024/10/14 14:30:18 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


------------------------------------------------
CREATE PROCEDURE [dbo].[DB_Backup_Full]
AS

/*******************************************************************************************
<Create By> : wenjie.wang
<Create Date> : 2021_03_16
********************************************************************************************/

DECLARE @DBName VARCHAR (200)
SELECT @DBName=DB_NAME() --FROM master.dbo.sysprocesses where status = 'runnable'
--select @DBName
--Perform Full BackUp
DECLARE @FullFileName VARCHAR(200)
DECLARE @FileFlag VARCHAR(20)
--Delete local old backup file
DECLARE @SQLStr VARCHAR(300)

Set @FileFlag='Full_20'+convert(char(6),getdate(),12)
Set @FullFileName='/data/backup/'+@DBName+@FileFlag+'.BAK'
BackUp DataBase @DBName To Disk=@FullFileName with init

GO

然后在ubuntu系统创建调用存储过程的sql文件DB_Backup_Full.sql，内容如下：

root@admin123:/data/backup# cat /data/backup/DB_Backup_Full.sql
exec DB_Backup_Full;

测试sql是否备份正常：

root@admin123:/data/backup# /opt/mssql-tools18/bin/sqlcmd -S localhost -U sa -C -P 'xxxxxx' -d creta2  -i /data/backup/DB_Backup_Full.sql
Processed 147088 pages for database 'creta2', file 'PRIMARY' on file 1.
Processed 1576 pages for database 'creta2', file 'CRETA2_IDX01' on file 1.
Processed 72 pages for database 'creta2', file 'CRETA2_LOB01' on file 1.
Processed 1 pages for database 'creta2', file 'CRETA2_LOG' on file 1.
BACKUP DATABASE successfully processed 148737 pages in 10.881 seconds (106.791 MB/sec).

创建调用脚本/data/backup/sqlserver_backup.sh（提前存好密码）：

dbuser=sa
dbpwd=`cat /root/.full_backup1.pwd`
savedays=15

#delete certain days ago datas
backup_dir=/data/backup
find ${backup_dir} -name "*.bak" -mtime +${savedays} -exec rm -rf {} \;

#backups
/opt/mssql-tools18/bin/sqlcmd -S localhost -U ${dbuser} -C -P ${dbpwd}  -d creta2 -i /data/backup/DB_Backup_Full.sql

crontab进行配置，新增一行：

30 0  * * * /bin/bash /data/backup/sqlserver_backup.sh > /dev/null 2>&1