SQL Server Error 3013: Restore Database is Terminating Abnormally

Overview of SQL Server Error 3013

One of the most frustrating situations that a user experiences while working with SQL Server is when errors are encountered. These errors disrupt the normal functioning of SQL Server and in worst case scenario may even lead to data loss. Sometimes, SQL Server Backup Detected Corruption In the Database Log. In this write-up, we are going to discuss about one such SQL Error 3013.

Error Description

SQL Server Error 3013 mainly occurs when the backup process to a disk/tape or a restoration process from a disk/tape is under process.

What is "Restore Database Terminating Abnormally" in SQL Error 3013 ?

The error message and the occurrence factors of SQL Server error 3013 vary according to the SQL Server version the user is currently working with.

SQL Server 7.0

In this SQL version, the error is encountered when the clustered index has been created in all the filegroups of the table.

SQL Server 2000

In this case, if the database of size 2 GB already exists and a separate database of size more than 2 GB is restored in the existing database, the error is generated.

SQL Server 2005

In the instance of restoration of both the data and log files at the same time, the Error 3013 is encountered.

Reason behind Its Occurrence

The dominant reason behind the occurrence of this error is the inability of the database to read the filemark present in the backup device or inaccessibility of the filemark itself. Before proceeding further, let us know what filemark actually is.

A filemark in a backup device is the element that does not store user data. In order to organize and unify the storage patterns, the filemarks, divide the partition in smaller segments. Their primary task is splitting data of all the individual files that are stored in the backup device.

The other reasons, which also play an important role in the occurrence of SQL error 3013, are:

• Media failure on the device in which the backup has been stored.
• Write failure whilst the execution of the backup process.
• Connectivity loss during the network backup process.

Workaround

1. The error can be removed by manual deletion of the device and ensuring that the server performs new backup to the device. The command to do so is:

2. In case the restoration process is the reason behind the error occurrence, it is possible to retrieve the other backup data by specifying the file number in the device.

In order to check the presence of multiple backup sets on the device, run the following code:

For indicating a specific set of backup, run the following command:

Note: The FileNumber term is used to represent the file series that the user wants to restore

Conclusion

In the blog, we discussed about the SQL Server Error 3013. With the help of the workarounds mentioned in the above section, the users can easily remove this error and continue with the restoration and backup process. In case if you failed with manual solution then you can also try SQL BAK recovery tool to successfully repair corrupt SQL .bak file.

Read More

A Guide for Decrypting SQL Server Database Objects

Overview

The SQL Server 2005 and SQL Server 2008 provide a new feature for encrypting data to protect it from unauthorized access or hacker’s attacks. To make the data accessible, it needs to be decrypted with the use of key or password. It is necessary to encrypt critical SQL Server data, connections and stored procedures while storing in the database and transmitting data between client and server across network.

The problem with encryption is that it is not a very secure way of encrypting contents of stored procedures. Most of users rely on source code that is stored in SQL Server instead of moving code to source control application. In order to access the code, we need to decrypt the encrypted database objects. In the article, we will be discussing the way to decrypt the encrypted SQL Server database objects.

How to Decrypt SQL Server Database Objects

The possible solutions to decrypt the encrypted SQL Server database objects are by using codes to decrypt or by using third party tools designed to decrypt the database objects.

Encryption

Firstly, Let us see how encryption of database objects works: SQL server database objects like stored procedures, views, and functions can be encrypted using ‘WITH ENCRYPTION’ option to hide the contents of the database objects from unauthorized access. Specifying WITH ENCRYPTION will prevent unauthorized access, as data will be stored in system table in encrypted form.

If we try to access the content of the encrypted stored procedure using: Error will be generated ‘the object comments have been encrypted’.

Decryption in SQL Server Database

Firstly, we need to open a Dedicated Administrator Connection (DAC) to SQL Server, which will give access to tables and views. It is done by prefixing admin to connection string upon connection of query windows using SQL Server Management studio. DAC mode can only be used if you are logged onto the server and using a client on that server and if you hold the sysadmin role.

The Process of Decryption Comes in Following Steps:

Get the encrypted Definition

The system table where the actual definition is stored is called sys.sysobjvalues and it is not directly accessible. We will access it using DAC mode. The information that we want to retrieve is stored in a VARBINARY (MAX) column called imageval

Use XOR decryption process

During encryption, SQL Server takes the bytes in imageval column and applies bitwise XOR with a bye pattern (called key pattern) on it. XOR is fully symmetric which means if bit pattern A & key pattern B are used, XOR result is A^B=C then, C^B=A is also true. That is we just need to XOR again with same pattern key to decrypt the data.

