SQL SERVER如何还原一个误删表数据

–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]