Tuesday, August 8, 2017

Learn How to Recover Deleted Records from SQL Server 2014

Problem

Sometimes users may have numerous of databases in their SQL Server. However, while managing their database sometimes by mistake their records are deleted due to which they face a lot of issues. To maintain the workflow they need to recover deleted records from SQL Server 2014. This could be possible only with successful SQL LOG Recovery

How to Get Back Deleted Records in SQL Server: Solution

Mainly Transaction log is responsible for all database-logging activities. Similarly, in this discussion, we will utilize the usage of similar undocumented function "fn_dblog" to get any unauthorized deletion of data as well as to retrieve deleted data in SQL server.

If you are aware that when your data is removed then, you can easily solve your problem on how to recover deleted data in SQL server database. However, if you do not know the data as well as the time when the data is erased then, it is important to understand first who has deleted the data. In this segment, we will first find the particular LSN under which deleted statement is run after that we will rollback deleted records in SQL until that LSN.

Each record in MS SQL Server transaction log is distinctively recognized by LSN (Log Sequence Number). LSN of log record at which particular event happened can be beneficial for making precise restore sequences and to retrieve deleted data. LSNs are utilized internally at the time of RESTORE sequence to path the point in time at which the data has been reinstated. When data backup is restored, data is reinstated to LSN corresponding to point in time at which backup was taken. Differential, as well as log backups, advance restored the database to later time that corresponds to higher LSN.

Important: Do not do any modifications in production without testing it properly in lower-life cycle environments

Recover Deleted Records from SQL Server with LSN

Follow these steps to recover deleted records in SQL Server with successful SQL Log Recovery:

Step 1

Before moving further, we will make the database as well as the table on which one will run DELETE statement. Then, we will get the data deletion and then rollback deleted records in SQL. Implement the T-SQL code given below to generate database and table and retrieve deleted data.


Step 2

We have made a database with table 'Location' of three columns. Now, we will put few rows in table.


Step 3

We will go further to erase some rows and recover deleted records from SQL Server table by using LSNs from the SQL Server Transaction Log.

Step 4

 

Now, find the SQL Server Transaction Log and retrieve deleted records in SQL. Run the mentioned code below to get data information about all removed transactions with SQL Log recovery.

You can see the transaction IDs under which DELETE statement was executed. As we know that data is removed from Location table, we will focus on only those transaction IDs under which data is erased from location table to retrieve deleted data. We can get table name from "AllocUnitName" column. It states DELETE statement has been executed on HEAP table 'dbo.Location' under transaction ID 0000:0000055e. There is only one transaction ID under which we can view deleted records SQL Server, which means this action has been implemented in a single batch. Now, capture transaction ID from next command.

Step 5

 

We initiate that transaction ID from above script that we will utilize in below statement to find the LSN of LOP_BEGIN_XACT operation and recover deleted records from SQL Server. It means that we want an exact LSN when DELETE statement started its transaction to retrieve deleted data.

Step 6

 

We will utilize STOPBEFOREMARK operation to get back deleted records in SQL Server. We cannot utilize this hexadecimal value to implement the operation of STOPBEFOREMARK. Therefore, we want alteration this value into decimal format. Then put all these together as single number.

Step 7

Now implement transaction log backup on database where data was removed if transaction log backup has not competed since data deletion. Then, we will restore the database to other location or on a similar server with the variant name until above LSN. Then, we will import and recover deleted data in SQL Server from newly reinstated database to production database.

Step 8

 

Now, the database is restored, One can check whether you could recover and find deleted records SQL Server or not. Execute the below SELECT statement to preview the first ten rows of location table that was deleted and then retrieve deleted data.

Now you know how to retrieve deleted records in SQL Server with SQL Log Recovery. You can utilize this erased data as per your need. If you need to export this data to live database you can utilize the SQL Server Integration Services, i.e. import and export wizard.

SQL Log Recovery become Hard

Above steps can be useful in most of the cases. But several situation may occur when it becomes difficult to restore deleted data with SQL Log recovery. Lets take a look on these situation:

  • Changes made after the DELETE, TRUNCATE or DROP statments
  • Unable to take the SQL Server offline
  • New Transactions performed after the data was lost
  • No, recent Full Database backup available.

In such situation user can go with third party SQL Log Recovery to successfully restore deleted data records in SQL Server.

Conclusion

After understanding, the users problem how to recover deleted records from SQL Server 2014. In the above discussion, we have discussed the ways to retrieve deleted data from the SQL Server database by Successful SQL LOG Recovery that makes easy for users in restoring SQL Server deleted records in SQL version 2016 / 2014/ 2012 / 2008r2 / 2005 / 2002 and all below version.