SQL server uses a pattern generated by object_id and object_type of encrypted object. If we can use the SQL Server to encrypt the object, we can use the same object_id as the object to decrypt by calculating the key pattern using XOR, which will combine plain text and encrypted text. We need to alter encrypted object by temporarily replacing it with known object, grab sys.sysobjvalues.imageval value, and undo the replace operation, which is possible by wrapping all this in a transaction.

We need to get key pattern first and the plaintext of object. Since XOR of two VARBINARY (MAX) values is not possible, we can use loop through the bytes one at a time to apply XOR manually.

Alternatively, the code given below can be used for decrypting SQL Server database object Stored Procedure:

Inside the code, the procedure uses name of object as parameter that needs to be decrypted. It detects the object type first and creates ALTER statement for it. It fills the beginning of statement with enough spaces to cover all cipher text. Then, procedure replaces object inside transaction and capture cipher text of the known plaintext. Lastly, the XOR operation will be carried out in a loop and then return the decrypted object definition.

Conclusion

In the article, we have discussed about the encryption and decryption of SQL Server database objects. In order to decrypt the encrypted SQL Server database object, the most difficult task is to get access to encrypted data. With the help of DAC, we can access the data. Once we get the encrypted data, we can decrypt using key pattern that can be calculated using plaintext and cipher text of known object of same type and with same object_id. If the methods discussed above fail to decrypt SQL Server database then you can try third party commercial tool.

Read More

Forensic Analysis of Log files in SQL Server

The Importance of Log Files in SQL Server

On the SQL server every day we perform many transactions and as we know that all these transactions affect the log file. After all, the transactions are stored in the log file so every transaction contains some space in the log file so it is the responsibility of log file to manage all the transactions in SQL server. The other very important thing is that it is next to impossible to keep in mind all the transactions performed on the database. So SQL server provides such a facility that we do not need to remember the transactions rather, we give them space to store and retrieve from there. If necessary, we can know which transaction took place when we had performed a task, so that we can easily understand all the transactions.

But sometimes we have to face some difficult situations where the database becomes inaccessible due to technical failure or human errors. It is a major problem with SQL server, where we are performing a task on SQL server and sudden a power failure takes place. In such condition, there are so many chances where a log file can corrupt also affecting the transaction logs. The SQL server database becomes readable to these queries, but it depends on us as to which recovery model we have chosen.

Why We Use Log Files for Forensics?

As we know, the log files hold the transaction logs in SQL server so that if necessary we could use these transaction logs to get access to the corresponding data. Another important thing is that the size of a log file does matter a lot in SQL server. It is so because if a third person tries to misuse our database and the size of the log file has increased after this; and if we forget to remember the size of the log file and want to know what activities were performed by a third person, then we can take into account the activities logged into the log file to detect these unknown activities.

Another reason for using log files for forensics is that we can detect those records that we have deleted accidentally

When We Need the Log Forensics?

In some cases a log file is also needed for forensics as a log file is made up of the transaction logs. Thus, it is very important to focus on those transactions which make changes in the database. Such transactions are delete, update, insert or drop. These are DDL and DML statements and can change the database. So a third person can easily change our database if we have not applied any security to the database. In this case, it is very important for us to check the last performed actions. After the forensics, we will get to know where were the changes made in the database.

How to Detect Deleted Statements in SQL Server?

It happens many times that when you're dealing with your database and accidentally performed any task such as dropping a table, deleting a record or if a third person has accessed your database and performed some DML statements, In such case, there is no need to worry about it, the SQL Server uses some undocumented commands, through which you can detect all the transactions performed by you or by a third person.

Where to Begin the Detection?

To start the detection we will follow some basic steps:

1. At first, we will create a database

 ---Create database----
     create database readingdblog;
     GO

In the first step we have created a database named it ‘readingdblog’

2. After creating a database we will create a table in it.

 ---Create table---
       use readingdblog;
       GO
       create table person
       (
       sn int identity,
       name varchar (30),
       city varchar (50)
       );

In the second step we have created a table named person with two columns.

3. After creating a table, we will now insert a record/row into the table.

---insert rows---
        use readingdblog;
        GO
        insert into person values('alen','new york');
        GO

After inserting a record we have to delete this record for the detection.

---Delete row---
        use readingdblog;
        GO
        delete from person where sn=1;
        GO

4. After deleting the record, we will now find the info about deleted record. Run the below command to find info about deleted records.

     USE ReadingDBLog
     GO
 SELECT 
     [Transaction ID],
     Operation,
     Context,
     AllocUnitName
 FROM 
    fn_dblog(NULL, NULL) 
 WHERE 
    Operation = 'LOP_DELETE_ROWS'

