SRE文档

深浅模式

1.查看备份记录 ​

sql
-- 查看备份记录
SELECT 
    CONVERT(CHAR(100), SERVERPROPERTY('Servername')) AS Server, 
    msdb.dbo.backupset.database_name, 
    msdb.dbo.backupset.backup_start_date, 
    msdb.dbo.backupset.backup_finish_date, 
    msdb.dbo.backupset.expiration_date, 
    CASE msdb..backupset.type 
    WHEN 'D' THEN 'FULL Backup' 
    WHEN 'I' THEN 'DIFF Backup'
    WHEN 'L' THEN 'Log Backup' 
END AS backup_type, 
msdb.dbo.backupset.backup_size, 
msdb.dbo.backupmediafamily.logical_device_name, 
msdb.dbo.backupmediafamily.physical_device_name, 
msdb.dbo.backupset.name AS backupset_name, 
family_sequence_number '介质簇位置',
msdb.dbo.backupset.description 
FROM msdb.dbo.backupmediafamily 
INNER JOIN msdb.dbo.backupset ON msdb.dbo.backupmediafamily.media_set_id = msdb.dbo.backupset.media_set_id 
WHERE (CONVERT(datetime, msdb.dbo.backupset.backup_start_date, 102) >= GETDATE() - 7) 
ORDER BY msdb.dbo.backupset.database_name, msdb.dbo.backupset.backup_finish_date
---
-- 查看备份记录
SELECT 
    CONVERT(CHAR(100), SERVERPROPERTY('Servername')) AS Server, 
    msdb.dbo.backupset.database_name, 
    msdb.dbo.backupset.backup_start_date, 
    msdb.dbo.backupset.backup_finish_date, 
    msdb.dbo.backupset.expiration_date, 
    CASE msdb..backupset.type 
    WHEN 'D' THEN 'FULL Backup' 
    WHEN 'I' THEN 'DIFF Backup'
    WHEN 'L' THEN 'Log Backup' 
END AS backup_type, 
msdb.dbo.backupset.backup_size, 
msdb.dbo.backupmediafamily.logical_device_name, 
msdb.dbo.backupmediafamily.physical_device_name, 
msdb.dbo.backupset.name AS backupset_name, 
family_sequence_number '介质簇位置',
msdb.dbo.backupset.description 
FROM msdb.dbo.backupmediafamily 
INNER JOIN msdb.dbo.backupset ON msdb.dbo.backupmediafamily.media_set_id = msdb.dbo.backupset.media_set_id 
WHERE (CONVERT(datetime, msdb.dbo.backupset.backup_start_date, 102) >= GETDATE() - 7) 
ORDER BY msdb.dbo.backupset.database_name, msdb.dbo.backupset.backup_finish_date
---

查询当前数据库备份进度 ​

sql
--查询当前数据库备份进度
SELECT   DB_NAME(er.[database_id]) [DatabaseName],er.[command] AS [CommandType],er.[percent_complete]
,er.start_time,CONVERT(DECIMAL(5, 2) , er.[percent_complete]) AS [Complete_Percent]
,CONVERT(DECIMAL(38, 2), er.[total_elapsed_time] / 60000.00) AS [ElapsedTime_m]  
,CONVERT(DECIMAL(38, 2), er.[estimated_completion_time] / 60000.00) AS [EstimatedCompletionTime_m]  
FROM sys.dm_exec_requests AS er  
WHERE er.[command] in ( 'RESTORE DATABASE' ,'BACKUP DATABASE')  -- DB_NAME(er.[database_id]) in ('ky2011') and
--查询当前数据库备份进度
SELECT   DB_NAME(er.[database_id]) [DatabaseName],er.[command] AS [CommandType],er.[percent_complete]
,er.start_time,CONVERT(DECIMAL(5, 2) , er.[percent_complete]) AS [Complete_Percent]
,CONVERT(DECIMAL(38, 2), er.[total_elapsed_time] / 60000.00) AS [ElapsedTime_m]  
,CONVERT(DECIMAL(38, 2), er.[estimated_completion_time] / 60000.00) AS [EstimatedCompletionTime_m]  
FROM sys.dm_exec_requests AS er  
WHERE er.[command] in ( 'RESTORE DATABASE' ,'BACKUP DATABASE')  -- DB_NAME(er.[database_id]) in ('ky2011') and

2.备份日志 ​

2.1备份事物日志 ​

sql
-- 前提的是FULL 类型
ALTER DATABASE hantest SET RECOVERY FULL

