1.进入cmd

2.进入sqlserver其中sa为用户名，root为密码

osql -S 127.0.0.1 -U sa -P root

3.使用master

use master;
go

4.新建库：XXX(注意：确保存在E:/SQLDATA路径)

create database XXX
on  primary
(
name='XXX_data',
filename='E:\SQLDATA\XXX_data.mdf',
size=5mb,
maxsize=100mb,
filegrowth=15%
)
log on
(
name='XXX_log',
filename='E:\SQLDATA\XXX_log.ldf',
size=2mb,
maxsize=20mb,
filegrowth=1mb
)
go

5.切换到刚刚创建的数据库

use XXX;
go

6.创建用户

6.1创建用户1(lz1)赋予db_owner：

create login lz1 with password='1234', default_database=XXX;
go
create user lz1 for login lz1 with default_schema=dbo;
go
exec sp_addrolemember 'db_owner', 'lz1';
go

6.2创建用户2(lz2)赋予db_owner：

create login lz2 with password='1234', default_database=XXX;
go
create user lz2 for login lz2 with default_schema=dbo;
go
exec sp_addrolemember 'db_owner', 'lz2';
go

6.3创建用户2(lz3)赋予db_datareader(只读)：

create login lz3 with password='1234', default_database=XXX;
go
create user lz3 for login lz3 with default_schema=dbo;
go
exec sp_addrolemember 'db_datareader', 'lz3';
go

7.备份:(注意：确保存在E:/backups路径)

BACKUP DATABASE XXX TO DISK ='E:/backups/XXX-OLD.bak' with init;
go

8.还原

8.1进入master

use master;
go

8.2完整还原(注意：将数据库备份文件放在E:/backups路径下)

RESTORE  DATABASE XXX FROM DISK='E:/backups/XXX-20220908.bak';
go

8.3进入XXX

use XXX;
go