Sunday, April 12, 2015

How To Deal with Error Message 7105 in SQL Server

Microsoft SQL server supports LOB (Large Object) data types to store the very large amount (Maximum size is 4 Gigabytes) of data. These data types are CLOB (Character LOB), BLOB (Binary LOB) and DBCLOB (Double-Byte Character LOB).Structures of these data types are different from regular data types. Sometimes SQL server generates Error 7105 due to LOB referred by database page can’t be accessible. Continue reading this article, you will find resolution for this.

Error Message 7105: When error 7105 occurs in SQL server then we can see the following error message on the screen-

Msg 7105, Level 22, State 6, Line 1
The Database ID 11, Page (255:177), slot 1 for LOB data type node does not exist. This is usually caused by transactions that can read uncommitted data on a data page. Run DBCC CHECKTABLE

Note:In this error Severity Level is 22 which mean connection is terminated by the server.

Reasons of error 7105 in MS SQL: This error can be occur by the following reasons –

  1. Database corruption issue lies within a database page or LOB (Large Object) structure.
  2. The query that fails with READ UNCOMMITED ISOLATION or NOLOCK query hint.
  3. When SQL server engine causing the query to fail with the same error.

Solutions: You can use the following methods to repair SQL Database-

  1. Run DBCC CHECKDB against the database or DBCC CHECKTABLE against the table where the problem was encountered.
  2. If the problem is linked with the database query then rewrite and rerun the same.
  3. If the problem is related to SQL server engine then update the server with latest service pack.
  4. Restore the .mdf file from the last backup. 
»» 

Tuesday, December 30, 2014

How to Fix SQL Server Error 8928

SQL server database corruption is a like a worst nightmare for every SQL server database users. For any organization; small to big, database integrity and availability is the most important part. Database administrator (DBA) is responsible for it, if you are a DBA of SQL server database then it’s your responsibility to manage the database and make a proper disaster plan to handle any kinds of database corruption scenarios. In this article, we will learn about a particular error message that appears during selection of data from a table.

Select data from table;

Select * from My_table_name

But received below error message

Msg 8928, Level 16, State 1, Line 55
Object ID 245575913, index ID 0, partition ID 72057594040549376, alloc unit ID 72057594045792256 (type In-row data): Page (1:288) could not be processed. See other errors for details.
CHECKDB found 0 allocation errors and 4 consistency errors in table '[Table_Name]'
(object ID 1326627769).
repair_allow_data_loss is the minimum repair level for the errors found by DBCC CHECKDB

As the error message indicates, there is a corruption in the specified database. So don’t use the DBCC CHECKDB command with repair option first because you will loss amount of data from the database after executing it.

Restore from backup: It is suggested to restore the database from clean & updated full backup. It is the best solution to handle any kind of SQL database corruption.

Note: Before using your backup, you will need to verify it. ‘Restore verifyonly’ command checks the SQL server database backup and ensures that you are restoring your database from consistence backup. While restore only command ensures that backup is complete and can be used in all format.

If you don’t have updated backup then you can go with Repair command but you will need to put your database in single user mode first.

ALTER Database Database-Name SET SINGLE-USER

Now you can run DBCC CHECKDB with repair_allow_data_loss on the database.

DBCC CHECKDB (Database-Name, Repair_Allow_Data_Loss)

After running this command; you will get a message on your screen.

The error has been repaired.

Conclusion: It is very hard to recover data from corrupt database files without backup. Sometimes corrupt database file can be repaired, sometimes not. So always make a backup of database and regularly update it. Make this habit and add in your daily routine. Most of the times it has been observed that SQL database data lost during manual repairing process, to avoid this situation DBA’s can use some tools to repair corrupt SQL database.

Note: ALL third party SQL database repair software are paid, there is no free tool available
»» 

Wednesday, November 26, 2014

Create Access Database Backup – Manual & Automatically