-- 的有数据库完整备份
USE [master]
BACKUP DATABASE [hantest] TO DISK = N'D:\hanback\hanTestDB.bak'  WITH NOFORMAT, NOINIT, NAME = N'hantestTestDB-Full Database Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10


BACKUP LOG [hantest] TO DISK='NUL:'  -- 备份事务日志，备份成NUL，就不用占硬盘空间
-- 前提的是FULL 类型
ALTER DATABASE hantest SET RECOVERY FULL

-- 的有数据库完整备份
USE [master]
BACKUP DATABASE [hantest] TO DISK = N'D:\hanback\hanTestDB.bak'  WITH NOFORMAT, NOINIT, NAME = N'hantestTestDB-Full Database Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10


BACKUP LOG [hantest] TO DISK='NUL:'  -- 备份事务日志，备份成NUL，就不用占硬盘空间

3.备份类型 ​

3.1完整备份 ​

文档，https://learn.microsoft.com/zh-cn/sql/t-sql/statements/backup-transact-sql?view=sql-server-ver16

sql
#完全备份
backup database db_name to disk = 'E:\backup\db_name.bak' with stats 10,retaindays=9,format,init,skip;

-- stats 显示每 1% 后的统计信息的选项
-- format[noformat] ,覆盖任何现有的介质标头和备份集
-- init 覆盖备份介质中的现有备份集,默认为追加到介质中最新的备份集 (NOINIT)
-- retaindays=9 表示必须经过多少天才可以覆盖该备份集
-- PASSWORD = 'Q!W@E#R$' 设置密码
-- skip 禁用备份集的过期和名称检查
#完全备份
backup database db_name to disk = 'E:\backup\db_name.bak' with stats 10,retaindays=9,format,init,skip;

-- stats 显示每 1% 后的统计信息的选项
-- format[noformat] ,覆盖任何现有的介质标头和备份集
-- init 覆盖备份介质中的现有备份集,默认为追加到介质中最新的备份集 (NOINIT)
-- retaindays=9 表示必须经过多少天才可以覆盖该备份集
-- PASSWORD = 'Q!W@E#R$' 设置密码
-- skip 禁用备份集的过期和名称检查

E:\backup，保证路径提前创建好

https://edu.51cto.com/center/course/lesson/index?id=18190

3.2差异备份 ​

  • 语法
sql
BACKUP LOG database_name 
TO DISK  =  'physical_device_name'
[ WITH { 
  COPY_ONLY
| { COMPRESSION | NO_COMPRESSION } 
| { NOINIT | INIT } 
| { NOSKIP | SKIP } 
| { NOFORMAT | FORMAT } 
| STATS [ = percentage ] 
| { NORECOVERY | STANDBY = undo_file_name }
| NO_TRUNCATE }]

-- NORECOVERY 选项，指定备份事务日志的尾部，并使数据库处于RESTORING状态
BACKUP LOG database_name 
TO DISK  =  'physical_device_name'
[ WITH { 
  COPY_ONLY
| { COMPRESSION | NO_COMPRESSION } 
| { NOINIT | INIT } 
| { NOSKIP | SKIP } 
| { NOFORMAT | FORMAT } 
| STATS [ = percentage ] 
| { NORECOVERY | STANDBY = undo_file_name }
| NO_TRUNCATE }]

-- NORECOVERY 选项，指定备份事务日志的尾部，并使数据库处于RESTORING状态
sql
backup database db_name to disk='E:\backup\db_name.bak' with differential,retaindays=9,noformat,noinit,compression;
backup database db_name to disk='E:\backup\db_name.bak' with differential,retaindays=9,noformat,noinit,compression;

3.3事务日志备份 ​

要执行事务日志的备份，数据库的恢复模式（Recovery Mode）必须是FULL，并且数据库必须执行过一次数据库的完整备份操作，否则，事务日志将处于自动截断（Auto-Truncate）状态，无法进行事务日志备份

尾日志备份 ​

NORECOVERY 选项，指定备份事务日志的尾部，并使数据库处于RESTORING状态

sql
backup log db_name
to disk = 'D:\backup\db_name.trn'
with
compression,
format,
init,
skip,
stats=5，
norecovery
backup log db_name
to disk = 'D:\backup\db_name.trn'
with
compression,
format,
init,
skip,
stats=5，
norecovery

日志截断 ​

正常情况下，数据库处于Online状态，在进行事务日志备份时，如果不指定 NO_TRUNCATE 选项，那么数据库将已备份的事务日志文件截断，避免事务日志过大，耗尽disk空间；如果指定 NO_TRUNCATE 选项，表示日志备份不会将事务日志文件截断，该选项一般在数据库处于异常状态时使用

自动截断模式 ​

