How to Restore Table Data in SQL Server in 2020?
Ashwani Tiwari |
Published: October 8, 2020 | SQL Server
You can restore table data in SQL Server using these techniques-
1. SQL Server Management Studio Restoration Process
2. The LSN Method
3. A Professional Software
1. What causes this deletion
2. Methods to Recover
3. Key Takeaways
Thousands of Database Administrators, unfortunately, face accidental deletion of their database files stored in tables of SQL Server. In this article, you will learn about three different methods to recover your deleted table data.
But before we do that, I’ll tell you about the causes of this deletion, which are numerous, and then we will dig into them so that you don’t face such issues again. So, without any delay, let’s get straight to it…
What is the Cause of Deletion or Corruption of Table data?
Corruption or deletion of the table data in SQL Server can be caused due to a myriad of reasons, however, these are the most prominent ones-
1. Abrupt Power Loss- This can be caused due to sudden loss of computer power during which work was being done on the SQL Server. This will result in a corrupt database and even a whole deleted database.
2. Software Bugs- In some cases, the SQL Server program itself has internal bugs that interfere with your database files, and thus, cause corruption or deletion.
3. Data Migration- Database files and table records are especially vulnerable at the time of transfer from one server to another. Special care should be taken while transferring the data. Or better yet, it would be wise to use a migration tool to avoid any errors.
4. Malware & Viruses- In the 21st century, viruses are notorious for causing huge damages to organizations in terms of lost or stolen data. Database Administrators are no exception. Malware like the infamous Ransomeware is known to lock away important database files until a “ransom” is paid. Administrators should keep their machines protected.
So instead of trying to recover a deleted table in SQL Server, it will be far better to avoid these threats to your database files.
PRO TIP: Use SQL Server Recovery software to restore your table data right away in a hassle-free way.
Methods to Restore Table Data in SQL Server – Explained
The two methods to be discussed next are manual methods that can be implemented by the user. They, however, have a few drawbacks-
Both methods will not work if the backup file from which you intend to recover your files have been corrupted.
And in the case of the LSN method, the process itself is technically complicated. Also, it will take a long time to recover the data.
#1:Restore Table in SQL Server Management Studio Using Restoration Wizard-
You can recover deleted from the table in SQL Server using the Restoration wizard available in SQL Server Management Studio(SSMS). This will restore your whole database, along with database objects and tables, to a previous point of time; the time at which you took the backup. Follow these steps and recover deleted tables in SQL server.-
- Launch SSMS and click on the server name in the Object Explorer.
- Right-click on the Databases folder.
- Now, click on the Restore Database…
- Select the Device and select your backup.
- Set the destination database.
- Click on Ok to restore your database.
Following these steps will be able to restore table in SQL server management studio. Finally, refresh your database to view the restored database files.
#2:Recover Deleted Table Data Using LSN Keywords-
The second method to restore deleted table data in SQL Server is to use the LSN method. Follow these steps.:
- Check the number of rows using this query:
SELECT * FROM Table_name
- Take a log back using the below query:
BACKUP LOG [Databasename]
TO DISK = N’D:\Databasename\RDDTrLog.trn’
WITH NOFORMAT, NOINIT,
NAME = N’Databasename-Transaction Log Backup’,
SKIP, NOREWIND, NOUNLOAD, STATS = 10
- Collect the information about deleted records from the table:
Select [Current LSN] LSN], [Transaction ID], Operation, Context, AllocUnitName
WHERE Operation = ‘LOP_DELETE_ROWS’
- Find the deleted table record exact time of deletion using this command:
[Current LSN], Operation, [Transaction ID], [Begin Time], [Transaction Name], [Transaction SID]
[Transaction ID] = ‘000:000001f3′
[Operation] = ‘LOP_BEGIN_XACT’
- To start the restoration of deleted SQL Server data, run this query:
Recover Deleted D USE Databasename
RESTORE DATABASE Databasename_COPY FROM
DISK = ‘D:\Databasename\RDDFull.bak’
MOVE ‘Databasename’ TO ‘D:\RecoverDB\Databasename.mdf’,
MOVE ‘Databasename_log’ TO ‘D:\RecoverDB\Databasename_log.ldf’,
- Run this command to check if your files have been restored:
USE Databasename_Copy GO Select * from Table_name
And this is how you can restore your deleted SQL Server table data using the LSN commands.
#3: Use Enterprise Software to Recover Deleted Table in SQL Server-
You can avoid these complications and recover your deleted table data using professional software used by SQL professionals worldwide to recover their corrupt or deleted SQL Server files. Follow the steps below to restore your data right away-
- Install and launch the software.
- Click on Open to upload the MDF file.
- You can have a Preview of the table records after clicking on OK.
- Click on Export and set Export to/As as SQL Server Database.
- Enter the database details for authentication.
- Select Destination Database as Create New Database.
- Export your database with the only schema or with Schema and data as per your requirements.
Your table data and other database objects will be recovered shortly. The time taken will depend upon the size of the database files.
Corruption and deletion of important SQL Server database files is something that is faced by thousands of Administrators daily. Sometimes, this results in a deleted tables in.
You learned about three different approaches that you can take to recover your deleted table in SQL Server. The first is to use to Restoration Wizard that comes inbuilt with the SSMS. Follow the steps to restore the table in SQL Server management studio. The second method is to use LSN queries. And last but not the least, the third way is to use an enterprise-level tool that will automate the process for you.
I hope this article helped you learn how to restore table data in SQL Server which has been corrupted or deleted.