You should regularly backup your database to safeguard your data from any uncertain data loss situations. Backup saves your money and precious time invested on the database to build it. Without backup, it’s like nightmare to recover data from loss or corrupt Access database file. An Access database gets corrupt or damaged to several reasons, as explained here and you can easily repair corrupt database by reading this article. If you are database administrator then it’s your responsibility to take backup of database on regular time interval and make sure that backup is working perfectly. Time interval for backup update depends upon your business requirement need.    

Backup an Access Database: You can create backup of an Access database manually or automatically. Manual backup creation of database is very easy and essential; there is some 3rd party software that performs automated backup of file system. You can also use DropBox directory for online backup. To do this, you need to copy the database file into your DropBox directory. If you don’t know about this (read here), it is a great piece of software that synchronizes the files & folders on your machine to the Internet (Online).   

Create Database Backup Manually: It is very easy and quick process. It just takes around 30 minutes to accomplish the backup process and these 30 minutes can save your valuable time and some dollars. Please note that backup process time duration may be higher for the larger database. Following steps are required to be done to create a backup of an Access database. 

  • Open your Access database that you want to create backup.

  • Click on the Office button and go-to the ‘manage’ tag.
  • Click on the ‘Back Up Database’ from ‘Manage this database’ tab.

  • Now ‘Save as’ dialog box will be open. Choose the location where you want to save the backup and review the name of database, by default MS Access named the backup as database name with the date and it is the good idea. It helps during the database restoration process. If you want to change the database backup name then change it. It’s totally depends upon you.
  • Done 
Create Database Backup automatically when Database is Open: Follow below steps to accomplish the automatic backup of your database.
  1. Create a table within your Access database and named it as BackupDetails.
  2. Add a VBA module with code.
  3. Now create macros that automatically run when Access database is loaded. To create macros, see the follow the following steps.
Create Tab ->Macro->Named the Macro

Important Tips:
  • Add database backup as a regular task in your daily routine.
  • If your database contains sensitive information then encrypt it with password.
  • For safely purpose, make an offsite backup of your database.
»» 

Friday, October 17, 2014

Backup Encryption in Microsoft SQL server 2014

Microsoft releases newer version of SQL Server that is SQL server 2014. It is very close to the previous version of SQL Server 2012 release. For many, it is very hard to upgrade the newer version of SQL server because it requires money & resources. However, the newer version of SQL Server has many new features for organizations that can justify the cost and upgrade. The most notable features in SQL server 2014 are In-Memory OLTP, enhancement in Always-on, and enhancement in backup.

In this article, we will learn and concentrate on backup enhancement in SQL server 2014.

SQL server 2014 supports in-built backup encryption for the database. All the previous SQL server versions did not support database backup encryption that required a third party software to accomplish this task. With SQL server 2014, DBAs can encrypt the database backup while creating the backup of database. It can be accomplish by specifying the encryption algorithm.

Benefits of SQL Backup Encryption:
  • It helps in securing the data of SQL server database backup. When you try to create a backup of yourdesired database, SQL server 2014 will provide the option for backup data encryption.
  • It supports multiple encryption algorithms: AES 128, AES 192, AES 256, and DES up to AES 256 bit.
  • It is helpful for all the SQL server databases that are encrypted using TDE.
How to Encrypt SQL Backup: You can encrypt the SQL database backup during backing up the database by following methods:
  1. Maintenance Plan Wizard
  2. Using T-SQL
Maintenance Plan Wizard: It creates maintenance plan that is used by database agent on regular basis to perform the task. You can use it to perform database administrative tasks like integrity check, backup, statistics update, shrink database, cleanup history, rebuilt index, recognize index, and many more,  when you required.

