{"id":20678,"date":"2018-06-27T11:02:27","date_gmt":"2018-06-27T11:02:27","guid":{"rendered":"https:\/\/www.stellarinfo.com\/blog\/?p=20678"},"modified":"2024-11-05T06:34:54","modified_gmt":"2024-11-05T06:34:54","slug":"recover-deleted-records-in-sql-server","status":"publish","type":"post","link":"https:\/\/www.stellarinfo.com\/blog\/recover-deleted-records-in-sql-server\/","title":{"rendered":"How to Recover Deleted Table Records in SQL Server?"},"content":{"rendered":"<?xml encoding=\"utf-8\" ?><?xml encoding=\"utf-8\" ?><p>In SQL Server, you can use the DELETE statement with WHERE clause to delete specific records in a table. Sometimes, while executing the command, you fail to mention the WHERE clause. This can result in the deletion of all the records in the table. It might also happen that you&rsquo;ve specified the incorrect WHERE clause, resulting in the deletion of some important records from the table, which you don&rsquo;t want to delete. However, you can easily recover such deleted table records in SQL Server. In this post, we will discuss different methods to recover deleted records from table.<\/p><h2 class=\"wp-block-heading\" id=\"methods-to-recover-deleted-table-records-in-sql-server\"><strong>Methods to Recover Deleted Table Records in SQL Server<\/strong><\/h2><p>You can follow the given methods to recover the deleted records in MS SQL Server.<\/p><h3 class=\"wp-block-heading\"><strong>Method 1 &ndash; Use Log Sequence Number (LSN)<\/strong><\/h3><p>If you know the time when the record is deleted, then you can use the Log Sequence Number (LSN) to recover the deleted rows and their records from a SQL database table. LSN is a unique identifier given to each record in a transaction log. You can construct restore sequences by using the LSN of a log record.<\/p><blockquote class=\"note_alert\">\n<p><em><strong>Note:<\/strong>&nbsp;This method will only work if you&rsquo;ve a healthy Transaction Log backup.<\/em><\/p>\n<\/blockquote><p>To understand how to use the LSN to recover deleted table records, we&rsquo;ll create a test database and a table, insert rows into the table, delete some rows using the DELETE operation, get information about the deleted data, and then recover the data using LSN.<\/p><h4 class=\"wp-block-heading\"><strong>Step 1: Create a Database<\/strong><\/h4><p>By executing the following query, we will create a database named &lsquo;RecoverDeletedRecords&rsquo; and a table named &lsquo;Employee&rsquo; with three columns:<\/p><pre class=\"wp-block-code command_container\"><code>USE [master];&nbsp;&nbsp;\n\nGO&nbsp;&nbsp;&nbsp;\n\nCREATE DATABASE RecoverDeletedRecords;\n\nGO\n\nUSE RecoverDeletedRecords;&nbsp;&nbsp;&nbsp;\n\nGO\n\nCREATE TABLE [Employee] (\n\n[Sr.No] INT IDENTITY,\n\n[Date] DATETIME DEFAULT GETDATE (),\n\n[City] CHAR (25) DEFAULT 'City1');<\/code><\/pre><figure class=\"wp-block-gallery has-nested-images columns-default is-cropped wp-block-gallery-1 is-layout-flex wp-block-gallery-is-layout-flex\">\n<figure class=\"wp-block-image size-large\"><img loading=\"lazy\" decoding=\"async\" width=\"1024\" height=\"609\" data-id=\"167779\" src=\"https:\/\/www.stellarinfo.com\/blog\/wp-content\/uploads\/2024\/05\/Create-Database-in-SSMS.png\" alt=\"Create Database in SSMS\" class=\"wp-image-167779 apply-gradient-on-post-images\" srcset=\"https:\/\/www.stellarinfo.com\/blog\/wp-content\/uploads\/2024\/05\/Create-Database-in-SSMS.png 1024w, https:\/\/www.stellarinfo.com\/blog\/wp-content\/uploads\/2024\/05\/Create-Database-in-SSMS-300x178.png 300w, https:\/\/www.stellarinfo.com\/blog\/wp-content\/uploads\/2024\/05\/Create-Database-in-SSMS-768x457.png 768w, https:\/\/www.stellarinfo.com\/blog\/wp-content\/uploads\/2024\/05\/Create-Database-in-SSMS-150x89.png 150w\" sizes=\"auto, (max-width: 1024px) 100vw, 1024px\" \/><\/figure>\n<\/figure><h4 class=\"wp-block-heading\"><strong>Step 2: Insert Data into the Table<\/strong><\/h4><p>Now, we&rsquo;ll insert rows into the table by running the following query:<\/p><pre class=\"wp-block-code command_container\"><code>USE RecoverDeletedRecords;\n\nGO\n\nINSERT INTO Employee DEFAULT VALUES;\n\nGO 100<\/code><\/pre><div class=\"wp-block-image\">\n<figure class=\"aligncenter size-full is-resized\"><img loading=\"lazy\" decoding=\"async\" width=\"1024\" height=\"608\" src=\"https:\/\/www.stellarinfo.com\/blog\/wp-content\/uploads\/2024\/05\/Insert-Records-into-New-Table.png\" alt=\"Insert Records into New Table\" class=\"wp-image-167780 apply-gradient-on-post-images\" style=\"width:665px;height:auto\" srcset=\"https:\/\/www.stellarinfo.com\/blog\/wp-content\/uploads\/2024\/05\/Insert-Records-into-New-Table.png 1024w, https:\/\/www.stellarinfo.com\/blog\/wp-content\/uploads\/2024\/05\/Insert-Records-into-New-Table-300x178.png 300w, https:\/\/www.stellarinfo.com\/blog\/wp-content\/uploads\/2024\/05\/Insert-Records-into-New-Table-768x456.png 768w, https:\/\/www.stellarinfo.com\/blog\/wp-content\/uploads\/2024\/05\/Insert-Records-into-New-Table-150x89.png 150w\" sizes=\"auto, (max-width: 1024px) 100vw, 1024px\" \/><\/figure>\n<\/div><h4 class=\"wp-block-heading\"><strong>Step 3: Delete Rows from the Table<\/strong><\/h4><p>Now, we will delete some rows from the table (named Employee) by executing the following:<\/p><pre class=\"wp-block-code command_container\"><code>USE RecoverDeletedRecords\n\nGo\n\nDELETE Employee\n\nWHERE [Sr.No] &lt; 10\n\nGO\n\nSelect * from Employee<\/code><\/pre><figure class=\"wp-block-gallery has-nested-images columns-default is-cropped wp-block-gallery-2 is-layout-flex wp-block-gallery-is-layout-flex\">\n<figure class=\"wp-block-image size-large\"><img loading=\"lazy\" decoding=\"async\" width=\"1024\" height=\"609\" data-id=\"167781\" src=\"https:\/\/www.stellarinfo.com\/blog\/wp-content\/uploads\/2024\/05\/Delete-Table-Rows.png\" alt=\"Delete Table Rows\" class=\"wp-image-167781 apply-gradient-on-post-images\" srcset=\"https:\/\/www.stellarinfo.com\/blog\/wp-content\/uploads\/2024\/05\/Delete-Table-Rows.png 1024w, https:\/\/www.stellarinfo.com\/blog\/wp-content\/uploads\/2024\/05\/Delete-Table-Rows-300x178.png 300w, https:\/\/www.stellarinfo.com\/blog\/wp-content\/uploads\/2024\/05\/Delete-Table-Rows-768x457.png 768w, https:\/\/www.stellarinfo.com\/blog\/wp-content\/uploads\/2024\/05\/Delete-Table-Rows-150x89.png 150w\" sizes=\"auto, (max-width: 1024px) 100vw, 1024px\" \/><\/figure>\n<\/figure><p>All the rows having Sr. No less than 10 will be deleted from the table &lsquo;Employee&rsquo; (see the above image).<\/p><h4 class=\"wp-block-heading\"><strong>Step 4: Get Information about Deleted Rows<\/strong><\/h4><p>Next, we will get information about the deleted rows by searching the transaction log by executing the below query:<\/p><pre class=\"wp-block-code command_container\"><code>USE RecoverDeletedRecords\n\nGO\n\nSELECT\n\n&nbsp;[Current LSN],&nbsp;&nbsp;&nbsp;\n\n&nbsp;[Transaction ID],\n\n&nbsp;&nbsp;&nbsp;&nbsp; Operation,\n\n&nbsp;&nbsp;&nbsp;&nbsp; Context,\n\n&nbsp;&nbsp;&nbsp;&nbsp; AllocUnitName\n\nFROM\n\n&nbsp;&nbsp;&nbsp; fn_dblog(NULL, NULL)\n\nWHERE\n\n&nbsp;&nbsp;&nbsp; Operation = 'LOP_DELETE_ROWS'<\/code><\/pre><div class=\"wp-block-image\">\n<figure class=\"aligncenter size-full is-resized\"><img loading=\"lazy\" decoding=\"async\" width=\"587\" height=\"521\" src=\"https:\/\/www.stellarinfo.com\/blog\/wp-content\/uploads\/2024\/05\/Transaction-ID-of-Deleted-Rows.png\" alt=\"Transaction ID of Deleted Rows\" class=\"wp-image-167783 apply-gradient-on-post-images\" style=\"width:587px;height:auto\" srcset=\"https:\/\/www.stellarinfo.com\/blog\/wp-content\/uploads\/2024\/05\/Transaction-ID-of-Deleted-Rows.png 587w, https:\/\/www.stellarinfo.com\/blog\/wp-content\/uploads\/2024\/05\/Transaction-ID-of-Deleted-Rows-300x266.png 300w, https:\/\/www.stellarinfo.com\/blog\/wp-content\/uploads\/2024\/05\/Transaction-ID-of-Deleted-Rows-150x133.png 150w\" sizes=\"auto, (max-width: 587px) 100vw, 587px\" \/><\/figure>\n<\/div><p>After getting the Transaction IDs of the deleted rows, we&rsquo;ll have to find the time when the rows were deleted.<\/p><h4 class=\"wp-block-heading\"><strong>Step 5: Get Log Sequence Number of the&nbsp;LOP_BEGIN_XACT Log Record<\/strong><\/h4><p>To find the exact time when the rows were deleted, we will use the transaction ID to get the LSN of the LOP_BEGIN_XACT log record of a transaction:<\/p><pre class=\"wp-block-code command_container\"><code>USE RecoverDeletedRecords\n\nGO\n\nSELECT\n\n&nbsp;[Current LSN],&nbsp;&nbsp;&nbsp;\n\n&nbsp;Operation,\n\n&nbsp;&nbsp;&nbsp;&nbsp; [Transaction ID],\n\n&nbsp;&nbsp;&nbsp;&nbsp; [Begin Time],\n\n&nbsp;&nbsp;&nbsp;&nbsp; [Transaction Name],\n\n&nbsp;&nbsp;&nbsp;&nbsp; [Transaction SID]\n\nFROM\n\n&nbsp;&nbsp;&nbsp; fn_dblog(NULL, NULL)\n\nWHERE\n\n&nbsp;&nbsp;&nbsp; [Transaction ID] = '0000:0000020e'\n\nAND\n\n&nbsp;&nbsp;&nbsp; [Operation] = 'LOP_BEGIN_XACT'<\/code><\/pre><figure class=\"wp-block-gallery has-nested-images columns-default is-cropped wp-block-gallery-3 is-layout-flex wp-block-gallery-is-layout-flex\">\n<figure class=\"wp-block-image size-large\"><img loading=\"lazy\" decoding=\"async\" width=\"767\" height=\"531\" data-id=\"167784\" src=\"https:\/\/www.stellarinfo.com\/blog\/wp-content\/uploads\/2024\/05\/Current-LSN-of-Transaction-Log.png\" alt=\"Current LSN of Transaction Log\" class=\"wp-image-167784 apply-gradient-on-post-images\" srcset=\"https:\/\/www.stellarinfo.com\/blog\/wp-content\/uploads\/2024\/05\/Current-LSN-of-Transaction-Log.png 767w, https:\/\/www.stellarinfo.com\/blog\/wp-content\/uploads\/2024\/05\/Current-LSN-of-Transaction-Log-300x208.png 300w, https:\/\/www.stellarinfo.com\/blog\/wp-content\/uploads\/2024\/05\/Current-LSN-of-Transaction-Log-150x104.png 150w\" sizes=\"auto, (max-width: 767px) 100vw, 767px\" \/><\/figure>\n<\/figure><p>The above command will show the current LSN of the transaction, the time (2021\/03\/15 19:36:59:337) when the DELETE statement was executed, LSN (00000014:0000001a:0001), and Transaction ID (0000:0000020e).<\/p><h4 class=\"wp-block-heading\"><strong>Step 6: Recover Deleted Records<\/strong><\/h4><p>To recover the deleted SQL table records, we need to convert the LSN values from hexadecimal to decimal form.&nbsp;To do so, we need to add &lsquo;0x&rsquo; before the log sequence number (see the below code).<\/p><pre class=\"wp-block-code command_container\"><code>--Restoring Full backup with norecovery.\n\nRESTORE DATABASE RecoverDeletedRecords_COPY\n\n&nbsp;&nbsp;&nbsp; FROM DISK = 'C:\\Program Files\\Microsoft SQL Server\\MSSQL10_50.STELLAR\\MSSQL\\Backup\\RecoverDeletedRecords.bak'\n\nWITH\n\n&nbsp;&nbsp;&nbsp; MOVE 'RecoverDeletedRecords' TO 'C:\\Program Files\\Microsoft SQL Server\\MSSQL10_50.STELLAR\\MSSQL\\Backup\\RecoverDeletedRecords.mdf',\n\n&nbsp;&nbsp;&nbsp; MOVE 'RecoverDeletedRecords_log' TO 'C:\\Program Files\\Microsoft SQL Server\\MSSQL10_50.STELLAR\\MSSQL\\Backup\\RecoverDeletedRecords.ldf',\n\n&nbsp;&nbsp;&nbsp; REPLACE, NORECOVERY;\n\n&nbsp;&nbsp;&nbsp; GO\n\n--Restore Log backup with STOPBEFOREMARK option to recover exact LSN.\n\n&nbsp;&nbsp; RESTORE LOG RecoverDeletedRecords_COPY\n\nFROM\n\n&nbsp;&nbsp;&nbsp; DISK = N'C:\\Program Files\\Microsoft SQL Server\\MSSQL10_50.STELLAR\\MSSQL\\Backup\\RecoverDeletedRecords_tlogbackup.trn'\n\nWITH\n\n&nbsp;&nbsp;&nbsp; STOPBEFOREMARK = 'lsn:0x00000014:0000001a:0001'<\/code><\/pre><div class=\"wp-block-image\">\n<figure class=\"aligncenter size-full is-resized\"><img loading=\"lazy\" decoding=\"async\" width=\"1024\" height=\"610\" src=\"https:\/\/www.stellarinfo.com\/blog\/wp-content\/uploads\/2024\/05\/Recover-Deleted-Records.png\" alt=\"Recover Deleted Records\" class=\"wp-image-167785 apply-gradient-on-post-images\" style=\"width:752px;height:auto\" srcset=\"https:\/\/www.stellarinfo.com\/blog\/wp-content\/uploads\/2024\/05\/Recover-Deleted-Records.png 1024w, https:\/\/www.stellarinfo.com\/blog\/wp-content\/uploads\/2024\/05\/Recover-Deleted-Records-300x179.png 300w, https:\/\/www.stellarinfo.com\/blog\/wp-content\/uploads\/2024\/05\/Recover-Deleted-Records-768x458.png 768w, https:\/\/www.stellarinfo.com\/blog\/wp-content\/uploads\/2024\/05\/Recover-Deleted-Records-150x89.png 150w\" sizes=\"auto, (max-width: 1024px) 100vw, 1024px\" \/><\/figure>\n<\/div><p>Now that the data is restored, we need to check whether the deleted records have been recovered. The following query will show the first 10 rows of the table that were deleted (in Step 3).<\/p><pre class=\"wp-block-code command_container\"><code>USE RecoverDeletedRecords_COPY\n\nGO\n\nSELECT * from Employee<\/code><\/pre><h3 class=\"wp-block-heading\"><strong>Method 2 &ndash; Use a MS SQL Database Recovery Software<\/strong><\/h3><p>If the above method fails to recover the deleted records or you don&rsquo;t have backup, you can use a third-party SQL database recovery software, such as Stellar Repair for MS SQL. It is an advanced SQL repair software that can help you to quickly recover deleted records from the database file in a few clicks. The software can also recover other objects, like views, triggers, stored procedures, etc. from corrupted or damaged database files. The tool supports all Windows editions, including the latest Windows 11.<\/p><h4 class=\"wp-block-heading\"><strong>Steps to Recover Deleted Table Records using Stellar Repair for MS SQL:<\/strong><\/h4><ul class=\"wp-block-list\">\n<li>Download, install, and launch Stellar Repair for MS SQL software.<\/li>\n<\/ul><ul class=\"wp-block-list\">\n<li>On the software&rsquo;s main interface, you&rsquo;ll see a prompt to stop SQL Server database and create a copy of the database at a different location. Press&nbsp;<strong>OK.<\/strong><\/li>\n<\/ul><div class=\"wp-block-image\">\n<figure class=\"aligncenter size-full is-resized\"><img loading=\"lazy\" decoding=\"async\" width=\"1024\" height=\"547\" src=\"https:\/\/www.stellarinfo.com\/blog\/wp-content\/uploads\/2024\/05\/Stellar-Repair-for-MS-SQL-Main-Interface.png\" alt=\"Stellar Repair for MS SQL Main Interface\" class=\"wp-image-167786 apply-gradient-on-post-images\" style=\"width:674px;height:auto\" srcset=\"https:\/\/www.stellarinfo.com\/blog\/wp-content\/uploads\/2024\/05\/Stellar-Repair-for-MS-SQL-Main-Interface.png 1024w, https:\/\/www.stellarinfo.com\/blog\/wp-content\/uploads\/2024\/05\/Stellar-Repair-for-MS-SQL-Main-Interface-300x160.png 300w, https:\/\/www.stellarinfo.com\/blog\/wp-content\/uploads\/2024\/05\/Stellar-Repair-for-MS-SQL-Main-Interface-768x410.png 768w, https:\/\/www.stellarinfo.com\/blog\/wp-content\/uploads\/2024\/05\/Stellar-Repair-for-MS-SQL-Main-Interface-150x80.png 150w\" sizes=\"auto, (max-width: 1024px) 100vw, 1024px\" \/><\/figure>\n<\/div><ul class=\"wp-block-list\">\n<li>Click <strong>Browse<\/strong> to select&nbsp;the SQL database (MDF) file from where you want to recover the deleted records. If you do not know the location of MDF file, click&nbsp;<strong>Search<\/strong>&nbsp;to find the file.<\/li>\n<\/ul><div class=\"wp-block-image\">\n<figure class=\"aligncenter size-full is-resized\"><img loading=\"lazy\" decoding=\"async\" width=\"522\" height=\"364\" src=\"https:\/\/www.stellarinfo.com\/blog\/wp-content\/uploads\/2024\/05\/Select-Database-MDF-File-in-Software.png\" alt=\"Select Database MDF File in Software\" class=\"wp-image-167787 apply-gradient-on-post-images\" style=\"width:667px;height:auto\" srcset=\"https:\/\/www.stellarinfo.com\/blog\/wp-content\/uploads\/2024\/05\/Select-Database-MDF-File-in-Software.png 522w, https:\/\/www.stellarinfo.com\/blog\/wp-content\/uploads\/2024\/05\/Select-Database-MDF-File-in-Software-300x209.png 300w, https:\/\/www.stellarinfo.com\/blog\/wp-content\/uploads\/2024\/05\/Select-Database-MDF-File-in-Software-150x105.png 150w\" sizes=\"auto, (max-width: 522px) 100vw, 522px\" \/><\/figure>\n<\/div><ul class=\"wp-block-list\">\n<li>After selecting the database file, click the&nbsp;<strong>Include Deleted Records<\/strong>&nbsp;option and then click&nbsp;Repair.<\/li>\n<\/ul><div class=\"wp-block-image\">\n<figure class=\"aligncenter size-full is-resized\"><img loading=\"lazy\" decoding=\"async\" width=\"522\" height=\"362\" src=\"https:\/\/www.stellarinfo.com\/blog\/wp-content\/uploads\/2024\/05\/Include-Deleted-Records-Option.png\" alt=\"Include Deleted Records Option\" class=\"wp-image-167788 apply-gradient-on-post-images\" style=\"width:655px;height:auto\" srcset=\"https:\/\/www.stellarinfo.com\/blog\/wp-content\/uploads\/2024\/05\/Include-Deleted-Records-Option.png 522w, https:\/\/www.stellarinfo.com\/blog\/wp-content\/uploads\/2024\/05\/Include-Deleted-Records-Option-300x208.png 300w, https:\/\/www.stellarinfo.com\/blog\/wp-content\/uploads\/2024\/05\/Include-Deleted-Records-Option-150x104.png 150w\" sizes=\"auto, (max-width: 522px) 100vw, 522px\" \/><\/figure>\n<\/div><ul class=\"wp-block-list\">\n<li>Select the appropriate scan mode &ndash;&nbsp;&lsquo;Standard Scan&rsquo;&nbsp;or&nbsp;&lsquo;Advanced Scan&rsquo; and then click&nbsp;<strong>OK<\/strong>.<\/li>\n<\/ul><div class=\"wp-block-image\">\n<figure class=\"aligncenter size-full\"><img loading=\"lazy\" decoding=\"async\" width=\"506\" height=\"207\" src=\"https:\/\/www.stellarinfo.com\/blog\/wp-content\/uploads\/2024\/05\/Select-Scan-Mode.png\" alt=\"Select Scan Mode\" class=\"wp-image-167789 apply-gradient-on-post-images\" srcset=\"https:\/\/www.stellarinfo.com\/blog\/wp-content\/uploads\/2024\/05\/Select-Scan-Mode.png 506w, https:\/\/www.stellarinfo.com\/blog\/wp-content\/uploads\/2024\/05\/Select-Scan-Mode-300x123.png 300w, https:\/\/www.stellarinfo.com\/blog\/wp-content\/uploads\/2024\/05\/Select-Scan-Mode-150x61.png 150w\" sizes=\"auto, (max-width: 506px) 100vw, 506px\" \/><\/figure>\n<\/div><ul class=\"wp-block-list\">\n<li>Click&nbsp;OK&nbsp;when the&nbsp;&lsquo;Repair Complete&rsquo;&nbsp;message box appears.<\/li>\n<\/ul><div class=\"wp-block-image\">\n<figure class=\"aligncenter size-full\"><img loading=\"lazy\" decoding=\"async\" width=\"474\" height=\"184\" src=\"https:\/\/www.stellarinfo.com\/blog\/wp-content\/uploads\/2024\/05\/Repair-Complete-Message-Box.png\" alt=\"Repair Complete Message Box\" class=\"wp-image-167790 apply-gradient-on-post-images\" srcset=\"https:\/\/www.stellarinfo.com\/blog\/wp-content\/uploads\/2024\/05\/Repair-Complete-Message-Box.png 474w, https:\/\/www.stellarinfo.com\/blog\/wp-content\/uploads\/2024\/05\/Repair-Complete-Message-Box-300x116.png 300w, https:\/\/www.stellarinfo.com\/blog\/wp-content\/uploads\/2024\/05\/Repair-Complete-Message-Box-150x58.png 150w\" sizes=\"auto, (max-width: 474px) 100vw, 474px\" \/><\/figure>\n<\/div><ul class=\"wp-block-list\">\n<li>The software shows preview of all the recoverable items. You can look for the table records marked as deleted.<\/li>\n<\/ul><div class=\"wp-block-image\">\n<figure class=\"aligncenter size-full is-resized\"><img loading=\"lazy\" decoding=\"async\" width=\"902\" height=\"729\" src=\"https:\/\/www.stellarinfo.com\/blog\/wp-content\/uploads\/2024\/05\/Log-Report.png\" alt=\"Log Report\" class=\"wp-image-167791 apply-gradient-on-post-images\" style=\"width:663px;height:auto\" srcset=\"https:\/\/www.stellarinfo.com\/blog\/wp-content\/uploads\/2024\/05\/Log-Report.png 902w, https:\/\/www.stellarinfo.com\/blog\/wp-content\/uploads\/2024\/05\/Log-Report-300x242.png 300w, https:\/\/www.stellarinfo.com\/blog\/wp-content\/uploads\/2024\/05\/Log-Report-768x621.png 768w, https:\/\/www.stellarinfo.com\/blog\/wp-content\/uploads\/2024\/05\/Log-Report-150x121.png 150w\" sizes=\"auto, (max-width: 902px) 100vw, 902px\" \/><\/figure>\n<\/div><ul class=\"wp-block-list\">\n<li>Next, select the items (deleted records) you want to save and click&nbsp;<strong>Save<\/strong>&nbsp;on the&nbsp;File&nbsp;menu.<\/li>\n<\/ul><div class=\"wp-block-image\">\n<figure class=\"aligncenter size-full is-resized\"><img loading=\"lazy\" decoding=\"async\" width=\"600\" height=\"149\" src=\"https:\/\/www.stellarinfo.com\/blog\/wp-content\/uploads\/2024\/05\/Save-Database.jpg\" alt=\"Save Database\" class=\"wp-image-167792 apply-gradient-on-post-images\" style=\"width:600px;height:auto\" srcset=\"https:\/\/www.stellarinfo.com\/blog\/wp-content\/uploads\/2024\/05\/Save-Database.jpg 600w, https:\/\/www.stellarinfo.com\/blog\/wp-content\/uploads\/2024\/05\/Save-Database-300x75.jpg 300w, https:\/\/www.stellarinfo.com\/blog\/wp-content\/uploads\/2024\/05\/Save-Database-150x37.jpg 150w\" sizes=\"auto, (max-width: 600px) 100vw, 600px\" \/><\/figure>\n<\/div><ul class=\"wp-block-list\">\n<li>In&nbsp;&lsquo;<strong>Save Database<\/strong>&rsquo;&nbsp;dialog box, you can choose to save the recovered data in MDF, CSV, HTML, or XLS (Excel) file format. Then, click <strong>Save<\/strong>.<\/li>\n<\/ul><div class=\"wp-block-image\">\n<figure class=\"aligncenter size-full\"><img loading=\"lazy\" decoding=\"async\" width=\"482\" height=\"404\" src=\"https:\/\/www.stellarinfo.com\/blog\/wp-content\/uploads\/2024\/05\/Saving-Options.png\" alt=\"Saving Options\" class=\"wp-image-167793 apply-gradient-on-post-images\" srcset=\"https:\/\/www.stellarinfo.com\/blog\/wp-content\/uploads\/2024\/05\/Saving-Options.png 482w, https:\/\/www.stellarinfo.com\/blog\/wp-content\/uploads\/2024\/05\/Saving-Options-300x251.png 300w, https:\/\/www.stellarinfo.com\/blog\/wp-content\/uploads\/2024\/05\/Saving-Options-150x126.png 150w\" sizes=\"auto, (max-width: 482px) 100vw, 482px\" \/><\/figure>\n<\/div><p><strong>Watch the complete working process of the SQL Recovery software:<\/strong><\/p><figure class=\"wp-block-embed is-type-video is-provider-youtube wp-block-embed-youtube wp-embed-aspect-16-9 wp-has-aspect-ratio\"><div class=\"wp-block-embed__wrapper\">\n<iframe loading=\"lazy\" title=\"How to Repair and Restore Corrupt SQL server Database with Stellar Repair for MS SQL Software\" width=\"750\" height=\"422\" src=\"https:\/\/www.youtube.com\/embed\/yso4axqXxlM?feature=oembed\" frameborder=\"0\" allow=\"accelerometer; autoplay; clipboard-write; encrypted-media; gyroscope; picture-in-picture; web-share\" referrerpolicy=\"strict-origin-when-cross-origin\" allowfullscreen><\/iframe>\n<\/div><\/figure><h2 class=\"wp-block-heading\" id=\"conclusion\"><strong>Conclusion<\/strong><\/h2><p>Above, we have discussed the methods to recover deleted records in SQL Server. If you&rsquo;ve the database backup available and know the time of deletion of records, then you can use the transaction log with Log Sequence Number (LSN) to regain access to the deleted records. If this method doesn&rsquo;t work or if your backup file is corrupted, then you can use <strong><a href=\"https:\/\/www.stellarinfo.com\/sql-recovery.php\" target=\"_blank\" rel=\"noreferrer noopener\">Stellar Repair for MS SQL<\/a><\/strong> to recover the deleted data from the SQL Server database file. This tool&nbsp;not only helps in recovering deleted records but also restores all the data from the damaged or corrupted SQL database file.<\/p><p><strong>FAQ:<\/strong><\/p>\n","protected":false},"excerpt":{"rendered":"<p>In SQL Server, you can use the DELETE statement with WHERE clause&hellip; <a class=\"more-link\" href=\"https:\/\/www.stellarinfo.com\/blog\/recover-deleted-records-in-sql-server\/\">Continue reading <span class=\"screen-reader-text\">How to Recover Deleted Table Records in SQL Server?<\/span><\/a><\/p>\n","protected":false},"author":82,"featured_media":167862,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[10],"tags":[831],"class_list":["post-20678","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-database-recovery","tag-recover-deleted-records-in-sql-server","entry"],"acf":[],"yoast_head":"<!-- This site is optimized with the Yoast SEO plugin v26.9 - https:\/\/yoast.com\/product\/yoast-seo-wordpress\/ -->\n<title>How to Recover Deleted Table Records in SQL Server?<\/title>\n<meta name=\"description\" content=\"Recover deleted records in SQL Server 2019, 2017, 2016, 2014, 2012, 2008 and older versions using a safe and secure SQL recovery tool.\" \/>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/www.stellarinfo.com\/blog\/recover-deleted-records-in-sql-server\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"How to Recover Deleted Table Records in SQL Server?\" \/>\n<meta property=\"og:description\" content=\"Recover deleted records in SQL Server 2019, 2017, 2016, 2014, 2012, 2008 and older versions using a safe and secure SQL recovery tool.\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.stellarinfo.com\/blog\/recover-deleted-records-in-sql-server\/\" \/>\n<meta property=\"og:site_name\" content=\"Stellar Data Recovery Blog\" \/>\n<meta property=\"article:published_time\" content=\"2018-06-27T11:02:27+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2024-11-05T06:34:54+00:00\" \/>\n<meta property=\"og:image\" content=\"https:\/\/www.stellarinfo.com\/blog\/wp-content\/uploads\/2018\/06\/Recover-Deleted-Table-Records-in-SQL-Server.jpg\" \/>\n\t<meta property=\"og:image:width\" content=\"1000\" \/>\n\t<meta property=\"og:image:height\" content=\"600\" \/>\n\t<meta property=\"og:image:type\" content=\"image\/jpeg\" \/>\n<meta name=\"author\" content=\"Monika Dadool\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Monika Dadool\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"7 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\/\/www.stellarinfo.com\/blog\/recover-deleted-records-in-sql-server\/#article\",\"isPartOf\":{\"@id\":\"https:\/\/www.stellarinfo.com\/blog\/recover-deleted-records-in-sql-server\/\"},\"author\":{\"name\":\"Monika Dadool\",\"@id\":\"https:\/\/www.stellarinfo.com\/blog\/#\/schema\/person\/02a465e9b5b4912eafedd1ae248558fd\"},\"headline\":\"How to Recover Deleted Table Records in SQL Server?\",\"datePublished\":\"2018-06-27T11:02:27+00:00\",\"dateModified\":\"2024-11-05T06:34:54+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\/\/www.stellarinfo.com\/blog\/recover-deleted-records-in-sql-server\/\"},\"wordCount\":948,\"commentCount\":12,\"image\":{\"@id\":\"https:\/\/www.stellarinfo.com\/blog\/recover-deleted-records-in-sql-server\/#primaryimage\"},\"thumbnailUrl\":\"https:\/\/www.stellarinfo.com\/blog\/wp-content\/uploads\/2018\/06\/Recover-Deleted-Table-Records-in-SQL-Server.jpg\",\"keywords\":[\"recover Deleted Records in SQL Server\"],\"articleSection\":[\"SQL Database Repair\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\/\/www.stellarinfo.com\/blog\/recover-deleted-records-in-sql-server\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\/\/www.stellarinfo.com\/blog\/recover-deleted-records-in-sql-server\/\",\"url\":\"https:\/\/www.stellarinfo.com\/blog\/recover-deleted-records-in-sql-server\/\",\"name\":\"How to Recover Deleted Table Records in SQL Server?\",\"isPartOf\":{\"@id\":\"https:\/\/www.stellarinfo.com\/blog\/#website\"},\"primaryImageOfPage\":{\"@id\":\"https:\/\/www.stellarinfo.com\/blog\/recover-deleted-records-in-sql-server\/#primaryimage\"},\"image\":{\"@id\":\"https:\/\/www.stellarinfo.com\/blog\/recover-deleted-records-in-sql-server\/#primaryimage\"},\"thumbnailUrl\":\"https:\/\/www.stellarinfo.com\/blog\/wp-content\/uploads\/2018\/06\/Recover-Deleted-Table-Records-in-SQL-Server.jpg\",\"datePublished\":\"2018-06-27T11:02:27+00:00\",\"dateModified\":\"2024-11-05T06:34:54+00:00\",\"author\":{\"@id\":\"https:\/\/www.stellarinfo.com\/blog\/#\/schema\/person\/02a465e9b5b4912eafedd1ae248558fd\"},\"description\":\"Recover deleted records in SQL Server 2019, 2017, 2016, 2014, 2012, 2008 and older versions using a safe and secure SQL recovery tool.\",\"breadcrumb\":{\"@id\":\"https:\/\/www.stellarinfo.com\/blog\/recover-deleted-records-in-sql-server\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.stellarinfo.com\/blog\/recover-deleted-records-in-sql-server\/\"]}]},{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/www.stellarinfo.com\/blog\/recover-deleted-records-in-sql-server\/#primaryimage\",\"url\":\"https:\/\/www.stellarinfo.com\/blog\/wp-content\/uploads\/2018\/06\/Recover-Deleted-Table-Records-in-SQL-Server.jpg\",\"contentUrl\":\"https:\/\/www.stellarinfo.com\/blog\/wp-content\/uploads\/2018\/06\/Recover-Deleted-Table-Records-in-SQL-Server.jpg\",\"width\":1000,\"height\":600,\"caption\":\"Recover Deleted Table Records in SQL Server\"},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.stellarinfo.com\/blog\/recover-deleted-records-in-sql-server\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/www.stellarinfo.com\/blog\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"How to Recover Deleted Table Records in SQL Server?\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/www.stellarinfo.com\/blog\/#website\",\"url\":\"https:\/\/www.stellarinfo.com\/blog\/\",\"name\":\"Stellar Data Recovery Blog\",\"description\":\"\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/www.stellarinfo.com\/blog\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":\"Person\",\"@id\":\"https:\/\/www.stellarinfo.com\/blog\/#\/schema\/person\/02a465e9b5b4912eafedd1ae248558fd\",\"name\":\"Monika Dadool\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/www.stellarinfo.com\/blog\/#\/schema\/person\/image\/\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/7d22d2cc256776033dcf284e9c6b96fcf19473429aa25ea91b3f7561ae5e8b7a?s=96&d=mm&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/7d22d2cc256776033dcf284e9c6b96fcf19473429aa25ea91b3f7561ae5e8b7a?s=96&d=mm&r=g\",\"caption\":\"Monika Dadool\"},\"description\":\"Monika Dadool is a Senior Content Writer at Stellar with over 5 years of experience in technical writing. She is a tech enthusiast and expert who specializes in writing about SQL Server, MySQL Server, MariaDB Server, Microsoft Access, Active Directory, email recovery, Microsoft 365, pattern recognition, machine learning, data recovery, file repair, and operating systems like Linux, Windows, and Mac. She also writes about accounting software such as QuickBooks and Sage 50, as well as web-scripting languages like HTML, JavaScript, Python, PHP, Visual Basic, ASP.NET, and AJAX. Monika is passionate about researching and exploring new technologies, and she enjoys developing engaging technical blogs that help organizations and database administrators resolve various issues. When she's not creating content, you can find her on social media, watching web series, reading books, or exploring new food recipes.\",\"sameAs\":[\"https:\/\/www.linkedin.com\/in\/monika-dadool-105a87163\/\"],\"url\":\"https:\/\/www.stellarinfo.com\/blog\/author\/monika\/\"}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"How to Recover Deleted Table Records in SQL Server?","description":"Recover deleted records in SQL Server 2019, 2017, 2016, 2014, 2012, 2008 and older versions using a safe and secure SQL recovery tool.","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/www.stellarinfo.com\/blog\/recover-deleted-records-in-sql-server\/","og_locale":"en_US","og_type":"article","og_title":"How to Recover Deleted Table Records in SQL Server?","og_description":"Recover deleted records in SQL Server 2019, 2017, 2016, 2014, 2012, 2008 and older versions using a safe and secure SQL recovery tool.","og_url":"https:\/\/www.stellarinfo.com\/blog\/recover-deleted-records-in-sql-server\/","og_site_name":"Stellar Data Recovery Blog","article_published_time":"2018-06-27T11:02:27+00:00","article_modified_time":"2024-11-05T06:34:54+00:00","og_image":[{"width":1000,"height":600,"url":"https:\/\/www.stellarinfo.com\/blog\/wp-content\/uploads\/2018\/06\/Recover-Deleted-Table-Records-in-SQL-Server.jpg","type":"image\/jpeg"}],"author":"Monika Dadool","twitter_card":"summary_large_image","twitter_misc":{"Written by":"Monika Dadool","Est. reading time":"7 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/www.stellarinfo.com\/blog\/recover-deleted-records-in-sql-server\/#article","isPartOf":{"@id":"https:\/\/www.stellarinfo.com\/blog\/recover-deleted-records-in-sql-server\/"},"author":{"name":"Monika Dadool","@id":"https:\/\/www.stellarinfo.com\/blog\/#\/schema\/person\/02a465e9b5b4912eafedd1ae248558fd"},"headline":"How to Recover Deleted Table Records in SQL Server?","datePublished":"2018-06-27T11:02:27+00:00","dateModified":"2024-11-05T06:34:54+00:00","mainEntityOfPage":{"@id":"https:\/\/www.stellarinfo.com\/blog\/recover-deleted-records-in-sql-server\/"},"wordCount":948,"commentCount":12,"image":{"@id":"https:\/\/www.stellarinfo.com\/blog\/recover-deleted-records-in-sql-server\/#primaryimage"},"thumbnailUrl":"https:\/\/www.stellarinfo.com\/blog\/wp-content\/uploads\/2018\/06\/Recover-Deleted-Table-Records-in-SQL-Server.jpg","keywords":["recover Deleted Records in SQL Server"],"articleSection":["SQL Database Repair"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/www.stellarinfo.com\/blog\/recover-deleted-records-in-sql-server\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/www.stellarinfo.com\/blog\/recover-deleted-records-in-sql-server\/","url":"https:\/\/www.stellarinfo.com\/blog\/recover-deleted-records-in-sql-server\/","name":"How to Recover Deleted Table Records in SQL Server?","isPartOf":{"@id":"https:\/\/www.stellarinfo.com\/blog\/#website"},"primaryImageOfPage":{"@id":"https:\/\/www.stellarinfo.com\/blog\/recover-deleted-records-in-sql-server\/#primaryimage"},"image":{"@id":"https:\/\/www.stellarinfo.com\/blog\/recover-deleted-records-in-sql-server\/#primaryimage"},"thumbnailUrl":"https:\/\/www.stellarinfo.com\/blog\/wp-content\/uploads\/2018\/06\/Recover-Deleted-Table-Records-in-SQL-Server.jpg","datePublished":"2018-06-27T11:02:27+00:00","dateModified":"2024-11-05T06:34:54+00:00","author":{"@id":"https:\/\/www.stellarinfo.com\/blog\/#\/schema\/person\/02a465e9b5b4912eafedd1ae248558fd"},"description":"Recover deleted records in SQL Server 2019, 2017, 2016, 2014, 2012, 2008 and older versions using a safe and secure SQL recovery tool.","breadcrumb":{"@id":"https:\/\/www.stellarinfo.com\/blog\/recover-deleted-records-in-sql-server\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.stellarinfo.com\/blog\/recover-deleted-records-in-sql-server\/"]}]},{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/www.stellarinfo.com\/blog\/recover-deleted-records-in-sql-server\/#primaryimage","url":"https:\/\/www.stellarinfo.com\/blog\/wp-content\/uploads\/2018\/06\/Recover-Deleted-Table-Records-in-SQL-Server.jpg","contentUrl":"https:\/\/www.stellarinfo.com\/blog\/wp-content\/uploads\/2018\/06\/Recover-Deleted-Table-Records-in-SQL-Server.jpg","width":1000,"height":600,"caption":"Recover Deleted Table Records in SQL Server"},{"@type":"BreadcrumbList","@id":"https:\/\/www.stellarinfo.com\/blog\/recover-deleted-records-in-sql-server\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/www.stellarinfo.com\/blog\/"},{"@type":"ListItem","position":2,"name":"How to Recover Deleted Table Records in SQL Server?"}]},{"@type":"WebSite","@id":"https:\/\/www.stellarinfo.com\/blog\/#website","url":"https:\/\/www.stellarinfo.com\/blog\/","name":"Stellar Data Recovery Blog","description":"","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/www.stellarinfo.com\/blog\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Person","@id":"https:\/\/www.stellarinfo.com\/blog\/#\/schema\/person\/02a465e9b5b4912eafedd1ae248558fd","name":"Monika Dadool","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/www.stellarinfo.com\/blog\/#\/schema\/person\/image\/","url":"https:\/\/secure.gravatar.com\/avatar\/7d22d2cc256776033dcf284e9c6b96fcf19473429aa25ea91b3f7561ae5e8b7a?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/7d22d2cc256776033dcf284e9c6b96fcf19473429aa25ea91b3f7561ae5e8b7a?s=96&d=mm&r=g","caption":"Monika Dadool"},"description":"Monika Dadool is a Senior Content Writer at Stellar with over 5 years of experience in technical writing. She is a tech enthusiast and expert who specializes in writing about SQL Server, MySQL Server, MariaDB Server, Microsoft Access, Active Directory, email recovery, Microsoft 365, pattern recognition, machine learning, data recovery, file repair, and operating systems like Linux, Windows, and Mac. She also writes about accounting software such as QuickBooks and Sage 50, as well as web-scripting languages like HTML, JavaScript, Python, PHP, Visual Basic, ASP.NET, and AJAX. Monika is passionate about researching and exploring new technologies, and she enjoys developing engaging technical blogs that help organizations and database administrators resolve various issues. When she's not creating content, you can find her on social media, watching web series, reading books, or exploring new food recipes.","sameAs":["https:\/\/www.linkedin.com\/in\/monika-dadool-105a87163\/"],"url":"https:\/\/www.stellarinfo.com\/blog\/author\/monika\/"}]}},"_links":{"self":[{"href":"https:\/\/www.stellarinfo.com\/blog\/wp-json\/wp\/v2\/posts\/20678","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.stellarinfo.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.stellarinfo.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.stellarinfo.com\/blog\/wp-json\/wp\/v2\/users\/82"}],"replies":[{"embeddable":true,"href":"https:\/\/www.stellarinfo.com\/blog\/wp-json\/wp\/v2\/comments?post=20678"}],"version-history":[{"count":51,"href":"https:\/\/www.stellarinfo.com\/blog\/wp-json\/wp\/v2\/posts\/20678\/revisions"}],"predecessor-version":[{"id":173821,"href":"https:\/\/www.stellarinfo.com\/blog\/wp-json\/wp\/v2\/posts\/20678\/revisions\/173821"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.stellarinfo.com\/blog\/wp-json\/wp\/v2\/media\/167862"}],"wp:attachment":[{"href":"https:\/\/www.stellarinfo.com\/blog\/wp-json\/wp\/v2\/media?parent=20678"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.stellarinfo.com\/blog\/wp-json\/wp\/v2\/categories?post=20678"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.stellarinfo.com\/blog\/wp-json\/wp\/v2\/tags?post=20678"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}