linux sql server 2017笔记

20190926添加：
Ubuntu 18.04.3 LTS安装mssql-server=14.0.3192.2-2
root@yiyouserver:~# apt-get install mssql-server=14.0.3192.2-2
root@yiyouserver:~# apt-cache policy mssql-server
mssql-server:
Installed: 14.0.3192.2-2
Candidate: 14.0.3223.3-15
root@yiyouserver:~# /opt/mssql-tools/bin/sqlcmd -S localhost -U SA -P YiYou2019
1> SELECT Name from sys.Databases
2> GO
Name
--------------------------------------------------------------------------------------------------------------------------------
master
tempdb
model
msdb

(4 rows affected)
1> drop database TestDB3
2> go
在linux下还原windows上备份的sql server数据库
RESTORE FILELISTONLY FROM DISK = '/var/opt/mssql/data/QPGameUserDB.bak'
RESTORE DATABASE QPGameUserDB FROM DISK = '/var/opt/mssql/data/QPGameUserDB.bak'
WITH MOVE 'QPGameUserDB' TO '/var/opt/mssql/data/QPGameUserDB.mdf',
MOVE 'QPGameUserDB_log' TO '/var/opt/mssql/data/QPGameUserDB_log.ldf'
GO
还原中：
Processed 2488 pages for database 'QPGameUserDB', file 'QPGameUserDB' on file 1.
Processed 1 pages for database 'QPGameUserDB', file 'QPGameUserDB_log' on file 1.
Converting database 'QPGameUserDB' from version 611 to the current version 869.
Database 'QPGameUserDB' running the upgrade step from version 611 to version 621.
......
Database 'QPGameUserDB' running the upgrade step from version 868 to version 869.
RESTORE DATABASE successfully processed 2489 pages in 0.141 seconds (137.910 MB/sec).

还原后：
1> SELECT Name from sys.Databases
2> go
Name
--------------------------------------------------------------------------------------------------------------------------------
master
tempdb
model
msdb
QPGameUserDB

(5 rows affected)

图形化客户端连接工具方面,Navicat for SQL Server要比SQL Server Management Studio好用。用低版本的SSMS（例如2008 R2的v10.50.1600.1）打开高版本的SQL Server 2017时经常会弹出“索引超出了数组界限。”的报错提示框。
SqlLocalDB.exe和SQLCMD.EXE命令行工具
cd %ProgramFiles%\Microsoft SQL Server\110\Tools\Binn
C:\Program Files\Microsoft SQL Server\110\Tools\Binn>SqlLocalDB.exe /?
Microsoft (R) SQL Server Express LocalDB 命令行工具
版本 11.0.2100.60
版权所有 (c) Microsoft Corporation。保留所有权利。

sqlcmd /?
Microsoft (R) SQL Server 命令行工具
版本 10.50.6000.34 NT INTEL X86【对应Sql Server 2008】
版本 9.00.1399.06 NT INTEL X86【对应Sql Server 2005】
版本 11.0.2100.60 NT x64【对应Sql Server 2012】
版本 12.0.2000.8 NT【对应Sql Server 2014】
版权所有 (c) Microsoft Corporation。保留所有权利。

用法: Sqlcmd [-U 登录 ID] [-P 密码]
[-S 服务器] [-H 主机名] [-E 可信连接]
[-N 加密连接][-C 信任服务器证书]
[-d 使用数据库名称] [-l 登录超时值] [-t 查询超时值]
[-h 标题] [-s 列分隔符] [-w 屏幕宽度]
[-a 数据包大小] [-e 回显输入] [-I 允许带引号的标识符]
[-c 命令结束] [-L[c] 列出服务器[清除输出]]
[-q "命令行查询"] [-Q "命令行查询" 并退出]
[-m 错误级别] [-V 严重级别] [-W 删除尾随空格]
[-u unicode 输出] [-r[0|1] 发送到 stderr 的消息]
[-i 输入文件] [-o 输出文件] [-z 新密码]
[-f <代码页> | i:<代码页>[,o:<代码页>]] [-Z 新建密码并退出]
[-k[1|2] 删除[替换]控制字符]
[-y 可变长度类型显示宽度]
[-Y 固定长度类型显示宽度]
[-p[1] 打印统计信息[冒号格式]]
[-R 使用客户端区域设置]
[-b 出错时中止批处理]
[-v 变量 = "值"...] [-A 专用管理连接]
[-X[1] 禁用命令、启动脚本、环境变量[并退出]]
[-x 禁用变量替换]
[-? 显示语法摘要]

