[學習筆記]SQL server完全備份指南

林曉lx發表於2023-02-21
筆記SQLServer

方式一，使用SQL Server Management Studio

準備工作

連線目標資料庫伺服器

在目標資料庫上右鍵->屬性，將資料庫的恢復模式設定為“簡單”，相容級別設定為“SQL Server 2016(130)”
在這裡插入圖片描述

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

TRUNCATE TABLE [dbo].[AbpAuditLogs]

收縮資料庫

在這裡插入圖片描述
這一步將減少資料庫中資料檔案和日誌檔案的大小，並允許資料庫中有 10% 的可用空間。
在這裡插入圖片描述

移動資料庫

資料庫->任務->分離，開啟資料庫分離對話方塊

在這裡插入圖片描述
勾選刪除連線
在這裡插入圖片描述
點選確定，等待資料庫分離完成

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

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

資料庫備份

資料庫->任務->備份，開啟備份對話方塊
在這裡插入圖片描述
制定備份策略
策略是每週一次的全量備份，每天一次的增量備份， 全量備份的檔案需要單獨複製到其他地方做異地備份。
每次全量備份，將單獨生成獨立的.bak檔案，命名以<資料庫名稱>-full-<日期編號>.bak為標準
如 BlogDb-full-0216.bak
增量備份時，備份型別選擇“差異”
指定備份目標到“磁碟”，並新增一個路徑，這裡以/var/opt/mssql/backup目錄為例

在這裡插入圖片描述
點選確定開始備份
等待備份完成，宿主機的備份目錄下，可以看到.bak檔案，將這些檔案複製至其他伺服器上以實現異地備份
在這裡插入圖片描述

還原資料庫

資料庫中右鍵，選擇還原資料庫
“源”中選擇裝置，並指定備份介質為目標.bak檔案
在這裡插入圖片描述

選擇後可以檢視最新的備份集，如果備份集包含多個差異備份，可以透過時間線功能，檢視並選擇所需要的備份集位置
在這裡插入圖片描述
點選需要還原的備份集
在這裡插入圖片描述
點選確定開始還原

方式二，使用命令列工具

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

準備工作

安裝sql-tool工具:

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容器的卷所對映的物理路徑
在這裡插入圖片描述
物理路徑下的內容：
在這裡插入圖片描述

[可選]將表中將無用的業務資料刪除，以減少備份或移動的檔案大小，這一步根據業務需求而決定，以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% 的可用空間。
在這裡插入圖片描述

移動資料庫

首先將資料庫設定下線

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

等待資料庫備份完成
在這裡插入圖片描述

[可選]將備份檔案.bak檔案複製至目標伺服器中，實現異地備份

 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://learn.microsoft.com/zh-cn/SQL/linux/sql-server-linux-backup-and-restore-database?view=sql-server-2017
https://learn.microsoft.com/zh-CN/sql/tools/sqlcmd/sqlcmd-use-utility?view=sql-server-ver16&viewFallbackFrom=sql-server-linux-2017
https://learn.microsoft.com/zh-cn/sql/relational-databases/databases/shrink-a-database?view=sql-server-ver16
https://learn.microsoft.com/zh-cn/sql/relational-databases/backup-restore/differential-backups-sql-server?view=sql-server-ver16
https://learn.microsoft.com/zh-cn/sql/t-sql/statements/create-database-transact-sql?view=sql-server-ver16&tabs=sqlpool
https://learn.microsoft.com/zh-cn/sql/relational-databases/databases/database-detach-and-attach-sql-server?view=sql-server-ver16

相關文章