Perform following steps to encrypt SQL database backup with Maintenance plan wizard.
  • Right click on the desired database, you want to create backup.
  • Now select ‘Tasks’ and then ‘Back Up’, a backup dialog box will be open.
  • Right click on the ‘Backup Devices’ and select ‘Back up a Database’.
  • You are on Backup option page, Tick ‘encrypt backup’ as shown in the below image.
  • Now select your desired backup algorithm & certificate key from the drop down list.
  • Click on the ‘OK’ button to accomplish the backup encryption operation.

Using T-SQL:

You can easily encrypt the backup of SQL server database with T-SQL during backup creation. Run below T-SQL command to accomplish the task:

TO DISK = D'\\ServerLocation\Backup\Database-Name_backup.bak'
,ENCRYPTION (Algorithm-Name, SERVER CERTIFICATE = Key or Certificat)
,STATS = 10 GO


It is very important to make a backup copy of your ‘Certificate or Asymmetric key’. During backup restoration or for attaching purpose, certificate or asymmetric key is required. Without it, you can not restore or attach your database on another server.
»» 

Monday, September 29, 2014

How to upgrade lower version of SQL server database to upper SQL server versions

The most important question severally asked by clients or junior DBAs, why should they upgrade to newer version of SQL server?

Upgrading to newer version of SQL server requires money, resources, time, training, and many more. So first decide you will have to need all these things when you are planning to upgrade to newer version of SQL server. As Microsoft stated SQL server 2005 is out of mainstream support that means you are not getting any security upgrade, support for SQL server 2005 & all lower versions. If you are still using SQL server 2005 or any lower version then I recommend you to upgrade latest version of SQL server that is SQL server 2014. You can get SQL server 2014 standard edition from here.

If you are upgrading to New version of SQL server then you are not going to get security updates only, you will have so many newer features in the newer versions like AlwaysOn, High-Availability, New features in database engine any many more.

If you have decided to upgrade to newer version of SQL server then don’t worry about your older database!!!

I will tell you different methods to upgrade your older databases on newly upgraded SQL server with step by step guide.

You can upgrade your lower version of SQL server database to newer version by any of the following methods:
  • Copy Database Wizard Method
  • Detach and Attach Method
  • Backup and Restore Method
Copy Database Wizard: Copy database wizard helps you in upgrading your SQL server to later version of SQL server. If you have chosen this method to upgrade later version of SQL server then adhere the following requirement:
  • No-one is trying to access the database during copy database wizard.
  • Don't try to rename your database during operation.
Here is step by step guide to perform database upgradation through copy database wizard method:
  • Connect to instance of database engineusing object explorer in SSMS.
  • Click Databases ->Right click on database -> Click on Task -> Click on Copy database.
  • Finish all the steps in database wizard.
After performing above steps, your database is available and upgraded automatically.

Note: To verify optimal performance of upgraded database, runs stored procedure (sp_updatestats) against the newly upgraded database.

Detach & Attach: It is another method to upgrade older SQL server database on newly upgraded SQL server. The database is immediately available & automatically upgraded to the newer version after performing Attach & Detach operations on the database.

Before using this method, please adhere the following restrictions:
  • Master, Model & MSDB databases created using lower version of SQL server cannot attached in the new version.
  • Make copy of data & log files. It helps in reattaching the original database on instances of original SQL server versions.
Here is step by step guide to perform database upgradation through Detach & Attach method:
  • Detach the SQL server database from instance of SQL serverby using stored procedure sp_detach_db.
EXEC sp_detach_db 'Your_Database_database;
  • Copy & move the data and log file of detached database.
  • Attach the copied data & log file to an instance of upgraded SQL server version using create statement of SQL server with for attach or for attach rebuild log options.
Note: You have the copied of log file then use For Attach option otherwise use For Attach-Rebuild-Log Option.

Backup and Restore: It is the best and easiest method to upgrade a SQL server database from lower version to later version of SQL server. For this, you have to just take the full backup of SQL server database (Read my previous article on: how to take full backup of SQL server database) from lower version and restore it on the newer version of SQL server.