sqlcmd -S "106.54.31.102" -U "sa" -P "yt@20190415"
1> :ServerList

服务器:
(local)
HEMEI-20180102W
HEMEI-20180102W\SQLEXPRESS

sqlcmd -S "CCNET-F5FB91A41" -U "hpkj888" -P "hpkj8888"
或
sqlcmd -S "61.164.110.131" -U "hpkj888" -P "hpkj8888"
1> :ServerList
服务器:
CCNET-F5FB91A41
LENOVO-6VTD1742
MEDIA
RAFDASERVER02
SERVER
WIN-4A1T5PFRNLO
WIN-51937I9DDE4
WIN-82VFPHGQ1AO
WIN-GFSAMM4VFFR

1> exit

建库、建表
create database qpgameuserdb1
go
USE qpgameuserdb1
create table accountsinfo(UserID int primary key,IsGuest tinyint)
ALTER TABLE qpgameuserdb1.dbo.accountsinfo ADD OpenID NVARCHAR(32),UnionID NVARCHAR(64),FaceUrl NVARCHAR(256)
go
select name,uid from sysobjects where xtype = 'U'
go

执行sql脚本：
sqlcmd -E -dmaster -ic:\memberdata.sql
sqlcmd -S "106.54.31.102" -U "sa" -P "yt@20190415" -ic:\memberdata.sql
sqlcmd -S "106.54.31.102" -U "sa" -P "yt@20190415" -dqpgameuserdb1 -ic:\memberdata.sql

sqlcmd -S "106.54.31.102" -U "sa" -P "yt@20190415" -dnote -ic:\GSP_InsertRecord.sql

sqlcmd -S "106.54.31.102" -U "sa" -P "yt@20190415" -dnote
1> DECLARE @RC int
2> DECLARE @Name nchar(20)
3> DECLARE @Age nchar(3)
4> -- TODO: 在此处设置参数值。
5> set @Name='han10'
6> set @Age=24
7> EXECUTE @RC = [note].[dbo].[GSP_InsertRecord]
8>    @Name
9>   ,@Age
10> print @rc
11> go
0
1> select * from DemoTable
2> go
Name                 Age
-------------------- ---
韩Ivan                27
han10                24

(2 行受影响)

linux【Ubuntu64bit】下命令行工具tsql【不好用】
root@yiyouserver:/home/hxh# /usr/local/freetds/bin/tsql -S 106.54.31.102 -U SA -P yt@20190415
locale is "en_US.UTF-8"
locale charset is "UTF-8"
using default charset "UTF-8"
1> SELECT Name from sys.Databases
2> go
Name
master
tempdb
model
msdb
ReportServer
ReportServerTempDB
qpgameuserdb
qpgameuserdb1
note
(9 rows affected)
1> use note
2> create table test (a varchar(255) COLLATE Chinese_PRC_CS_AS_WS NULL )
3> go
1> create Table get(Word nvarchar(31) COLLATE Chinese_PRC_CI_AS NOT NULL,Comment nvarchar(50) COLLATE Chinese_PRC_CI_AS NOT NULL);
2> go
1> Insert into get(Word,Comment)Values('poet','诗人')
2> go
1> exit
root@yiyouserver:/home/hxh#

linux【Ubuntu64bit】下命令行工具sqlcmd【较好用】
/opt/mssql-tools/bin/sqlcmd -S 106.54.31.102 -U SA -P yt@20190415
当 IDENTITY_INSERT 设置为 OFF 时，不能为表 'Singer' 中的标识列插入显式值。
1> select * from note..Singer
2> go
SingerID SingerName Birthday
----------- ------------------------------- -----------------------

