10 SQL Server如何从备份中还原一个被误删的表?

发布于 2020-08-07 12:02:41

请问SQL Server数据库误删了一个表,如何从备份文件中单独还原这个表,如果整体还原代价太大了!

查看更多

关注者
0
被浏览
194
admin
admin 2020-08-07
韬光养晦,厚积薄发!

--1先做数据库完整备份还原,还原到临时库:

USE [master]
RESTORE DATABASE [hotel_bak] FROM DISK = N'D:\BackupDB\Hotel\Hotel_2018_04_21_0213.bak' 
WITH FILE = 1, MOVE N'Hotel' TO N'E:\Database2\hotel_bak.mdf', MOVE N'Hotel_log' TO N'E:\Database2\hotel_bak_log.ldf', 
NORECOVERY, NOUNLOAD, REPLACE, STATS = 5
GO

--2还原数据库事务日志,按顺序逐一应用:

RESTORE LOG [hotel_bak] FROM DISK = N'E:\BackupLog\Hotel\Hotel_2018_04_22_0021.trn' 
WITH FILE = 1, NORECOVERY, NOUNLOAD, STATS = 10
GO
RESTORE LOG [hotel_bak] FROM DISK = N'E:\BackupLog\Hotel\Hotel_2018_04_23_0021.trn' 
WITH FILE = 1, NORECOVERY, NOUNLOAD, STATS = 10
GO
RESTORE LOG [hotel_bak] FROM DISK = N'E:\BackupLog\Hotel\Hotel_2018_04_24_0021.trn' 
WITH FILE = 1, NORECOVERY, NOUNLOAD, STATS = 10
GO
RESTORE LOG [hotel_bak] FROM DISK = N'E:\BackupLog\Hotel\Hotel_2018_04_25_0021.trn' 
WITH FILE = 1, NORECOVERY, NOUNLOAD, STATS = 10
GO

--记得最后一次日志应用选择RECOVERY,代表还原结束:

RESTORE LOG [hotel_bak] FROM DISK = N'E:\BackupLog\Hotel\Hotel_2018_04_26_0021.trn' 
WITH FILE = 1, RECOVERY, NOUNLOAD, STATS = 10
GO

--3最后提取数据到正式表:

insert into [hotel].[dbo].[NightlyRate]
SELECT [hotelId]
,[roomTypeId]
,[ratePlanId]
,[date]
,[cost]
,[status]
,[instantConfirmation]
,[instantConfirmCount]
,[availableRooms]
,[salePrice]
,[marketPrice]
,[ClientID] 
FROM [hotel_bak].[dbo].[NightlyRate]
1 个回答

撰写答案

请登录后再发布答案,点击登录

发布
问题

分享
好友

手机
浏览

扫码手机浏览