数据库备份与还原

最新推荐文章于 2025-08-10 13:24:43 发布

ai_li7758521

最新推荐文章于 2025-08-10 13:24:43 发布

阅读量772

点赞数

CC 4.0 BY-SA版权

文章标签：数据库 database disk insert table 脚本

本文链接：https://blog.youkuaiyun.com/ai_li7758521/article/details/5893999

本文提供了一套完整的SQL Server数据库备份及恢复方案，包括全备、差备、日志备份和尾日志备份等关键步骤，并附带了实用的T-SQL脚本。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

在完整恢复模式和大容量日志恢复模式下，还原数据和（可选）差异备份后，通常会还原后续日志备份（包括尾日志备份）以将数据库恢复到失败点。本文包括完整，差异和日志为日志备份，以及还原到最近事故点的脚本。

--EXEC sp_dropdevice MyBack EXEC sp_addumpdevice disk,MyBack,'D:/Test.bak' EXEC sp_addumpdevice disk,MyDiffBack,'D:/TestDiff.bak' EXEC sp_addumpdevice disk,MyLogBack,'D:/TestLog.bak' EXEC sp_addumpdevice disk,MyTailBack,'D:/TestLogTail.bak' USE MASTER BACKUP DATABASE Test To MyBack WITH FORMAT --------------------------------------------------------- --建表插入数据 USE Test CREATE TABLE T1(ID INT IDENTITY,NAME NVARCHAR(20),VALUE INT) INSERT T1 SELECT 'BEFORE DIFFBACK',20 UNION SELECT 'BEFORE DIFFBACK',30 --增量备份 USE MASTER BACKUP DATABASE Test TO MyDiffBack WITH DIFFERENTIAL GO ----------------------------------------------------------- -------------------------------------------------------- --插入数据 INSERT Test..T1 SELECT 'BEFORE LOGBACKUP',40 UNION SELECT 'BEFORE LOGBACKUP',50 USE MASTER BACKUP LOG Test TO MyLogBack GO -------------------------------------------------------- -------------------------------------------------------- INSERT Test..T1 SELECT 'AFTER LOGBACKUP',60 UNION SELECT 'AFTER LOGBACKUP',70 --备份尾日志 BACKUP LOG Test TO MyTailBack WITH NO_TRUNCATE GO ---------------------------------------------------------- ------------------------------------------------------ SELECT * FROM msdb..backupfile ------------------------------------------------------ 3. RESTORE DATABASE Test FROM MyBack WITH NORECOVERY,REPLACE GO RESTORE DATABASE Test FROM MyDiffBack WITH NORECOVERY GO RESTORE DATABASE Test FROM MyLogBack WITH NORECOVERY GO RESTORE DATABASE Test FROM MyTailBack WITH RECOVERY GO