Note: It is the preferred method by most of the DBAs for upgrading lower version of database on later version of SQL server.

Now point comes, what is the difference between Detach/Attach and Backup/Restore method?

As I have described above most the expert DBAs recommend BACKUP/RESTORE method to upgrade a database from lower version to higher version. It helps in disaster recovery scenario because you have backup of your database at some location. But this is not absolute, because if you want to upgrade a very large database from lower version to higher version then backup & restore method may take a lot of time which is very frustrating. At this situation, detaching/attaching a SQL server database is a better way to upgrade a database. Detaching & Attaching operation is very fast but you need to aware that detaching a SQL server database will bring your database offline for a short time. And it does not provide disaster recovery option also.
»» 

Monday, September 22, 2014

Tips to Recover Data from Corrupt MySQL Database

Stellar Phoenix Database repair for MySQL can help in recovery of data from corrupt or damaged MySQL database. In this article I will show how to repair corrupt or damaged MySQL database’ file when it becomes corrupt.

Corruption in MySQL database can happen due to any uncertain reasons. Hardware problem, virus attach, power failure, bugs in MySQL itself, automatic system shutdown are the most common causes for MySQL database corruption. A bad hard drive can write garbage value in the MySQL database and then MySQL reports error messages.

“Cannot load from mysql.proc. The table is probably corrupted”
“MySQL error code 145= Table was marked as crashed and should be repaired”

There are several ways available to recover data from corrupt or damaged MySQL database:

InnoDB Force Recovery

It helps in recovery of data from corrupt MySQL database. By default, 0 is the value of innodb force recovery and database administrator can change it from 1 to 6. A higher value of InnoDB force recovery includes all the functionalities of lower values. For Example, 6 includes all the functionalities of values 5, 4, 3, 2, & 1.

Note: Only use InnoDB force recovery value greater than 0 when there is an emergency situation and make a backup copy of your database before trying it. Backup will help you in recreating the database when needed.

Steps to use InnoDB force recovery:
  • First of all stop MySQL server (mysqld) and copy all the files from /var/lib/mysql.
  • Use innodb-force-recovery to mysqld.
  • Start the MySQL server.
  • Dump all the databases.
  • Now restore the database from dump.
Restore from Backup

If the above method does not help then try tested & updated backup. It is the ideal solution to recover database from corrupt database file. Go with the next method, if backup is not available or too old.

Stellar Phoenix Database Recovery for MySQL

The only remaining method is use Stellar Phoenix database repair for MySQL software if above solutions fail to restore the database from corrupt MySQL InnoDB database.

Stellar Phoenix database repair for MySQL is developed by Stellar data recovery and the current version is 4.0. It is available for Windows as well as for Linux but you need to copy your MySQL database from Linux machine to Windows machine and run repair operation over there then after restore the database from Windows machine to Linux machine.
»» 

Wednesday, July 30, 2014

Tips to Repair Corrupt SharePoint Database

Application started reporting an IO error in the SharePoint database, and end users started getting below error message:

Cannot complete this action. Please try again

Solution: If you have encounter above error message in the SharePoint database then try following methods to fix it.

Try command line tool - Databaserepair: Stsadm operation
It detects & deletes orphaned items from corrupt SharePoint content database. SharePoint content database can be corrupt or damaged due to any uncertain reasons like hardware failure, power failure, virus attack etc. and corrupted SharePoint database may contain orphaned items. An example of orphaned item is a document may not have parent document.

Here is the syntax of Databaserepair - Stsadm operation:

Run follow syntax to detect an orpahned site
stsadm -o databaserepair -url <url name> -databasename <database name>
Example: stsadm -o databaserepair -url <> -databasename <my first site>
Run follow syntax to detect an orpahned site
stsadm -o databaserepair -url <url name> -databasename <database name>[-deletecorruption]
Example: stsadm -o databaserepair -url <> -databasename <my first site> -deletecorruption