如果数据库符合以下两种条件之一，那么Database就处于自动截断模式：

  • 数据库的恢复模式是simple；
  • 数据库的恢复模式是full 或 bulk_Logged，并且没有做过数据库完整备份；

自动截断模式是指数据库引擎把处于可还原状态（recoverable）状态的事务日志自动截断，使日志文件能够重复使用，避免日志文件无限增长。如果事务日志不是自动截断模式，那么事务日志会保存到日志文件中，导致日志文件持续增长。只有做日志备份时，日志文件才会被截断；如果没有定期的日志备份，那么日志文件会持续地增长，直到耗尽磁盘的所有空间，因此，必须制定一个日志备份计划，把事务日志截断，才能使数据库的事务日志文件的大小保持在一个可以管理的水平上

  • 查看数据截断模式
sql
select db_name(database_id) as DBName,
    case when last_log_backup_lsn is null then 'Auto' else 'Manul' end as TruncateMode
from sys.database_recovery_status
select db_name(database_id) as DBName,
    case when last_log_backup_lsn is null then 'Auto' else 'Manul' end as TruncateMode
from sys.database_recovery_status

sql
backup log db_name
to disk = 'D:\TestDBBackupFolder\Sitedb_bak3.trn'
with
compression,
format,
init,
skip,
stats=5
backup log db_name
to disk = 'D:\TestDBBackupFolder\Sitedb_bak3.trn'
with
compression,
format,
init,
skip,
stats=5

4.恢复 ​

sql
USE [master];
GO

RESTORE DATABASE [DBTest]
FROM DISK = N'C:\Program Files\Microsoft SQL
Server\MSSQL15.MSSQLSERVER\MSSQL\Backup\bc\DBTest.bak' WITH FILE = 1, NOUNLOAD, STATS = 5;
GO
USE [master];
GO

RESTORE DATABASE [DBTest]
FROM DISK = N'C:\Program Files\Microsoft SQL
Server\MSSQL15.MSSQLSERVER\MSSQL\Backup\bc\DBTest.bak' WITH FILE = 1, NOUNLOAD, STATS = 5;
GO

4.自动备份 ​

==前提是开启代理服务==

去启动服务，在桌面找到计算机，右键点击【管理】，找到【服务和应用程序】，点击【服务】，找到Sql Server 代理(MSQLSERVER)，点击右键，启动

1.维护计划向导 ​

  • 第一步：登录数据库—>管理—>维护计划—>维护计划向导

2.输入维护计划 ​

  • 第二步：输入维护计划“名称”及“说明”，点击“更改”按钮

3.新建作业计划 ​

  • 第三步：在“新建作业计划”界面进行相关的设置

计划类型：重复执行;

​ 频率执行：每周、每天、每月（这里我选择每天进行备份）

​ 每天频率：时间自行选择;（这里我选择每天备份一次）

​ 持续时间：开始时间默认当前时间（这里建议改成凌晨或者数据开销相对较小的时间段），结束时间可以不填;

以上设置完成后点击“确定”

4.选择维护任务 ​

  • 第四步：点击下一步，“选择维护任务”，这里我选择“备份数据库完整”及“清楚维护任务”，定期清除备份的数据，避免磁盘空间被备份数据占满，造成数据宕机

5.选择维护任务顺序 ​

  • 第五步：“选择维护任务顺序”，这里是先备份后删除

6.备份的数据库 ​

  • 第六步：选择要备份的数据库，可以根据需要备份的数据库进行选择，可以选择备份到哪个位置。，最好放在磁盘空间比较充足的盘符，扩展名默认bak就可以

7.定义清除维护任务 ​

  • 第七步：设置“定义清除维护任务”，选择备份时所设置的文件夹，文件扩展名bak，文件保留时间，根据自己的需求选择，备份保留时间

8.保存地址 ​

第八步：选择报告选项的保存地址，自己根据需要选择

  • 第九步：点击“完成”设置，此处可以看到前面的设置情况

  • 第十步：设置成功

11.维护计划 ​

  • 第十一步：设置完成后，可以看到“维护计划”内和“作业”内会生成一个作业计划

12.修改计划 ​

第十二步：维护计划右键“修改”可查看计划任务，右键可以再次设置相关参数

13.执行此作业 ​

第十三步：作业右键“作业开始步骤”可直接执行此作业，验证作业是否生效

5.脚本备份 ​

https://www.xin3721.com/Articletsql/sql3795.html

6.数据文件迁移 ​

6.1已有数据文件迁移 ​

由于线上环境磁盘不足，需要迁移部分数据文件

对数据进行全备，完整全备

