SQL Server 基础命令及报错汇总

数据库基本使用命令

-- 创建数据库，指定编码格式
CREATE DATABASE suggest_cm COLLATE Chinese_PRC_CI_AS;

use suggest_cm;
-- 创建数据库登录用户
CREATE LOGIN suggest_cm WITH PASSWORD = 'SuggestXXX';
CREATE USER suggest_cm FOR LOGIN suggest_cm;
-- 数据库所有权，【切换到对应数据库执行】，否则当前用户无法访问数据库
ALTER ROLE db_owner ADD MEMBER suggest_cm;
----------------------------------------------------------
--### 修改字符集报错
--1. 先改为单用户模式
ALTER DATABASE suggest_cm SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
--2. 然后关闭所有的查询窗口，修改Options的Collocation属性为 Chinese_PRC_CI_AS
ALTER DATABASE suggest_cm COLLATE Chinese_PRC_CI_AS;
--3. 再修改为多用户模式
ALTER DATABASE suggest_cm SET MULTI_USER;
--4. 验证数据库的默认编码
SELECT DATABASEPROPERTYEX('suggest_cm', 'Collation');
---------------------------------------------------------
-- 删除数据库
USE [master]--注意不能够USE [TestDB]，因为[TestDB]即将被删除，所以不能够将当前连接设置为连接到[TestDB]，否则下面的DROP DATABASE语句会报错
GO
ALTER DATABASE [suggest_cm] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;--首先将数据库改为单用户模式，WITH ROLLBACK IMMEDIATE提示切断所有其它连接到[TestDB]的数据库连接
GO
DROP DATABASE [suggest_cm];--删除[TestDB]及其数据库文件
GO
----------------------------------------------------------
-- 数据库备份 【sqlcmd命令行】
sqlcmd -S localhost -U suggest_cm
BACKUP DATABASE suggest_cm TO DISK='/var/opt/mssql/backup/suggest_cm_custom.bak' WITH INIT
GO
-- 数据库恢复
sqlcmd -S localhost -U sa
RESTORE DATABASE suggest_cm FROM DISK='/var/opt/mssql/backup/suggest_cm_custom.bak' WITH REPLACE
GO

-- 如果上述报错，方式二
RESTORE DATABASE suggest_cm FROM DISK = '/home/hadoop/backup/source/suggest_cm_backup_2024_03_05_000000_7628671.bak'
WITH MOVE 'suggest_cm' TO '/var/opt/mssql/data/suggest_cm.mdf', MOVE 'suggest_cm_log' TO '/var/opt/mssql/data/suggest_cm_log.ldf'
GO

常见问题解决

Linux中备份mssql数据库，出现权限错误的解决方法

在Linux中安装了mssql的数据库并存了一些数据，使用sqlcmd备份的时候

1	backup database xxxx TO DISK='/root/xxx/db.bak'

发现总是报错没有权限，
如

Msg 3201, Level 16, State 1, Server test-srv, Line 7
Cannot open backup device '/root/xxxx/db_20210304.bak'. Operating system error 5(Access is denied.).
Msg 3013, Level 16, State 1, Server test-srv, Line 7
BACKUP DATABASE is terminating abnormally.

可是我已经是root用户了，怎么会还报告没有权限了，后来想起来也许在sqlcmd中的执行用户并非root，查了下passwd文件，果然有一个mssql的用户，它的home目录是/var/opt/mssql，尝试修改备份命令

1	backup database xxxx TO DISK='/var/opt/mssql/db.bak'

成功

1
2
3

Processed 480 pages for database 'xxxx', file 'xxxx' on file 1.
Processed 2 pages for database 'xxxx', file 'xxxx' on file 1.
BACKUP DATABASE successfully processed 482 pages in 0.110 seconds (34.197 MB/sec).