说明： 此次模拟删除某些数据表 ， 尝试修复

前提条件：删表前有个历史完整备份、完整备份后的事务日志完整

 一、准备初始数据

1.建表插入数据

-- 创建表和插入数据
CREATE TABLE Students (
    StudentID INT PRIMARY KEY,
    Name NVARCHAR(50) NOT NULL,
    Age INT CHECK (Age >= 0),
    RegisterDate DATETIME DEFAULT GETDATE()
);
-- 插入数据
INSERT INTO Students (StudentID, Name, Age, RegisterDate)
VALUES 
    (3, '王五', 21, '2023-10-02 10:30:00'),
    (4, '赵六', 19, '2023-10-03 14:15:00'),
    (5, '陈七', 23, DEFAULT);  -- 使用默认值填充 RegisterDate
-- 查询表
select * from Students ;

3    王五    21    2023-10-02 10:30:00.000
4    赵六    19    2023-10-03 14:15:00.000
5    陈七    23    2025-03-06 15:40:27.967
View Code

2.完备数据库

--完整备份
BACKUP DATABASE [db_test] TO DISK = 'C:\bak\dbtest1545.bak';

 3.建表插入数据（15/48分）

-- 建表插入数据
CREATE TABLE Studentsnew (
    StudentID INT PRIMARY KEY,
    Name NVARCHAR(50) NOT NULL,
    Age INT CHECK (Age >= 0),
    RegisterDate DATETIME DEFAULT GETDATE()
);
-- 插入数据
INSERT INTO Studentsnew (StudentID, Name, Age, RegisterDate)
VALUES 
    (3, '王五', 21, '2023-10-02 10:30:00'),
    (4, '赵六', 19, '2023-10-03 14:15:00'),
    (5, '陈七', 23, DEFAULT);  -- 使用默认值填充 RegisterDate
-- 查询表
select * from Studentsnew
View Code

4.删表（15/51分）

drop table Studentsnew;
drop table Students;

5.建表插入数据（15/53分）

-- 建表插入数据
CREATE TABLE Studentsnew2 (
    StudentID INT PRIMARY KEY,
    Name NVARCHAR(50) NOT NULL,
    Age INT CHECK (Age >= 0),
    RegisterDate DATETIME DEFAULT GETDATE()
);
-- 插入数据
INSERT INTO Studentsnew2 (StudentID, Name, Age, RegisterDate)
VALUES 
    (3, '王五', 21, '2023-10-02 10:30:00'),
    (4, '赵六', 19, '2023-10-03 14:15:00'),
    (5, '陈七', 23, DEFAULT);  -- 使用默认值填充 RegisterDate
-- 查询表
select * from Studentsnew2
View Code

 

二、表及数据恢复

1.停止对数据库增删改动作;设置为单用户模式

USE master;
GO
-- 设置数据库为单用户模式（强制断开现有连接）
ALTER DATABASE [db_test] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
GO

 

2.对数据库做事务备份(15/58)

通过t-sql命令备份

要求：必须将数据库设置为 完整恢复模式 或 大容量日志恢复模式 才能备份事务日志

-- 检查恢复模式
SELECT name, recovery_model_desc FROM sys.databases where name='db_test';
-- 修改为完整恢复模式
ALTER DATABASE [db_test] SET RECOVERY FULL;
-- 事务日志备份
BACKUP LOG [db_test] TO DISK = 'C:\bak\dbtest1558.trn' WITH COMPRESSION, CHECKSUM;

 

 可以通过SSMS备份

 

3.对数据库做完备

同上

4.通过删表前最近一次备份还原

 

--还原
 RESTORE DATABASE [db_test] FROM DISK = 'C:\bak\dbtest1545.bak'WITH REPLACE;

 

 

5.确认表

说明：发现Students表恢复了，Studentsnew表不在，符合预期

-- 查询所有用户表（排除系统表）
SELECT name AS TableName
FROM sys.tables
WHERE is_ms_shipped = 0
View Code

 

 

5.通过审计日志还原

说明： 需要知道删表时间

A.恢复到时间15/50，预期看到Studentsnew，Students

 

 

B.恢复到时间15/52 ，预期看不到Studentsnew，Students

 

C.恢复到时间15/54，预期看到Studentsnew2

 

补充：

1.可以直接用SSMS自带备份恢复操作

2.可以使用第三方工具ApexSQLLOG（经过实战尝试过， 目前最新支持到数据库2019）

3.演示完整命令

SELECT request_session_id 
FROM sys.dm_tran_locks 
WHERE resource_database_id = DB_ID('db_test');

kill 56

SELECT 
    database_name,
    backup_start_date,
    backup_finish_date,
    physical_device_name
FROM 
    msdb.dbo.backupset AS bs
INNER JOIN 
    msdb.dbo.backupmediafamily AS bmf 
ON 
    bs.media_set_id = bmf.media_set_id
ORDER BY 
    backup_finish_date DESC;