Force PowerShell to Repair Content Database
To do this, you have to load the SharePoint management shell and run following equivalent powershell command:
$db = Get-SPDatabase "Content_DB_Name";

If both the above methods fail to repair corrupt SharePoint database then check the consistency of your database by disconnecting it from the SharePoint. You can detach a content database from SharePoint using “Deletecontentdb: Stsadm” operation. It detaches a content database when database name, server and web application are specified.
Here is the syntax of Deletecontentdb - Stsadm operation:

stsadm -o deletecontentdb -url <url name> -databasename <database name> [-databaseserver] <database server name>

Now, follow below steps to check the consistency of database after detaching it:
  • Login into SQL server
  • Go to the SQL server management studio
  • Run following command:
  • You will get error message details & recommendation to fix the error message.
  • Re-run DBCC CHECKDB with suggested repair clause (repair_fast, repair_rebuild, & repair_allow_data_loss).
Note: Some amount of data will be lost after performing repair_allow_data_loss, and the data loss is directly proportional to the level of corruption.
It is a 3rd party recovery software that easily detects & repairs corrupt SharePoint database. Software does not alter the content of database because it is read only in nature.

»» 

Sunday, June 29, 2014

Is Your SQL Server Database Need to Repair?

Run below command to check whether your SQL server database needs to be repaired or not

DBCC CHECKDB (Database-Name) with no_infomsgs

  • DBCC CHECKDB: It checks all the logical & physical integrity on the specified SQL server database.
  • Database-Name: Just replace the 'Database-Name' with your database name for which to run DBCC CHECKDB integrity. If you not specified the database name then current database will be used.
  • No-Infomsgs: It will remove all the information messages

If above command executed without any error message then there is no need to repair SQL server database but if there is error message(s) then you need to repair your SQL server database.

Note: If error message appears with DBCC updateusage then you need to run DBCC UPDATEUSAGE command on the database.

DBCC UPDATEUSAGE: It detects and corrects all the table related problems like rows, data page count, reserved page, used page etc. in specified database. Run below command to resolve table related problem in the database.

DBCC UPDATEUSAGE ({database_name} {table_name}) with no_infomsgs

If appears error message does not contain dbcc updateusage then you need to repair your SQL server database.

Why Need to Repair?
You can see the reasons from the Windows system & application event logs. SQL server logged possible cause for the problem in the windows system logs.

Suggestion: I will recommend you to check your database backup before repairing the corrupt SQL server database. If Backup is clean & updated then restore it.

How to Repair?
If backup is not available then you need to repair your SQL database. SQL server provides following repair options:
  • Repair_Rebuild: SQL server suggests it when there is no any possibility of data loss from the specified database.
  • Repair_Fast: SQL server suggests it when no repair is performed
  • Repair_Allow_Data_Loss: It will try to fix all the error messages with possibility of some data loss.
To perform any of the repair options, you need to put your database in single user_mode:

ALTER Database <database-name> SET Single_User

Here is the syntax of all repair options:

DBCC CheckDB ([YourDatabase], REPAIR_REBUILD)
DBCC CheckDB ([YourDatabase], REPAIR_FAST)

After repairing your database, don't forget to set your database in multiple user_mode. You can do this by running following command:


What to Do after Repairing the Database?
After repairing your corrupt SQL server database, you need to do following things:

  • Re-Run DBCC CHECKDB: It is very important to re-run DBCC CHECKDB command on the database after repairing. If it comes out without any error message then it is conform that there is no any inconsistency in the database.
  • Make Full Backup of Database: If DBCC CHECKDB comes out cleanly then take a full backup of your database and keep it at safe place. Backup will help you in restoring of database.
  • Test Your Backup: Verify your backup by restoring it on another server. Successful restorations can only guarantee that your backup is consistence.
  • Make Disaster Recovery Plan for Future: Make a proper disaster recovery plan for future.
»» 

Wednesday, June 18, 2014