Using fn_dblog function, we can see the deleted statement which we had performed. As we are looking for deleted records in person table; we can see the dbo.person in AllocUnitName column. The above output shows that a delete statement has been executed on a HEAP table ‘dbo.person’ under transaction ID 0000:0000031b. Now we will capture the transaction Id for our next step.

5. Now we will use this transaction ID to find out some more information about the deleted record(s).

    USE ReadingDBLog
    GO
 SELECT
    Operation,
    [Transaction ID],
    [Begin Time],
    [Transaction Name],
 FROM
    fn_dblog(NULL, NULL)
 WHERE
    [Transaction ID] = ‘0000:0000031b’
 AND
    [Operation] = 'LOP_BEGIN_XACT'

After executing the fifth step we can see the [Begin Time] of this transaction, by which we can easily detect the time at which a transaction has been performed and also we can see the name [Transaction Name] of this transaction.

Now we can see the delete statement began at 2015/01/10 05:20:28:623 under transaction ID 0000:0000031b

Conclusion:

The above detection method shows how we can detect accidentally deleted records and also if an unauthorized person misusing our database. So in that situation we can apply these above steps to detect all the transactions which he has performed. But the fact is that the procedure is so lengthy and time consuming and another thing is that if you don’t have the SQL server you cannot use this detection method. In such case you need to have a third party expert tool like SysTools SQL log analyzer for forensics purposes, which saves time and gives a satisfactory result to you.

Read More

Top Five Backup and Recovery Plans for SQL Database

Introduction

Creating a good backup and recovery plan for SQL database is a very important task of a database administrator. A Database administrator must have the knowledge about the troubles of SQL Server and also a quick strategy, which is suitable for the trouble. Many organizations hire accidental database administrators instead of database backup. Sometimes a bad configured backup plan can be the cause of a data loss. In this article, we will discuss the backup and recovery plans for SQL database.

Why we need a backup and recovery plan?

Sometimes, while working on a SQL Server you may face so many difficulties. One of which could be data loss. For example Network disruptions, low disk space or disk failure so on. Such type of difficulties can arise at any time in our machine or database. Although, If you have already created a good recovery plan or a strategy like a database mirroring, In which another server keeps a copy of your database and keeps it up to date. Afterall you may need to have a backup of your database. Because a disk failure on mirror server can stop the transactional process between primary and mirror database and that hiccup can corrupt the primary database and can put it in suspect status. In such situation, we cannot bring our database back.

Recovery techniques for SQL database

(1) Restoring a database from another SQL Server

The SQL Server provides the feature to restore your database from another SQL Server database where you have already created a backup copy of your existing database. SQL Server performs such tasks by using some T-SQL commands. for example: RESTORE HEADERONLY

The RESTORE HEADERONLY command is used to check what is in the backup file. See how to implement this command:

RESTORE HEADERONLY FROM DISK = 'C:\SQL\Backup\bkp.bak'

(2) Restoring a database on the same server

You can use enterprise manager to restore the database from the same server. The enterprise manager allows you to backup and restore your database.

To allow this service, Go to Enterprise Manager, then right click on the database (that you want to backup and restore) and select All Tasks. You will have a database Backup and Restore options.

(3) Restore using T-SQL commands

Apart from restoring the database from enterprise manager, another handy option is to restore the database from T-SQL command, by which you can create a script to your backup and also you can restore it using T-SQL script.

You can perform operation by using the following T-SQL commands.

RESTORE DATABASE databaseName

RESTORE LOG databaseName

(4) Database mirroring

One of the evolving feature, which came into SQL Server 2005 is Database mirroring. Which allows you to create a mirror image of your database on another server and automatically transfer it from the primary Server to secondary Server and also provides the feature to failover capabilities.

The failover processing of database mirroring depends on your choice that how you have configured it in your database. These are:

1. High availability
2. High protection
3. High performance

(5) Recover lost SQL Server data

All the above methods render you to have an unnecessary dataset, means that data which you can only use when your primary database may have been lost. But these methods may not useful when someone by mistakenly deletes a record from a table and you only have to recover that specific record. SQL Server provides some failover methods to recover lost data. i.e point-in-time restore, Log shipping, Clustering, Transaction Replication. However, each method has some of its pros and cons, which I will discuss in my next blog.

Conclusion

The article describes simple backup and recovery plans for SQL Database, in which we have discussed why a backup plan is important at the time of disaster and also discussed some recovery methods which helps we can recover our lost database. Creating a backup plan for the database is a good idea to prevent data loss.

Read More