(0 rows affected)
1> Insert into Singer(SingerName,Birthday)Values('张柏芝','1980-5-24 ')
2> go

(1 rows affected)
1> select * from note..Singer
2> go
SingerID SingerName Birthday
----------- ------------------------------- -----------------------
1 张柏芝 1980-05-24 00:00:00.000

(1 rows affected)

/opt/mssql-tools/bin/sqlcmd -S "106.54.31.102" -U "sa" -P "yt@20190415" -dnote -i/home/hxh/whry_hy_server/mssql/GSP_UpdateRecord.sql
/opt/mssql-tools/bin/sqlcmd -S "106.54.31.102" -U "sa" -P "yt@20190415"
1> DECLARE @RC int
2> DECLARE @OldName nchar(20)
3> DECLARE @Name nchar(20)
4> DECLARE @Age nchar(3)
5> set @OldName='han10'
6> set @Name='Ivan_han2'
7> set @Age=34
8> EXECUTE @RC = [note].[dbo].[GSP_UpdateRecord] @OldName,@Name,@Age
9> go

/opt/mssql-tools/bin/sqlcmd -S "106.54.31.102" -U "sa" -P "yt@20190415" -dnote -i/home/hxh/whry_hy_server/mssql/GSP_DeleteRecord.sql
/opt/mssql-tools/bin/sqlcmd -S "106.54.31.102" -U "sa" -P "yt@20190415" -dnote
1> DECLARE @RC int
2> DECLARE @Name nchar(20)
3> set @Name='Ivan_han2'
4> EXECUTE @RC = [note].[dbo].[GSP_DeleteRecord] @Name
5> print @RC
6> go
0
1> exit

root@yiyouserver:/home/hxh/whry_hy_server/mssql# /opt/mssql-tools/bin/sqlcmd -S "106.54.31.102" -U "sa" -P "yt@20190415"
1> BACKUP DATABASE note TO DISK='c:\bak\note.bak'
2> go
已为数据库 'note'，文件 'note' (位于文件 1 上)处理了 328 页。
已为数据库 'note'，文件 'note_log' (位于文件 1 上)处理了 6 页。
BACKUP DATABASE 成功处理了 334 页，花费 0.252 秒(10.350 MB/秒)。
1> exit

windows sql server 2014下的备份文件还原到linux sql server 2017下：
/opt/mssql-tools/bin/sqlcmd -S localhost -U SA -P YiYou2019
1> RESTORE DATABASE note FROM DISK='/home/hxh/whry_hy_server/mssql/note.bak'
2> with MOVE 'note' TO '/var/opt/mssql/data/note.mdf',MOVE 'note_log' TO '/var/opt/mssql/data/note_log.ldf'
3> go
Processed 328 pages for database 'note', file 'note' on file 1.
Processed 6 pages for database 'note', file 'note_log' on file 1.
Converting database 'note' from version 782 to the current version 869.
Database 'note' running the upgrade step from version 782 to version 801.
...
Database 'note' running the upgrade step from version 868 to version 869.
RESTORE DATABASE successfully processed 334 pages in 0.083 seconds (31.426 MB/sec).
1> SELECT Name from sys.Databases
2> go
Name
--------------------------------------------------------------------------------------------------------------------------------
master
tempdb
model
msdb
QPGameUserDB
note

(6 rows affected)
1>

附加、分离SQL Server数据库的命令：
sqlcmd -S "106.54.31.102" -U "sa" -P "yt@20190415" -dmaster
1> EXEC sp_attach_db @dbname = 'QPFangKaDB', @filename1 = 'C:\bakDB\QPFangKaDB.mdf',@filename2= 'C:\bakDB\QPFangKaDB.ldf'
2> go
正在将数据库 'QPFangKaDB' 从版本 706 转换为当前版本 782。
...
数据库 'QPFangKaDB' 正在从版本 781 升级到版本 782。
1> EXEC sp_detach_db @dbname = 'qpgameuserdb'
2> go

1> exec sp_helpdb
2> go