Save $549 on SQL Toolkit bundle from Stellar Phoenix

Stellar Phoenix, a pioneer in data recovery software & services now offering SQL toolkit bundle for all SQL server DBAs. SQL toolkit bundle has three essential tools that help you in recovery of corrupt SQL database, backup file and forgotten password.[300624711]=1&COUPON1=SQLTULK-EN&HADD[300624711][ADDITIONAL1]

Tools in SQL Toolkit Bundle:

Stellar Phoenix SQL database repair tool repairs corrupt or damaged SQL server database files (.mdf & .ndf). Software supports SQL server 2012, 2008 R2, 2008, 2005, & 2000.

Stellar Phoenix SQL Backup Recovery tool safely restores corrupt or damaged SQL database backup file into a new database.

Easily recovers lost or forgotten password of SQL server system administrator (SA) and all users.

You save 50% on the purchasing of all three essential tool individually.
So don't wait, buy & Save $549
»» 

Tuesday, June 17, 2014

Is Full Backup is Sufficient to Recover SQL Server Database?

Data is very important for everyone, ranging from individual to big organizations. For me, data is my life. I can’t think, what will be my life without data. So I regularly manage and update data. If you think that your data is also important for you as of mine then you should also manage and update your data.

What is Data?
Data can be anything like list of records, phonebook, measurements, survey records, employee records or even a description of any things. In the other words, you can say that data is a set of records.

How to Manage your Data?
Creating copy or copies of your data is the primary method for data management. Just create copy of your important data and store it on external hard drive, pen drive, or any other removable media so that you can recover any lost or deleted data from backup copy. Fortunately, Microsoft SQL server provides backup & restore facilities for data management.

SQL Server Backup
Backing up of your SQL server database is very important for protecting your database from data loss. SQL server facilitates for backing up transaction log, whole database, partial database or sets of files & filegroups. For these, SQL server offers following backup techniques:
  • Tranaction Log Backup: It records all the changes performed in the database that were not available in the previous log backup. To create a transaction log backup, you need to create at least one full backup of your database before. I recommend you to take a transaction log backup of your database frequently that will minimize data loss exposure and truncate the old transaction log.
  • Full Backup: It is also known as database backup because it backs up whole database including transaction log. It can be performed on the basis of your business need. Full backup contains the data at the time of full backup finished.
  • Differential Backup: It is faster in comparison of full backup because it contains the data since last full backup.
  • File Backup: It backs up one or more SQL server database files or filegroups. You can backup whole filegroup instead of individual files.

Data Loss, it can happen to anyone
You should always prepare of it. It can happen with anyone at anytime. So it is very important of everybody to prepare of it and make a suitable disaster recovery plan to avoid data loss and minimize the business downtime when data loss event will occur.

So now you may need to make a full backup of your SQL server database. You can create full backup of your SQL server database by using SQL server management studio, transact-SQL, or powershell.

Is Full Backup is Sufficient Enough?
If you have a read-only SQL server database or data in your SQL server database is very static then full backup is sufficient for you. Possibly you may recover your whole database with the help of full database backup.  But think differently, your database experiences frequent modification regularly then full backup is not sufficient for you because it does not contain data after full backup. To recover every bit of your data, you need to create other backups such as differential or transaction log backup. I have already discussed about these backups in the above paragraphs.

What is the role of recovery model in transaction log backup?
SQL server facilitates database administrators to manage their transaction logs through recovery model. There are three types of recovery model in SQL server: Simple, Full & Bulk-Logged. A database administrator can take backup of transaction log when his/her database in Full or Bulk Logged recovery models but this is not possible in simple recovery model.

Points to Ponder:
  • Make backup of database
  • Data is key to success
  • Regularly update database backup
  • Different types of backup required to recover database
  • Simple recovery model does not allow for transaction log backup
I hope that you have enjoyed to reading my article. As always, share your thoughts through comment.
»»