-- 创建表和插入数据
CREATE TABLE Students (
    StudentID INT PRIMARY KEY,
    Name NVARCHAR(50) NOT NULL,
    Age INT CHECK (Age >= 0),
    RegisterDate DATETIME DEFAULT GETDATE()
);
-- 插入数据
INSERT INTO Students (StudentID, Name, Age, RegisterDate)
VALUES 
    (3, '王五', 21, '2023-10-02 10:30:00'),
    (4, '赵六', 19, '2023-10-03 14:15:00'),
    (5, '陈七', 23, DEFAULT);  -- 使用默认值填充 RegisterDate
-- 查询表
select * from Students ;

--完整备份
BACKUP DATABASE [db_test] TO DISK = 'C:\bak\dbtest1545.bak'; 

-- 建表插入数据
CREATE TABLE Studentsnew (
    StudentID INT PRIMARY KEY,
    Name NVARCHAR(50) NOT NULL,
    Age INT CHECK (Age >= 0),
    RegisterDate DATETIME DEFAULT GETDATE()
);
-- 插入数据
INSERT INTO Studentsnew (StudentID, Name, Age, RegisterDate)
VALUES 
    (3, '王五', 21, '2023-10-02 10:30:00'),
    (4, '赵六', 19, '2023-10-03 14:15:00'),
    (5, '陈七', 23, DEFAULT);  -- 使用默认值填充 RegisterDate
-- 查询表
select * from Studentsnew

-- 
drop table Studentsnew;
drop table Students;

-- 建表插入数据
CREATE TABLE Studentsnew2 (
    StudentID INT PRIMARY KEY,
    Name NVARCHAR(50) NOT NULL,
    Age INT CHECK (Age >= 0),
    RegisterDate DATETIME DEFAULT GETDATE()
);
-- 插入数据
INSERT INTO Studentsnew2 (StudentID, Name, Age, RegisterDate)
VALUES 
    (3, '王五', 21, '2023-10-02 10:30:00'),
    (4, '赵六', 19, '2023-10-03 14:15:00'),
    (5, '陈七', 23, DEFAULT);  -- 使用默认值填充 RegisterDate
-- 查询表
select * from Studentsnew2

USE master;
GO

-- 设置数据库为单用户模式（强制断开现有连接）
ALTER DATABASE [db_test] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
GO

-- 检查恢复模式 
SELECT name, recovery_model_desc FROM sys.databases where name='db_test'; 
-- 修改为完整恢复模式 
ALTER DATABASE [db_test] SET RECOVERY FULL;
-- 事务日志备份 
BACKUP LOG [db_test] TO DISK = 'C:\bak\dbtest1558.trn' WITH COMPRESSION, CHECKSUM;  --WITH NORECOVERY 



-- 查询所有用户表（排除系统表）
USE db_test
SELECT name AS TableName
FROM sys.tables
WHERE is_ms_shipped = 0;
select  * from Students;

--还原
USE master
RESTORE DATABASE [db_test] FROM DISK = 'C:\bak\dbtest1545.bak' WITH REPLACE, NORECOVERY;
RESTORE LOG [db_test]
FROM DISK = 'C:\bak\dbtest1558.trn'
WITH RECOVERY, STOPAT = '2025-03-06 15:54:00'; -- 指定恢复时间点
-- 查询所有用户表（排除系统表）
USE db_test
SELECT name AS TableName
FROM sys.tables
WHERE is_ms_shipped = 0;
select  * from Students;

-- 恢复为多用户模式
USE master
ALTER DATABASE [db_test] SET MULTI_USER;
GO
View Code

 4.备份还原不是同位置同名数据库

---在还原数据库前，需要知道备份文件中记录的逻辑文件名（可能与物理文件名不同）：
RESTORE FILELISTONLY 
FROM DISK ='C:\medPro_jufeng_prod_backup_2025_03_26_233001_1959260\medPro_jufeng_prod_backup_2025_03_26_233001_1959260.bak'


--- 使用 WITH MOVE 重定向文件路径
 RESTORE DATABASE medpro_jufeng0327
FROM DISK = 'C:\medPro_jufeng_prod_backup_2025_03_26_233001_1959260\medPro_jufeng_prod_backup_2025_03_26_233001_1959260.bak'
WITH 
MOVE 'medPro_jufeng_test2' TO 'C:\Program Files\Microsoft SQL Server\MSSQL16.MSSQLSERVER\MSSQL\DATA\medpro_jufeng0327.mdf',
MOVE 'medPro_jufeng_test2_log' TO 'C:\Program Files\Microsoft SQL Server\MSSQL16.MSSQLSERVER\MSSQL\DATA\medpro_jufeng0327_log.ldf',
REPLACE; -- 如果存在同名数据库，使用 REPLACE 覆盖


--确认还原结果
USE master;
SELECT name, state_desc FROM sys.databases 
WHERE name = 'medpro_jufeng0327';
View Code