SQLSERVER saves data deleted by mistake at a certain time point

SQLSERVER to save the deleted data blog. csdn. netdba_huangzjarticledetails8491327 at a certain time point, you need to have a complete database backup before that time point. In addition, your database recovery mode is required to be: full recovery mode

SQLSERVER to save the data http://blog.csdn.net/dba_huangzj/article/details/8491327 that was mistakenly deleted at a certain point in time to save the data that was mistakenly deleted at a certain point in time, you need to have a full data database backup before that point in time. In addition, your database recovery mode is required to be: full recovery mode

SQLSERVER Save A TimePoint mistaken DeleteOf Data

Http://blog.csdn.net/dba_huangzj/article/details/8491327

YesSaveATimePoint mistakenDeleteOfDataYou needTimeThere is a completeDataDatabase Backup.

And you needDataDatabase recovery mode: "full recovery mode"

How to view yourDataThe database recovery mode is full recovery mode. Run the following SQL

1 SELECT  recovery_model, recovery_model_desc2 FROM    sys.databases3 WHERE   name = 'GPOSDB'

After creating a newDataIt is strongly recommended to do this once after the libraryDataFull database backup

-------------------------------------------------------------------Gorgeous split line-----------------------------------------------------------------------

Create a test table first

1/* 2 since tempdb is always in simple recovery mode, it is not suitable for case studies. 3. UseDataLibrary GPOSDB 4 */5 USE [GPOSDB] 6 GO 7 IF OBJECT_ID ('testrestore') is not null 8 drop table testRestore 9 GO10 create table testRestore11 (12 id int identity (1, 1), 13 name varchar (50) 14 );

Insert TestData

1 -- insert TestData: 2 insert into testRestore (Name) 3 SELECT 'test1' 4 union all 5 SELECT 'test2' 6 union all 7 SELECT 'test3' 8 union all 9 SELECT 'test4' 10 union all 11 SELECT 'test5' 12 UNION ALL 13 SELECT 'test6' 14 union all 15 SELECT 'test7' 16 union all 17 SELECT 'test8'

1 SELECT * FROM testRestore

FirstDataFull database backup, complete backupDataLibrary

1 BACKUP DATABASE [GPOSDB]2 TO DISK='D:\GPOSDB_FULLBACKUP2013-07-23.bak'3 WITH INIT4 go

SimulationDeleteTableData, Used hereWaitfor timeTo enableDeleteDataOfTimeTo be accurate, select.DeleteDataFacilitate subsequent restorationATimeBeforeData

1 USE [GPOSDB]2 GO3 WAITFOR TIME '22:34'4 DELETE FROM dbo.testRestore

QueryDataWhetherDelete

1 USE [GPOSDB]2 GO3 SELECT * FROM dbo.testRestore

Disconnect from GPOSDBDataDatabase connection. If there is still a connection, kill the connection, and then back up the transaction log, and back up the transaction log.

1 --DeleteInDataIt will take at least three minutes to execute the following SQL statement. Otherwise, an error will be reported when the transaction log is restored. 2 -- all and GPOSDB must be disconnected.DataDatabase Connection 3 USE master 4 GO 5 backup log [GPOSDB] to disk = N 'd: \ GPOSDB_LOGBACKUP2013-07-23.bak 'WITH NO_TRUNCATE, NOFORMAT, NAME = N 'gposdb-transaction log backup ', SKIP, NORECOVERY, STATS = 10, CHECKSUM 6 GO 7 DECLARE @ backupSetId as int 8 SELECT @ backupSetId = position 9 FROM msdb .. backupset10 WHERE database_name = N 'gposdb' AND backup_set_id = (SELECT11 MAX (backup_set_id) 12 FROM msdb .. backupset13 WHERE database_name = N 'gposdb' 14) 15 IF @ backupSetId is null -- check whether the transaction log backup IS successful. IF it IS not disconnected from GPOSDBDataDatabase Connection backup transaction log may fail 16 BEGIN17 RAISERROR (n' Verification Failed. Not foundDataThe backup information of the library "GPOSDB. ', 16, 1) 18 END19 restore verifyonly from disk = n'd: \ GPOSDB_LOGBACKUP2013-07-23.bak' with file = @ backupSetId20 GO

1 -- query whether the backup is successful 2 select top 10 * FROM msdb .. backupset order by [backup_set_id] DESC

RestoreDataFull database backup

1 --DataDATABASE in restoring State 2 restore database [GPOSDB] from disk = 'd: \ GPOSDB_FULLBACKUP2013-07-23.bak 'WITH replace, NORECOVERY

Restore transaction logs

1 -- becauseDeleteDataYes in '22: 34' so RESTORE to restore log [GPOSDB] from disk = 'd: \ GPOSDB_LOGBACKUP2013-07-23.bak 'with recovery, STOPAT = '2017-7-23'

If transaction log restoration fails, you can use the GUI

ViewData

1 USE [GPOSDB]2 GO3 SELECT * FROM dbo.testRestore

--------------------------------------------------------------- Gorgeous split line --------------------------------------------

Note::

(1) If you find that many people have performed operations after the misoperation, after the restoration is successful, other people's operations will be rushed out, so after the misoperation, immediately stop othersDataDatabase Operations.

(2) This method must beDataDatabase exclusive