通过ssm工具

1.打开数据库，登录进去

2.选择数据库右键点击——>任务——>备份——>数据库

备份完之后，对数据库的路径进行确认，点击数据库右击——>属性，文件——>路径

2.选择需要迁移数据库，右键依次选择”任务”--> ”分离”

勾选“删除链接”——>-勾选“更新统计信息”

3.将数据库目录下所需数据文件，将数据库文件复制到D盘新建的数据库目录

4.数据库 右键 ”附加”，“添加”——>选择刚才剪切过去的数据库.mdf文件——>确定

“添加”——>选择前面选择的磁盘并且刚才剪切过去的数据库.mdf文件——>确定

确认：

完成后刷新就可以查看数据库文件位置变了，且权限，文件任何都完整的迁移过来

5.登陆

然后登录SQL SERVER,继续使用windwos身份验证，（因为用SA用户去附加数据库的时候，会让数据库成为只读状态，所以能用windwos身份验证就用本地账户，因为权限的问题）

  • 命令行
sql
--查看当前的存放位置
select database_id,name,physical_name AS CurrentLocation,state_desc,size
from sys.master_files
where database_id=db_id(N'数据库名');

--修改文件的存放位置下次启动生效
--testDb为数据库名，
alter database 数据库名 modify file ( name = 文件名(不包含后缀), filename = '文件存储路径');
alter database 数据库名 modify file ( name = 文件名(不包含后缀), filename = '文件存储路径');
eg.
alter database testDb modify file ( name = testDb, filename = 'G:\SQL_DATA\testDb\testDb.mdf');
alter database testDb modify file ( name = testDb_log, filename = 'G:\SQL_DATA\testDb\testdb_log.ldf');

--修改默认的数据库文件存放位置（即时生效）
EXEC xp_instance_regwrite
@rootkey='HKEY_LOCAL_MACHINE',
@key='Software\Microsoft\MSSQLServer\MSSQLServer',
@value_name='DefaultData',
@type=REG_SZ,
@value='E:\MSSQL_MDF\data'
GO


--修改默认的日志文件存放位置（即时生效）
EXEC master..xp_instance_regwrite
@rootkey='HKEY_LOCAL_MACHINE',
@key='Software\Microsoft\MSSQLServer\MSSQLServer',
@value_name='DefaultLog',
@type=REG_SZ,
@value='E:\MSSQL_MDF\log'
GO
--查看当前的存放位置
select database_id,name,physical_name AS CurrentLocation,state_desc,size
from sys.master_files
where database_id=db_id(N'数据库名');

--修改文件的存放位置下次启动生效
--testDb为数据库名，
alter database 数据库名 modify file ( name = 文件名(不包含后缀), filename = '文件存储路径');
alter database 数据库名 modify file ( name = 文件名(不包含后缀), filename = '文件存储路径');
eg.
alter database testDb modify file ( name = testDb, filename = 'G:\SQL_DATA\testDb\testDb.mdf');
alter database testDb modify file ( name = testDb_log, filename = 'G:\SQL_DATA\testDb\testdb_log.ldf');

--修改默认的数据库文件存放位置（即时生效）
EXEC xp_instance_regwrite
@rootkey='HKEY_LOCAL_MACHINE',
@key='Software\Microsoft\MSSQLServer\MSSQLServer',
@value_name='DefaultData',
@type=REG_SZ,
@value='E:\MSSQL_MDF\data'
GO


--修改默认的日志文件存放位置（即时生效）
EXEC master..xp_instance_regwrite
@rootkey='HKEY_LOCAL_MACHINE',
@key='Software\Microsoft\MSSQLServer\MSSQLServer',
@value_name='DefaultLog',
@type=REG_SZ,
@value='E:\MSSQL_MDF\log'
GO

6.2更改数据库默认存储路径 ​

1、打开数据库，登录进去，选择服务器 “属性”。

2、选择”数据库设置”，如图直接修改数据和日志的存储路径

这样新建数据库默认的存储路径就变为手动设置的路径了

7.ssm数据库还原 ​

7.1不同数据库名字还原 ​

eg：从test_new还原至test 步骤1：选中要还原的数据库，右击-》任务-》还原-》数据库

步骤2：选择设备-》添加备份介质

步骤3：选择目标数据test

back3

步骤4：点击左边选择页【文件】-》选中将所以文件重新定位到文件夹-》选择目标数据库的mdf和ldf文件，即test.mdf和test_log.ldf

步骤5：点击左边选择页【选项】-》取消已勾选的还原前进行结尾日志备份-》选中覆盖现有数据库

如下图，成功还原数据库test