开发者学堂课程【SQL Server on Linux入门教程： SQLServer On Linux备份还原】学习笔记，与课程紧密联系，让用户快速学习知识
课程地址：https://developer.aliyun.com/learning/course/386/detail/4845
SQLServer On Linux 备份还原
MSSQL on Linux Backup & Restore
1、PARTI: BACKUP
（1）Create Table1 FULL BACKUP 首先创建 Table1 FULL BACKUP
（2）Create Table2 DIFFERENTIAL BACKUP 创建 Table2 differential 的 BACKUP（3）Create Table3 LOG BACKUP 创建 Table3 的 LOG BACKUP
在还原过程中最开始做一个 FULL BACKUP 的还原，接着做一
differential BACKUP 的还原，最后做一个 LOG BACKUP 还原，最终的效果是这三张表在最后都要呈现在 restore 上传的数据库里面。
（4）首先 terminal 已经连接到虚拟机，用 ssms 连过去，首先创建一个数据库，再创建 Table1，做 full backup，创建 Table2，做 DIFFERENTIAL backup，再创建Table3，做 log backup，这就是整个三种类型的 backup，执行。
具体代码如下：
USE masterGO--If not exists, create testing database
IF DB_ ID('TestDB' ) IS NULLCREATE DATABASE TestDB;GO--create the lst tableUSE TestDBGOIF OBJECT_ ID(' dbo. Table1', 'U') IS N0T NULLDROP TABLE dbo. Table1GOCREATE TABLE dbo. Table1 (RowID INTGO--make a full backupBACKUP DATABASE [TestDB]TO DISK = N' C: \var\opt\mssql\data\TestDB_ full. bak' WITH NOFORMAT, NOINIT,NAME = N' TestDB- -Full Database Backup' , SKIP,NOREWIND, NOUNLOAD, STATS = 10GO--create the second tableIF OBJECT_ID(' dbo. Table2', 'U') IS N0T NULLDROP TABLE dbo. Table2 GO
CREATE TABLE dbo. Table2 (
RowID INT
GO--take a diff backupBACKUP DATABASE [TestDB]TO DISK = N'C: \var\opt\mssql\data\TestDB_diff.bak'
WITH DIFFERENTIAL , N0FORMAT, NOINIT,NAME = N'Tes,DB-Differential Database Backup' , SKIP,NOREWIND, NOUNLOAD,STATS=10GO--create the third tableIF OBJECT ID('dbo.Table3','U') IS NOT NULL
DROP TABLE dbo. Table3GOCREATE TABLE dbo, Table3 (RowID INTGO-- take transaction log backupBACKUP L0G [TestDB]TO DISK = N' C:varlopt mssqldataTestDB_ log. bak' WITH NOFORMAT, NOINIT,NAME = N' TestDB- Transaction Log Backup' , SKIP,
NORFWTND NOINIOAD STATS=10
GO
再输入[root@localhost ~]# ls /var/opt/mssql/data/ I grep bak
检查文件。
TestDB_ diff.bak
TestDB_ fùll, bak
TestDB_ log. Bak
2、PARTII: RESTORE
RESTORE FULL BACKUP RESTORE DIFF BACKUP RESTORE LOGBACKUP
进行还原的步骤，如果这个数据库已经存在，先把它删掉，做一个 restore 动作，做 full backup 的 restore，再对 differential backup 做 restore 动作，最后做log backup 的 restore，最后看到的结果跟现在是一样的，有三个表。先删掉数据库，再去还原，具体代码如下：
USE master
GO
--drop database if exists for testing
IF DB_ ID('TestDB' ) IS NOT NULL
DROP DATABASE TestDB ;
GO
- - restore full database backup
RESTORE DATABASE [TestDB]
FROM DISK = ' C:varlopt mssq1dataTestDB_ full. bak' WITH FILE = 1,
MOVE N' TestDB' TO N' c: varlopt mssq1 datalTestDB. mdf'
MOVE N' TestDB_ 1og TO N' C: varlopt 'mssql dataTestDB_ log. 1df'
NORECOVERY,
NOUNLOAD ,
REPLACE,
STATS = 10
GO
--restore the diff backup
RESTORE DATABASE [TestDB]
FROM DISK = N' C: varlopt mssqldataTestDB_ diff. bak' WITH FILE = 1
NORECOVERY,
NOUNLOAD,
STATS = 10
GO
- - restore the transaction log backup
RESTORE L0G [TestDB]
FROM DISK = N' C:var opt mssql dataTestDB_ log. bak' WITH FILE = 1
NOUNLOAD, STATS = 10
GO
