CREATE TABLE TruncatemyData
(id INT IDENTITY(1, 1),
[Name] VARCHAR(40)
回滚数据演示 (Rollback data demonstration)
At this point, we have a SQL database with two empty tables [DeletemyData] and [TruncatemyData]. It is a new database, and we do not have any database backup for it. Let’s take a full database backup using the following query. You can also use the backup wizard in SSMS to do it graphically. It is a small database, so no need to worry about backup compression.
此时，我们有了一个带有两个空表[DeletemyData]和[TruncatemyData]SQL数据库。 这是一个新数据库，我们没有任何数据库备份。 让我们使用以下查询进行完整的数据库备份。 您也可以使用SSMS中的备份向导以图形方式进行备份。 它是一个小型数据库，因此无需担心备份压缩。
Backup database SQLShackdemo to disk='c:\temp\SQLShackdemo.bak'
Execute the following query to retrieve database backup history from the msdb system database.
执行以下查询以从msdb系统数据库中检索数据库备份历史记录。
SELECT bs.database_name AS DatabaseName,
CASE bs.type
WHEN 'D'
THEN 'Full'
WHEN 'I'
THEN 'Differential'
WHEN 'L'
THEN 'Transaction Log'
END AS BackupType,
CAST(bs.first_lsn AS VARCHAR(50)) AS FirstLSN,
CAST(bs.last_lsn AS VARCHAR(50)) AS LastLSN,
bmf.physical_device_name AS PhysicalDeviceName,
CAST(CAST(bs.backup_size / 1000000 AS INT) AS VARCHAR(14)) + ' ' + 'MB' AS BackupSize,
bs.recovery_model AS RecoveryModel
FROM msdb.dbo.backupset AS bs
INNER JOIN msdb.dbo.backupmediafamily AS bmf ON bs.media_set_id = bmf.media_set_id
WHERE bs.database_name = 'SQLShackdemo'
ORDER BY backup_start_date DESC,
backup_finish_date;
It gives first and last log sequence number (LSN) details as well.
它还提供了第一个和最后一个日志序列号（LSN）详细信息。
BEGIN
INSERT INTO DeletemyData([Name])
VALUES('Delete Data' + ' ' + CAST((@ID) AS VARCHAR));
SET @ID = @ID - 1;
DECLARE @id INT;
SET @ID = 10;
WHILE(@ID > 0)
BEGIN
INSERT INTO TruncatemyData([Name])
VALUES('Truncate data' + ' ' + CAST((@ID) AS VARCHAR));
SET @ID = @ID - 1;
Now, open two query windows in SSMS.
现在，在SSMS中打开两个查询窗口。
In the first query window, delete a few records from [DeletemyData] table.
在第一个查询窗口中，从[DeletemyData]表中删除一些记录。
DELETE FROM
WHERE id < 5;
In the second query window, truncate the SQL table. We cannot specify the WHERE clause in truncate, so it removes all records from a table.
在第二个查询窗口中，截断SQL表。 我们无法在截断中指定WHERE子句，因此它将从表中删除所有记录。
Truncate table TruncatemyData
Verify records in both the tables. We have zero records in the [TruncatemyData] table while [DeletemyData] contains six records.
验证两个表中的记录。 [TruncatemyData]表中有零条记录，而[DeletemyData]包含六条记录。
We can use undocumented function fn_dblog to get information about delete and truncate statements from the transaction log. Refer to this article, How to continuously read Transaction log file data directly in a SQL Server database with fn_dblog and fn_dump_dblog for more detail.
我们可以使用未记录的函数fn_dblog从事务日志中获取有关delete和truncate语句的信息。 请参阅本文， 如何使用fn_dblog和fn_dump_dblog在SQL Server数据库中直接直接连续读取事务日志文件数据，以获取更多详细信息。
We can filter transaction log entry using the delete and truncate table clause in the where condition.
我们可以使用where条件中的delete和truncate table子句来过滤事务日志条目。
USE SQLShackDemo;
SELECT [Current LSN],
[transaction ID] tranID,
[begin time],
Description,
operation,
Context
FROM ::fn_dbLog(NULL, NULL)
WHERE [Transaction Name] IN('Delete', 'Truncate table');
It shows two transaction log records. We can segregate transactions using the description column. As per the following screenshot, the first entry is for delete while later entry is for the truncate statement. You can note down the begin time of these transaction.
它显示两个事务日志记录。 我们可以使用描述列来隔离交易。 根据以下屏幕截图，第一个条目用于删除，而后面的条目用于truncate语句。 您可以记下这些事务的开始时间。
In the full recovery model, transaction log backup maintains the log chain. We can also do point in time recovery using transaction log backup. Let’s execute the following query for log backup. It takes backups of all data changes.
在完全恢复模型中，事务日志备份维护日志链。 我们还可以使用事务日志备份来进行时间点恢复。 让我们执行以下查询进行日志备份。 它备份所有数据更改。
Backup log SQLShackdemo to disk='c:\temp\SQLShackdemo_log.trn'
View database backup history using the above query from the msdb database. It shows two entries – full and transaction log backup.
使用上述查询，从msdb数据库查看数据库备份历史记录。 它显示两个条目–完整和事务日志备份。
恢复从SQL Delete语句删除的数据 (Recover data deleted from a SQL Delete statement)
Now, suppose you require to recover the deleted data from the existing backups. We will create a new database from the full backup. We need to restore a backup in NORECOVERY mode so that we can apply further transaction log backup on it.
现在，假设您需要从现有备份中恢复已删除的数据。 我们将从完整备份中创建一个新数据库。 我们需要以NORECOVERY模式还原备份，以便我们可以在其上应用进一步的事务日志备份。
USE [master];
RESTORE DATABASE [SQLShackDemo_restore] FROM DISK = N'C:\TEMP\SQLShackdemo.bak' WITH FILE = 1,
MOVE N'SQLShackDemo' TO N'C:\sqlshack\Demo\SQLShackDemo.mdf',
MOVE N'SQLShackDemo_log' TO N'C:\sqlshack\Demo\SQLShackDemo_log.ldf',
NORECOVERY, NOUNLOAD, STATS = 5;
Database [SQLShackDemo_restore] is in restoring mode. We cannot access the database while it is in restoring mode.
数据库[SQLShackDemo_restore]处于还原模式。 在恢复模式下，我们无法访问数据库。
In the article, we learned about Point in Time Recovery with SQL Server using the STOPAT parameter of Restore log command. We can specify a specific timestamp or LSN in the STOPAT parameter.
在本文中，我们使用Restore log命令的STOPAT参数了解了SQL Server的时间点恢复 。 我们可以在STOPAT参数中指定特定的时间戳或LSN。
Similarly, we can use STOPBEFOREMARK in a restore log statement. As its name suggests, this parameter instructs SQL Server to stop database restore once it reaches a specific timestamp or LSN. You can refer to Microsoft docs for more details on STOPBEFOREMARK.
同样，我们可以在还原日志语句中使用STOPBEFOREMARK。 顾名思义，此参数指示SQL Server在达到特定的时间戳或LSN后停止数据库还原。 您可以参考Microsoft文档以获取有关STOPBEFOREMARK的更多详细信息。
以十进制格式转换HEX LSN值 (Convert HEX LSN value in decimal format)
In the output of fn_dblog above, we have LSN for delete and truncate statements.
在上面的fn_dblog的输出中，我们具有用于删除和截断语句的LSN。
- Delete LSN: 00000026:00000230:0001 删除LSN ：00000026：00000230：0001
- Truncate LSN: 00000026:00000268:0001 截断LSN： 00000026：00000268：0001
LSN values in fn_dblog are in the hexadecimal format. Restore log command requires LSN in a decimal format. We can use the following query to convert it into the decimal format. Here, specify the LSN in the @LSN parameter.
fn_dblog中的LSN值采用十六进制格式。 Restore log命令要求LSN为十进制格式。 我们可以使用以下查询将其转换为十进制格式。 在这里，在@LSN参数中指定LSN。
DECLARE @LSN VARCHAR(22), @LSN1 VARCHAR(11), @LSN2 VARCHAR(10), @LSN3 VARCHAR(5);
SET @LSN = '00000026:00000230:0001';
SET @LSN1 = LEFT(@LSN, 8);
SET @LSN2 = SUBSTRING(@LSN, 10, 8);
SET @LSN3 = RIGHT(@LSN, 4);
SET @LSN1 = CAST(CONVERT(VARBINARY, '0x' + RIGHT(REPLICATE('0', 8) + @LSN1, 8), 1) AS INT);
SET @LSN2 = CAST(CONVERT(VARBINARY, '0x' + RIGHT(REPLICATE('0', 8) + @LSN2, 8), 1) AS INT);
SET @LSN3 = CAST(CONVERT(VARBINARY, '0x' + RIGHT(REPLICATE('0', 8) + @LSN3, 8), 1) AS INT);
SELECT CAST(@LSN1 AS VARCHAR(8)) + CAST(RIGHT(REPLICATE('0', 10) + @LSN2, 10) AS VARCHAR(10)) + CAST(RIGHT(REPLICATE('0', 5) + @LSN3, 5) AS VARCHAR(5));
Using the above query, we get the following LSN values for both delete and truncate statements.
使用上面的查询，我们为delete和truncate语句获得以下LSN值。
- Delete LSN: 38000000056000001 删除LSN ：38000000056000001
- Truncate LSN: 38000000061600001 截断LSN： 38000000061600001
Now, run the restore log query using the STOPBEFORMARK parameter. This query stops the processing of database restores before the specified LSN.
现在，使用STOPBEFORMARK参数运行还原日志查询。 此查询在指定的LSN之前停止数据库还原的处理。
Restore log [SQLShackDemo_restore] FROM DISK = N'C:\TEMP\SQLShackdemo_log.trn'
with STOPBEFOREMARK ='lsn:38000000056000001'
We get the following output of above RESTORE LOG command.
我们得到以上RESTORE LOG命令的以下输出。
Once the log backup is restored, we can access the database. Verify the records in the [DeletemyData] table, and it shows data is available. We can use this data and export to an original database using export and import wizard.
恢复日志备份后，我们就可以访问数据库了。 验证[DeletemyData]表中的记录，并显示数据可用。 我们可以使用此数据，并使用导出和导入向导将其导出到原始数据库。
恢复从SQL Truncate语句中删除的数据 (Recover data deleted from a SQL Truncate statement)
We have recovered data deleted by a delete statement. Let’s perform a similar test for recovering data from the truncate statement.
我们已经恢复了由delete语句删除的数据。 让我们执行类似的测试以从truncate语句中恢复数据。
Restore full database backup in NORECOVERY mode
以NORECOVERY模式还原完整的数据库备份
USE [master];
RESTORE DATABASE [SQLShackDemo_restore_1] FROM DISK = N'C:\TEMP\SQLShackdemo.bak' WITH FILE = 1,
MOVE N'SQLShackDemo' TO N'C:\sqlshack\Demo\SQLShackDemo.mdf',
MOVE N'SQLShackDemo_log' TO N'C:\sqlshack\Demo\SQLShackDemo_log.ldf',
NORECOVERY, NOUNLOAD, STATS = 5;
Restore transaction log backup with the STOPBEFOREMARK parameter. Specify the LSN we derived above from the hex.
使用STOPBEFOREMARK参数还原事务日志备份。 指定我们从十六进制导出的LSN。
Restore log [SQLShackDemo_restore_1] FROM DISK = N'C:\TEMP\SQLShackdemo_log.trn'
with STOPBEFOREMARK ='lsn:38000000061600001'
Verify data in the [TruncatemyData] table
验证[TruncatemyData]表中的数据
结论 (Conclusion)
In this article, we recovered deleted data using SQL Delete and SQL Truncate statements with the help of database backups. You should not perform any tests in the production database. You can create a test environment and explore data recovery.
在本文中，我们在数据库备份的帮助下使用SQL Delete和SQL Truncate语句恢复了已删除的数据。 您不应在生产数据库中执行任何测试。 您可以创建一个测试环境并探索数据恢复。
翻译自: https://www.sqlshack.com/how-to-use-database-backups-to-recover-data-after-sql-delete-and-sql-truncate-statements/
sql truncate
sql truncate This article explores the recovery of data removed by SQL Delete and SQL Truncate statements using SQL database backups. 本文探讨了使用SQL数据库备份恢复由SQL Delete和SQL Truncate语句删除的数据的方法。 ...
–1 use master backup database logTest to disk=’D:\\Program Files\\Microsoft SQL Server\\MSSQL10.MSSQLSERVER\\MSSQL\\Backup\\logTest.bak’ with format –2 use logTest create table table_1(Id int, test varchar(max)) insert table_1(Id,test) values (1,’3333′); insert table_1(Id,test) values (2,’4333′); in
truncate恢复表，在11.2.0.4文件系统和19c asm上测试通过
参考：http://www.hellodba.com/reader.php?ID=217&lang=CN
https://blog.csdn.net/ElanJie/article/details/89316570
代码下载：
http://www.hellodba.com/Download/FY_Rec...
sql truncate
This article gives you an insight into the SQL Truncate and SQL Delete commands behavior.
本文使您深入了解SQL截断和SQL删除命令的行为。
介绍 (Introduction)
In the article Difference between SQL Trun...
DELETE和TRUNCATE都是数据库中用于删除数据的命令，但它们的实现方式和效果不同。
DELETE命令用于删除表中的一行或多行数据，可以带有WHERE子句指定删除的条件。DELETE命令执行后，被删除的行将被保存在事务日志中，可以通过ROLLBACK命令进行回滚操作。DELETE命令执行速度较慢，因为它需要逐行扫描表并删除符合条件的行。
TRUNCATE命令用于删除表中的所有数据，不带有WHERE子句。TRUNCATE命令执行后，表的结构和索引都会被保留，但所有数据都被清空。TRUNCATE命令执行速度较快，因为它不需要逐行扫描表，而是直接删除表中的所有数据。
总的来说，如果只需要删除表中的部分数据，可以使用DELETE命令；如果需要删除表中的所有数据，可以使用TRUNCATE命令。但需要注意的是，TRUNCATE命令执行后无法回滚，因此在执行前需要谨慎考虑。
