@[toc]
本文將介紹如何在日常專案中，對SQL server資料庫做備份和還原工作，SQL server的備份/還原機制，詳情參見官方文件：[備份和還原 Linux 上的 SQL Server 資料庫](https://learn.microsoft.com/zh-cn/SQL/linux/sql-server-linux-backup-and-restore-database?view=sql-server-2017)

# 方式一，使用SQL Server Management Studio
## 準備工作

連線目標資料庫伺服器

在目標資料庫上右鍵->屬性，將資料庫的恢復模式設定為`簡單`，相容級別設定為`SQL Server 2016(130)`
![在這裡插入圖片描述](https://img-blog.csdnimg.cn/2fdbed564d2a492884226da86b424f26.png)

[可選]將表中將無用的業務資料刪除，以減少備份或移動的檔案大小，這一步根據業務需求而決定，以ABP系統為例，執行下列SQL命令將`AbpAuditLogs`表中的資料清除：
```
TRUNCATE TABLE [dbo].[AbpAuditLogs]
```

## 收縮資料庫
![在這裡插入圖片描述](https://img-blog.csdnimg.cn/2054f0ca7f914ad08b6b7ed3e6e81bab.png)
這一步將減少資料庫中資料檔案和日誌檔案的大小，並允許資料庫中有 10% 的可用空間。詳情參見官方文件：[收縮資料庫](https://learn.microsoft.com/zh-cn/sql/relational-databases/databases/shrink-a-database?view=sql-server-ver16)
![在這裡插入圖片描述](https://img-blog.csdnimg.cn/0e216ef7e5f74c66874bbabf88191883.png)

## 移動資料庫
透過將源資料庫伺服器中的資料庫檔案的分離，複製檔案，再將檔案附加至目標資料庫伺服器中，實現資料庫移動，詳情參見官方文件：[資料庫分離和附加 (SQL Server)](https://learn.microsoft.com/zh-cn/sql/relational-databases/databases/database-detach-and-attach-sql-server?view=sql-server-ver16)
資料庫->任務->分離，開啟資料庫分離對話方塊

![在這裡插入圖片描述](https://img-blog.csdnimg.cn/8ba9f1d03f244113a7c159f7953586fe.png)
勾選刪除連線
![在這裡插入圖片描述](https://img-blog.csdnimg.cn/12d3d885409b46c3bf2eb6d08359da05.png)
點選確定，等待資料庫分離完成

複製資料庫檔案到目標伺服器的/var/opt/mssql/data目錄下
使用SQL Server Management Studio連線目標伺服器資料庫，資料庫->附加

點選“新增..”，選擇/var/opt/mssql/data目錄下的`.mdf`檔案，點選確定，等待資料庫附加完成

## 資料庫備份
資料庫->任務->備份，開啟備份對話方塊
![在這裡插入圖片描述](https://img-blog.csdnimg.cn/48e06e10db7649bab81dfd19ae56de28.png)
制定備份策略
策略是每週一次的全量備份，每天一次的增量備份， 全量備份的檔案需要單獨複製到其他地方做異地備份。
每次全量備份，將單獨生成獨立的`.bak`檔案，命名以`-full-.bak`為標準
如 `BlogDb-full-0216.bak`
增量備份時，備份型別選擇“差異”，詳情參見官方文件：[差異備份 (SQL Server)](https://learn.microsoft.com/zh-cn/sql/relational-databases/backup-restore/differential-backups-sql-server?view=sql-server-ver16)
指定備份目標到“磁碟”，並新增一個路徑，這裡以`/var/opt/mssql/backup`目錄為例

![在這裡插入圖片描述](https://img-blog.csdnimg.cn/fcd8fdee74284a078456667711966682.png)
點選確定開始備份
等待備份完成，宿主機的備份目錄下，可以看到`.bak`檔案，將這些檔案複製至其他伺服器上以實現異地備份
![在這裡插入圖片描述](https://img-blog.csdnimg.cn/ec6b4a447dfa4e9c8db906f855b2d3e6.png)

## 還原資料庫
資料庫中右鍵，選擇還原資料庫
“源”中選擇裝置，並指定備份介質為目標`.bak`檔案
![在這裡插入圖片描述](https://img-blog.csdnimg.cn/4dfcaee036d74218b05bb1c46f815441.png)

選擇後可以檢視最新的備份集，如果備份集包含多個差異備份，可以透過時間線功能，檢視並選擇所需要的備份集位置
![在這裡插入圖片描述](https://img-blog.csdnimg.cn/a3651d5aa33149369f463f7a3844cde4.png)
點選需要還原的備份集
![在這裡插入圖片描述](https://img-blog.csdnimg.cn/1ea35cec8c5e481cbb4123ca1cd5f5bb.png)
點選確定開始還原

# 方式二，使用命令列工具

在客戶機上往往不提供Windows環境，因此需要使用終端工具透過命令列完成操作

## 準備工作

sqlcmd 實用工具是一個命令列實用工具，用於執行 Transact-SQL 語句，詳情請參考官方文件
[sqlcmd - 使用實用工具](https://learn.microsoft.com/zh-CN/sql/tools/sqlcmd/sqlcmd-use-utility?view=sql-server-ver16&viewFallbackFrom=sql-server-linux-2017)，[Transact-SQL 語句](https://learn.microsoft.com/zh-cn/sql/t-sql/statements/create-database-transact-sql?view=sql-server-ver16&tabs=sqlpool)
安裝mssql-tools工具集，其中包含了我們要用的sqlcmd 實用工具:
```
curl https://packages.microsoft.com/config/rhel/8/prod.repo > /etc/yum.repos.d/msprod.repo
sudo yum install -y mssql-tools unixODBC-devel
echo 'export PATH="$PATH:/opt/mssql-tools/bin"' >> ~/.bash_profile
echo 'export PATH="$PATH:/opt/mssql-tools/bin"' >> ~/.bashrc
```

確認資料庫容器對映的宿主機實體地址，若使用docker方式部署SQL server，且沒有做目錄對映，則按照下面的方式操作

首先將docker容器中的 /var/opt/mssql目錄內容複製到宿主機中的相同目錄下

```
docker cp sqlserver2017:/var/opt/mssql/ /var/opt/mssql
```

停止原始容器
```
docker stop sqlserver2017
```

新建容器，將/var/opt/mssql目錄對映至宿主機中的目錄中
```
docker run -e "ACCEPT_EULA=Y" -e "MSSQL_SA_PASSWORD=" -p 1433:1433 -v /var/opt/mssql:/var/opt/mssql --name sqlserver2017 --restart always -d mcr.microsoft.com/mssql/server:2017-latest
```
或者如果按照官方文件中的卷操作，輸入
```
docker volume ls
docker volume inspect 
```
檢視對應sqlserver容器的卷所對映的物理路徑
![在這裡插入圖片描述](https://img-blog.csdnimg.cn/cd8c9f8a49854470ad1ca42e2873bb52.png)
物理路徑下的內容：
![在這裡插入圖片描述](https://img-blog.csdnimg.cn/555c129b569a45479ba5c6118359a1ad.png)
將資料庫的恢復模式設定為“簡單”，執行命令：
```
sqlcmd -S <sqlserver> -U SA -P "" -Q "ALTER DATABASE [] SET RECOVERY SIMPLE"
```

將資料庫的相容級別設定為130，執行命令：
```
sqlcmd -S <sqlserver> -U SA -P "" -Q "ALTER DATABASE  [] SET COMPATIBILITY_LEVEL = 130;"
```

[可選]將表中將無用的業務資料刪除，以減少備份或移動的檔案大小，這一步根據業務需求而決定，以ABP系統為例，執行下列SQL命令將`AbpAuditLogs`表中的資料清除：
```
sqlcmd -S <sqlserver> -U SA -P "" -Q "TRUNCATE TABLE  [].[dbo].[AbpAuditLogs]"
```

## 收縮資料庫
執行命令
```
sqlcmd -S <sqlserver> -U SA -P "" -Q "DBCC SHRINKDATABASE (, 10)"
```
這一步將減少資料庫中資料檔案和日誌檔案的大小，並允許資料庫中有 10% 的可用空間。
![在這裡插入圖片描述](https://img-blog.csdnimg.cn/25a05e8a29e94aedb0dcc8d0a6ec58ff.png)

## 移動資料庫

首先將資料庫設定下線

```
sqlcmd -S <sqlserver> -U SA -P "" -Q "ALTER DATABASE [] SET OFFLINE WITH ROLLBACK IMMEDIATE"
```

分離資料庫
```
sqlcmd -S <sqlserver> -U SA -P "" -Q "EXEC sp_detach_db '', 'true'"
```

複製資料庫檔案到目標伺服器的/var/opt/mssql/data目錄下
```
scp -r -P 22 root@<sqlserver>:/var/opt/mssql/data/.mdf /var/opt/mssql/data
scp -r -P 22 root@<sqlserver>:/var/opt/mssql/data/_log.ldf /var/opt/mssql/data
```

在目標伺服器中附加這個資料庫
```
sqlcmd -S <sqlserver> -U SA -P "" -Q "CREATE DATABASE [] ON (FILENAME = '/var/opt/mssql/data/.mdf'),(FILENAME = '/var/opt/mssql/data/_log.ldf') FOR ATTACH"
```

## 備份資料庫
執行命令

```
sqlcmd -S <sqlserver> -U SA -P "" -Q "BACKUP DATABASE [] TO DISK = N'/var/opt/mssql/backup/-full.bak' WITH NOFORMAT, NOINIT, NAME = '-full', SKIP, NOREWIND, NOUNLOAD, STATS = 10"
```
備份應該只包含上次完整備份後更改的資料庫或檔案部分。 差異備份一般會比完整備份佔用更少的空間
如果是差異化備份，請新增如下引數
```
 WITH DIFFERENTIAL
```
等待資料庫備份完成
![在這裡插入圖片描述](https://img-blog.csdnimg.cn/e14efd2b5f224d86bb0907daafb5acbe.png)

[可選]將備份檔案`.bak`檔案複製至目標伺服器中，實現異地備份
```
mkdir /var/opt/mssql/backup
scp -r -P 22 root@<sqlserver>:/var/opt/mssql/backup/-full.bak /var/opt/mssql/backup
```

## 還原資料庫

執行命令
```
sqlcmd -S <sqlserver> -U SA -P "" -Q "RESTORE DATABASE [] FROM DISK = N'/var/opt/mssql/backup/-full.bak' WITH NOUNLOAD, REPLACE, NORECOVERY, STATS = 5"
```
如果資料庫備份檔案還包含備差異備份，則還需要選擇還原的差異備份集位置，

```
WITH FILE = 1
```

比如要還原的完整資料庫備份是裝置上的第六個備份集 (FILE = 6)，差異資料庫備份是裝置上的第九個備份集 (FILE = 9)。 在恢復了差異備份之後，便恢復了資料庫。

![在這裡插入圖片描述](https://img-blog.csdnimg.cn/fd0df456ce014ad2bccf0beab55c373a.png)</sqlserver></sqlserver></sqlserver></sqlserver></sqlserver></sqlserver></sqlserver></sqlserver></sqlserver></sqlserver></sqlserver></sqlserver>

深入解讀.NET MAUI音樂播放器專案（三）：介面互動

相關文章