Thursday, July 27, 2017

Two Ways to Restore Database from MDF File in SQL Server

Introduction

In todays decade Relational database Management system became the necessity of the organizations whether they are small level or large level. In such context SQL Server is the best and successful RDBMS which provides highly integrated services to manipulate the SQL database over the Server. As we talk about the SQL Server database the Server provides the primary storage call MDF File, NDF file also known as secondary file, LDF log transaction file. Well, in this section we will discuss about how to restore LDF and MDF files to a database so lets make some prerequisites perform tasks without any trouble.

  1. First of all detach the database. If you are trying to attach the database which is not detach you will get an error
  2. Maintain MDF File and LDF file in a specific folder before going to attach
  3. Locate MDF File and LDF File in the same location

There are two ways to restore database from MDF file in SQL Server:

  1. With the help of SQL Server Management Studio
  2. Using T-SQL

1. Through SQL Server Management Studio follow some steps:

  • Open SSMS and go to object explorer of the database engine instance
  • Then make a right click on the database and select Attach option of the list button
  • Windows appers, then click on Add button
  • Select the .mdf file and click on OK Button
  • Validate the selected files and go to OK Button

Now check the attached database

2. On using T-SQL

  1. Login to SQL Server database engine
  2. Go to New Query Window and use Create database with Attach close
    CREATE DATABASE MyDatabase 
    ON (FILENAME = 'C:\SQLServer\MyDatabase_Data.mdf'), 
    (FILENAME = 'C:\SQLServer\ MyDatabase _Log.ldf') 
    FOR ATTACH; 

Conclusion

In the above section, we learned how to restore LDF and MDF files to a database. It is highly recommended that you have to detach the MDF file before attaching a new one. This is a very helpful method to restore database from MDF file in SQL Server. The second method performs same task using T- SQL, for the users who needs to attach MDF file through SQL script. In case, if the transaction log file is missing then you can still attach MDF file in SQL Server without LDF file and if the MDF file is not accessible or corrupted then you can repair MDF file using third party SQL Recovery tools.

Saturday, September 3, 2016

How to Resume Suspended Database Mirroring in SQL Server ?

Overview

Database mirroring is a feature in SQL Server which is creating and maintaining the redundant copies of the database. The purpose behind database mirroring is continuous data availability and minimizes the loss of the data. Redundancy ensures that there is always a one backup copy of the database should remain accessible at the time of updates.

Basically mirroring feature in SQL Server is able to stand the random failures at the several parts when the user is linking between the two servers. It has some in built techniques which are not affected to the outside world. SO the SQL Server user can reboot the servers, connect and reconnect to the link and after that the database mirroring can still be resumed afterwards.

Causes for Database Mirroring Pause

  • No storage space in the primary storage.
  • SQL Server not working properly.
  • The main database is not connected to the mirrored database link.

So, an error is shown to the user while they are performing the database mirroring and all of the sudden it pauses.

Methods to Fix Database Mirroring Pause?

The above error occurs when the mirror server is running out of the space. The SQL Server user will be able to resume the mirroring as soon as the user frees some space. It means that there is no storage left in the primary storage, so in order to resume the database mirroring the user will check if they have any space in the secondary storage then they will free up all the primary storage and store it to the secondary database in order to resume the database mirroring.

Steps to Resume the Database Mirroring In SQL Server

Here are the steps to resume database mirroring using SQL Server Management Studio:

  • First, at the time of database mirroring connect to the principal server instance inside Object Explorer and then click on the server name to expand the server tree.
  • Second, expand the databases and click on the database.
  • Third, now right click on the database and select Tasks and after that click on Mirror. It will open the mirroring page of the Database properties dialog box.
  • Fourth, if the database is already pause then click on Resume button to resume the database mirroring.

Steps to Resume the Database Mirroring Using Transact-SQL

  • Connect to the database engine of the either partner.
  • Click on New Query for the standard bar.
  • Now Enter the following Transact-SQL query in order to resume the database mirroring:
ALTER DATABASE testdb SET PARTNER RESUME;

By the use of this command the user can resume the database mirroring when it was paused.

Conclusion

We discussed what is database mirroring and why it is important. In addition, we discussed about what are the causes for database mirroring pause and gave an example for it. We also gave some steps to resume database mirroring using any one of the two platforms. Database mirroring is an important thing as it backups the copy of the database and can be maintained easily at the time of loss of the data or loss of the database file.

Friday, January 1, 2016

Happy New Year Friends

Wishing all of you a very Happy New Year. May GOD bless all of you with lots of Success and Happiness.