Sometimes, the data gets accidentally deleted, truncated, or dropped from the SQL database. Also, it may happen that you do not know the time and date when the particular data was deleted, truncated, or dropped from the database. So, the question is how to recover such data. Below, we’ll look into the ways to recover deleted, truncated, or dropped data. But before that, let’s understand the DELETE, TRUNCATE, and DROP operations.
The DELETE Operation
DELETE is a common command used to delete data in SQL Server. Let’s say, there is a table with the top scorers in the football league (soccer).
CREATE TABLE TopScorers (
ID INT IDENTITY(1,1) PRIMARY KEY,
FirstName NVARCHAR(50),
LastName NVARCHAR(50),
Age INT,
Goals INT
);
Also, some data is inserted in the table.
INSERT INTO TopScorers (FirstName, LastName, Age, Goals)
VALUES
('Cristiano', 'Ronaldo', 36, 674),
('Lionel', 'Messi', 34, 672),
('Romario', 'Faria', 56, 772),
('Pele', 'Arantes', 81, 767),
('Gerd', 'Muller', 75, 735);
The DELETE operation can delete one row or multiple rows. In the below example, we will delete a single row with the first name equal to Cristiano.
DELETE FROM TopScorers
WHERE FirstName = 'Cristiano';
The TRUNCATE Operation
TRUNCATE is another operation that can delete the data. This operation truncates all the data. However, it does not store the individual records in the transaction log. It cannot be used with the WHERE clause. It is faster to remove all the rows in a table.
The following example shows how to remove all the rows of a table using the TRUNCATE statement.
截断表最佳射手
The DROP Operation
The DROP operation can delete an entire object. The DELETE and TRUNCATE commands delete the data whereas the DROP operation can delete a table, a view, a function, or other SQL Server objects.
The following example shows how to drop the table TopScorers.
DROP table TopScorers
Ways to Recover SQL Server Data after DELETE, TRUNCATE, and DROP Operations
We have mentioned below different ways to recover data after DELETE, TRUNCATE, and DROP operations.
1. Recover SQL Server Data using Backup
If your data was truncated, deleted, or dropped, the first option is to recover the data using a backup. Let’s say that you already have a backup before the DELETE, TRUNCATE, or DROP operations. To recover the data, you need to use theRESTOREcommand (see below):
RESTORE DATABASE stellar FROM DISK = 'c:\backup\stellar.bak'
Alternatively, you can use the SQL Server Management Studio (SSMS) to recover the backup. For this, open the SSMS and go to the Object Explorer. In the Databases folder, right-click and select theRestore Databaseoption.
In theRestore Databasewindow, select theDeviceoption and then select the backup created previously. Select the Backup sets to restore and pressOK.
- Recover SQL Server Data using Snapshot
Snapshot is like a picture of the database and can be used to restore the data. However, it is read-only. If you need to recover a few rows or objects from the database, this process can be faster because you don’t need to restore the entire database.
In this example, we will use the TopScorers table with 5 rows (created above).
To create the snapshot, we will use the following T-SQL commands.
CREATE DATABASE stellar_dbss ON (
NAME =stellar,
FILENAME = 'C:\backup\stellar_db.ss'
) AS SNAPSHOT OF stellar;
GO
Here, stellar_dbss is the snapshot name of the database. The file name contains the path and database file.
Note:This database does not contain transaction logs because it is read-only.
Finally, we select the database used for the snapshot.
If everything is fine, the snapshot database will be created.
Recover Data after DELETE Operations using Snapshot
Let’s say that we accidentally deleted some data from the original database (created above).
DELETE FROM TopScorers
WHERE FirstName = 'Cristiano';
We can use the following commands to restore the deleted data from the database snapshot to the original database.
INSERT INTO TopScorers (FirstName, LastName, Age, Goals)
SELECT
FirstName, LastName, Age, Goals
FROM [stellar_dbss].dbo.TopScorers
WHERE FirstName = 'Cristiano';
The above code will insert the data from the snapshot database table into the original table and recover the deleted row.
Recover Data after TRUNCATE Operations using Snapshot
Let’s say that we truncated some data from the original database.
截断表最佳射手
We can use the following commands to restore data from the database snapshot into the original database.
INSERT INTO TopScorers (FirstName, LastName, Age, Goals)
SELECT
FirstName, LastName, Age, Goals
FROM [stellar_dbss].dbo.TopScorers
Recover Data after DROP Operations using Snapshot
Let’s say, a table is dropped from the original database.
DROP table TopScorers
To restore the dropped table, in the SSMS, go to the table in the snapshot database, right-click the table that you want to recover, and selectScript table as > CREATE To > New Query Window.
Alternatively, you can use the below code:
/****** Object: Table [dbo].[TopScorers] Script Date: 10/05/2023 11:25:23 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[TopScorers](
[ID] [int] IDENTITY(1,1) NOT NULL,
[FirstName] [nvarchar](50) NULL,
[LastName] [nvarchar](50) NULL,
[Age] [int] NULL,
[Goals] [int] NULL,
PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
You can copy this code and execute it in the database where you want to recreate the table. You will have an empty table with no data. In order to load the data from the snapshot to the database, use these commands:
INSERT INTO TopScorers (FirstName, LastName, Age, Goals)
SELECT
FirstName, LastName, Age, Goals
FROM [stellar_dbss].dbo.TopScorers
3. Use a Third-Party SQL Recovery Software
In case you do not have a backup or a snapshot, you can use a third-party SQL recovery tool, such as的修复,MS SQL. This software has the option torecover deleted table records from the SQL Server database.
Before using the software, set the database offline and make a copy of it.
USE [master]
GO
ALTER DATABASE stellar SET OFFLINE
GO
Now, launch Stellar Repair for MS SQL, click the Browse button, and select the copy of the database file.
To recover the data, check theInclude Deleted Recordsoption and press theRepairbutton.
Finally, you can save the data in a new database or the current database, or export it to other formats, like CSV, HTML, and Excel.
Conclusion
Above, we have discussed the delete, truncate and drop operations in detail. We have also learned how to recover the data after these operations using a backup or a snapshot. Finally, we have mentioned a SQL recovery tool, named的修复,MS SQLthat can help recover deleted records from the database. This software is also useful when your SQL Server database gets corrupted.
