Database-SQL-Server-03-SQL-Server-Backup
备份方式
完全备份#
备份指定目标对象的所有数据,可使用该数据恢复该时刻的所有数据
优点#
备份简单，恢复简单快速，数据完整
缺点#
备份耗时久，且反复备份，占用存储空间较多
差异备份#
备份自上一次完全备份之后有变化的数据，需要结合上一次的完全备份数据才能恢复该差异备份时刻的所有数据
优点#
备份数据量小，备份速度比完全备份快。
缺点#
相对而言，恢复数据所耗费的时间比完全备份时间长
事务日志备份#
防止数据库日志ldf 文件一直增长，需要定时给事务日志进行备份，这样能截断日志文件
关于如何收缩数据请参考本文档末尾
需要先启用SQL代理
启用SQL Agent#
参考以下文档中对应节内容
UI和脚本2选1
UI创建自动备份Job#
在SQL Server Agent 下的Jobs 中右键，选New Job
填入Job 名称
创建步骤：Steps 中选New
填写步骤名，填入命令后点击OK
-- 以下命令2选1，备份的事务日志能用于恢复误删数据等，根据需求选择是否备份到文件中
-- 根据实际情况，替换<DatabaseName>为对应数据库名
-- 备份数据库的事务日志到Null设备，即不保存
BACKUP LOG [<DatabaseName>] TO DISK = 'NUL';
-- 备份数据库的事务日志到/var/opt/mssql/data/Test.trn文件
BACKUP LOG [<DatabaseName>] TO DISK = '/var/opt/mssql/data/Test.trn';
创建定时规则：回到Job页面，选择Schedules 点击New
填写定时规则：填写定时名称，Frequency 下的Occurs 中选择Daily
勾选Daily fraequency 下的Occurs once at ，选择合适时间例如：12:00:00 AM ，点击OK
回到主界面，点击OK
脚本创建自动备份Job#
注意替换脚本中的@command 中的命令行
USE [msdb]
GO
DECLARE @jobId BINARY(16)
EXEC msdb.dbo.sp_add_job @job_name=N'AutoBackupLog',
@enabled=1,
@notify_level_eventlog=0,
@notify_level_email=2,
@notify_level_page=2,
@delete_level=0,
@category_name=N'[Uncategorized (Local)]',
@owner_login_name=N'sa', @job_id = @jobId OUTPUT
select @jobId
GO
EXEC msdb.dbo.sp_add_jobserver @job_name=N'AutoBackupLog', @server_name = N'DB1'
GO
USE [msdb]
GO
EXEC msdb.dbo.sp_add_jobstep @job_name=N'AutoBackupLog', @step_name=N'BackupLog',
@step_id=1,
@cmdexec_success_code=0,
@on_success_action=1,
@on_fail_action=2,
@retry_attempts=0,
@retry_interval=0,
@os_run_priority=0, @subsystem=N'TSQL',
@command=N'BACKUP LOG [<DatabaseName>] TO DISK = ''NUL'';',
@database_name=N'master',
@flags=0
GO
USE [msdb]
GO
EXEC msdb.dbo.sp_update_job @job_name=N'AutoBackupLog',
@enabled=1,
@start_step_id=1,
@notify_level_eventlog=0,
@notify_level_email=2,
@notify_level_page=2,
@delete_level=0,
@description=N'',
@category_name=N'[Uncategorized (Local)]',
@owner_login_name=N'sa',
@notify_email_operator_name=N'',
@notify_page_operator_name=N''
GO
USE [msdb]
GO
DECLARE @schedule_id int
EXEC msdb.dbo.sp_add_jobschedule @job_name=N'AutoBackupLog', @name=N'EveryDay',
@enabled=1,
@freq_type=4,
@freq_interval=1,
@freq_subday_type=1,
@freq_subday_interval=0,
@freq_relative_interval=0,
@freq_recurrence_factor=1,
@active_start_date=20221103,
@active_end_date=99991231,
@active_start_time=0,
@active_end_time=235959, @schedule_id = @schedule_id OUTPUT
select @schedule_id
GO
备份策略
每天完全备份，保留7天#
周日完全备份，其余差异备份，保留7天#
设置定时备份
脚本（！未完成请使用UI！）#
每天完全备份，保留7天#
注意：不能直接运行！！！以下脚本中<DatabaseName> <DataBaseFilePath> 需要替换为相应值
/*
方法介绍：
时间函数
--使用select convert(char,getdate(),120)120或者10，查看不同格式
select SUBSTRING (convert(char,getdate(),120), 0, 11 )--年月日，格式2022-05-23，想改变格式，可以改变120这个数字
select REPLACE(convert(char,getdate(),14), ':', '_') --时间格式 23_08_23_833
创建文件夹命令
DECLARE
@foldername VARCHAR ( 255 );--文件存放路径
SET @foldername ='mkdir D:\Work\SqlServerBak\'+SUBSTRING (convert(char,getdate(),120), 0, 11 )
ExEc xp_cmdshell @foldername --调用DOS命令创建project文件夹
*/
DECLARE
@filenameWithDate VARCHAR ( 255 );--文件名
DECLARE
@<DataBaseFilePath> VARCHAR ( 255 );--文件存放路径
--根据当前时间自动生成文件名，后缀为.bak
SET @filenameWithDate = '_' + SUBSTRING ( CONVERT ( CHAR, getdate( ), 120 ), 0, 11 ) + ' ' + REPLACE( REPLACE( CONVERT ( CHAR, getdate( ), 14 ), ':', '_' ), ' ', '' ) + '.bak';
SET @<DataBaseFilePath> = N'/var/opt/mssql/backupjob/<DataBaseName>_backup' + @filenameWithDate;--设置文件路径
-- !!!以下备份脚本 2选1!!!
-- 1/[2] 只在副本备份数据库（如果启用了AG）
IF sys.fn_hadr_is_primary_replica ( '<DatabaseName>' ) <> 1
BEGIN
BACKUP DATABASE [<DatabaseName>] TO DISK = @<DataBaseFilePath> WITH COPY_ONLY;
END
-- 2/[2] 备份数据库,未启用AG
BACKUP DATABASE [<DatabaseName>] TO DISK = @<DataBaseFilePath>;
--删除7天前的备份文件
declare @endtime varchar(200)
set @endtime = dateadd(day,-7,getdate())--负7就是7天前
exec master..xp_delete_file 0,N'/var/opt/mssql/backupjob','bak',@endtime`
`/*
方法介绍：
时间函数
--使用select convert(char,getdate(),120)120或者10，查看不同格式
select SUBSTRING (convert(char,getdate(),120), 0, 11 )--年月日，格式2022-05-23，想改变格式，可以改变120这个数字
select REPLACE(convert(char,getdate(),14), ':', '_') --时间格式 23_08_23_833
创建文件夹命令
DECLARE
@foldername VARCHAR ( 255 );--文件存放路径
SET @foldername ='mkdir D:\Work\SqlServerBak\'+SUBSTRING (convert(char,getdate(),120), 0, 11 )
ExEc xp_cmdshell @foldername --调用DOS命令创建project文件夹
--开启高级模式
EXEC sp_configure 'show advanced options',1;
RECONFIGURE;
EXEC sp_configure 'xp_cmdshell', 1;
RECONFIGURE;
--不开启也可以删除
--关闭高级选项
EXEC sp_configure 'xp_cmdshell', 0;
RECONFIGURE;
EXEC sp_configure 'show advanced options', 0;
RECONFIGURE;
*/
DECLARE
@filename VARCHAR ( 255 );--文件名
DECLARE
@path_components VARCHAR ( 255 );--文件存放路径
--根据当前时间自动生成文件名，后缀为.bak
SET @filename = '_' + SUBSTRING ( CONVERT ( CHAR, getdate( ), 120 ), 0, 11 ) + ' ' + REPLACE( REPLACE( CONVERT ( CHAR, getdate( ), 14 ), ':', '_' ), ' ', '' ) + '.bak';
SET @path_components = N'/var/opt/mssql/backup/components_backup' + @filename;--设置文件路径
--只在副本备份数据库
IF sys.fn_hadr_is_primary_replica ( 'components' ) <> 1
BEGIN
BACKUP DATABASE [components] TO DISK = @path_components WITH COPY_ONLY;
END
--删除3天前的备份文件
declare @endtime varchar(200)
set @endtime = dateadd(day,-3,getdate())--负10就是十天
exec master..xp_delete_file 0,N'/var/opt/mssql/backup','bak',@endtime`
### 周日完全备份，其余差异备份，保留7天
`DECLARE
@filenameWithDate VARCHAR ( 255 );--文件名
DECLARE
@<DataBaseFilePath> VARCHAR ( 255 );--文件存放路径
--根据当前时间自动生成文件名，后缀为.bak
SET @filenameWithDate = '_' + SUBSTRING ( CONVERT ( CHAR, getdate( ), 120 ), 0, 11 ) + ' ' + REPLACE( REPLACE( CONVERT ( CHAR, getdate( ), 14 ), ':', '_' ), ' ', '' ) + '.bak';
SET @<DataBaseFilePath> = N'/var/opt/mssql/backupjob/<DataBaseName>_backup' + @filenameWithDate;--设置文件路径
--差异备份数据库
BACKUP DATABASE <DatabaseName> TO DISK = @filenameWithDate WITH DIFFERENTIAL, NOFORMAT, NOINIT, SKIP, NOREWIND, NOUNLOAD, STATS = 100
--删除7天前的备份文件
declare @endtime varchar(200)
set @endtime = dateadd(day,-7,getdate())--负7就是7天前
exec master..xp_delete_file 0,N'/var/opt/mssql/backupjob','bak',@endtime
UI#
每天完全备份，保留7天#
新建维护计划#
输入维护计划名，例如BackupPlan
新建并配置完全备份数据库任务#
选中Subplan_1
拖拽工具箱中的备份数据库任务到新标签页
在备份任务上右键单击，选择编辑(Edit )
连接（Connection ）选择本地服务器连接(Local server connection )
选择通用(General )标签页中
备份类型(Backup type )选择Full
数据库（Database(s) )选所有用户数据库(All user databases (excluding master,model,msdb,tempdb) )，点击OK保存
选择选项（Options ）标签页
设置备份压缩（Set backup compression ）选压缩备份(Compress backup )
勾选备份失效（Backup set will expire ）
选择之后（After ）
选择7天（days ）
点击OK
设置调度频率及时间#
点击调度（Schedule ）下的日历📅按钮
调度类型(Schedule type )选重复（Recurring ）
勾选启用（Enabled ）
频率（Frequency ）
Occurs 选天（Daily ）
Recurs every 选1天（1 days ）
每天频率（Daily frequency ）
Occurs once at 选午夜12点12:00:00AM 或其它系统空闲时间
Duration 下选中No end date
点击OK
设置数据库连接账号及密码#
点击管理连接（Manage Connections ... ）
选择本地服务器连接（Local server connection ）行
点击编辑（Edit ）按钮
填写备份任务执行时使用的用户名（User name ）
填写该用户名对应密码（Password ）
点击OK
保存维护计划#
关闭维护计划标签页
在跳出的是否保存更改（Save changes to the following items ）的窗口中选择Yes
周日完全备份，其余差异备份，保留7天#
新建维护计划#
输入维护计划名，例如BackupPlan
新建并配置完全备份数据库任务#
选择子计划Subplan_1
拖拽工具箱中的备份数据库任务到新标签页
在备份任务上右键单击，选择编辑(Edit )
连接（Connection ）选择本地服务器连接(Local server connection )
选择通用(General )标签页中
备份类型(Backup type )选择Full
数据库（Database(s) )选所有用户数据库(All user databases (excluding master,model,msdb,tempdb) )，点击OK保存
选择选项（Options ）标签页
设置备份压缩（Set backup compression ）选压缩备份(Compress backup )
勾选备份失效（Backup set will expire ）
选择之后（After ）
选择7天（days ）
点击OK
设置完全备份计划Subplan_1 调度频率及时间#
点击调度（Schedule ）下的日历📅按钮
调度类型(Schedule type )选重复（Recurring ）
勾选启用（Enabled ）
频率（Frequency ）
Occurs 选天（Weekly ）
Recurs every 选星期7（Sunday ）或者其它系统空闲星期X
每天频率（Daily frequency ）
Occurs once at 选午夜12点12:00:00AM 或其它系统空闲时间
Duration 下选中No end date
点击OK
新建并配置差异备份数据库任务#
点击新增子计划（Add Subplan ）
点击OK
选择新增的子计划Subplan_2
拖拽工具箱中的备份数据库任务到新标签页
在备份任务上右键单击，选择编辑(Edit )
连接（Connection ）选择本地服务器连接(Local server connection )
选择通用(General )标签页中
备份类型(Backup type )选择差异Differential
数据库（Database(s) )选所有用户数据库(All user databases (excluding master,model,msdb,tempdb) )，点击OK 保存
选择目标（Destination ）标签页
勾选对每一个数据库创建子文件夹（Create a sub-directory for each database ），以便区分完全备份文件和差异备份文件
选择选项（Options ）标签页
设置备份压缩（Set backup compression ）选压缩备份(Compress backup )
勾选备份失效（Backup set will expire ）
选择之后（After ）
选择7天（days ）
点击OK
设置差异备份计划Subplan_2 调度频率及时间#
点击调度（Schedule ）下的日历📅按钮
调度类型(Schedule type )选重复（Recurring ）
勾选启用（Enabled ）
频率（Frequency ）
Occurs 选周（Weekly ）
Recurs every 选星期1-星期6（Monday Tuesday Wednesday Thursday Friday Saturday ）或其它系统空闲星期，需要与完全备份的星期刚好互补为完整的一个星期
每天频率（Daily frequency ）
Occurs once at 选午夜12点12:00:00AM 或其它系统空闲时间
Duration 下选中No end date
点击OK
设置数据库连接账号及密码#
点击管理连接（Manage Connections ... ）
选择本地服务器连接（Local server connection ）行
点击编辑（Edit ）按钮
填写备份任务执行时使用的用户名（User name ）
填写该用户名对应密码（Password ）
点击OK
保存维护计划#
关闭维护计划标签页
在跳出的是否保存更改（Save changes to the following items ）的窗口中选择Yes
常见问题（FAQ）
Q1: 启用事务日志定时备份后，为什么日志还是那么大
A1：因为数据库默认规则，不会收缩日志文件和数据库文件，需要手动收缩
具体方法如下：
！！！注意：为了防止收缩时卡住，操作之前请先备份数据库！！！
在主SQL Server下
选择对应数据右键，选Tasks => Shrink
点击OK 即可
确认日志已收缩
`-- 确认日志文件大小和实际占用空间
DBCC SQLPERF(LOGSPACE)
-- 收缩日志文件，如果收缩日志不可能，则使用以下方式
-- DBCC SHRINKFILE(
-- 查看为什么不能收缩事务日志文件的原因
-- 返回结果的含义请参阅
-- https://learn.microsoft.com/en-us/sql/relational-databases/system-catalog-views/sys-databases-transact-sql?view=sql-server-ver15#:~:text=enabled after failover.-,log_reuse_wait,-tinyint
SELECT log_reuse_wait_desc FROM sys.databases WHERE name='
作者：KSPT
出处：https://www.cnblogs.com/KSPT/p/Database-SQL-Server-03-SQL-Server-Backup.html
版权：本作品采用「署名-非商业性使用-相同方式共享 4.0 国际」许可协议进行许可。
【推荐】国内首个AI IDE，深度理解中文开发场景，立即下载体验Trae
【推荐】编程新体验，更懂你的AI，立即体验豆包MarsCode编程助手
【推荐】抖音旗下AI助手豆包，你的智能百科全书，全免费不限次数
【推荐】轻量又高性能的 SSH 工具 IShell：AI 加持，快人一步
· DeepSeek 开源周回顾「GitHub 热点速览」
· 物流快递公司核心技术能力-地址解析分单基础技术分享
· .NET 10首个预览版发布：重大改进与新特性概览！
· AI与.NET技术实操系列（二）：开始使用ML.NET
· 单线程的Redis速度为什么快？