Thursday, May 21, 2015

Save More than 35% on Purchase of Stellar Database Repair Bundle

Recently I saw a discussion on forum about database recovery tool and I was very surprised to discover that many people do not think that these tools actually work. Sometimes when a user tried many options to recover and repair the database and unable to do it then, a third party tool can be the ray of hope. Stellar Data Recovery presents a database bundle which is tested by the team of experts. The most important thing for any tool is a reliable source which can provide useful software. This blog is about Database Bundle which includes Database recovery software for SQL Server, MySQL and Oracle. For many businesses, a database recovery tool plays a major role in their work and these tools are safe and secure.

This database bundle consist three tools:

Stellar Phoenix SQL Database Repair: It is a perfect tool that facilitates complete recovery of .MDF and .NDF file ofthe SQL server database. It supports MS SQL server 2014, 2012, 2008 and all lower versions. This tremendous tool recovers tables, triggers, keys, indexes, rules and constraints. It has the ability to self-reconnect in case of interruption during data recovery process and capable to repair much kind of corruption errors like: suspected database, consistency corruption, possible schema corruption and clustered index leaf node corruption. 

Price: $599

Key Features:
  • Recovery of foreign keys, primary keys, unique keys and identity.
  • Recovery of triggers, tables and view.
  • Supports sequence objects.
  • Supports ROW compressed and PAGE compressed data.
  • Supports XML data types and XML data indexes.
  • It allows finding of object name in the tree view.
  • It shows the preview of the recoverable database object.
Stellar Phoenix Database Repair for MySQL: It is a professional MySQL database recovery tool that carefully repairs corrupt MySQL databases to recover valuable objects such as primary key, triggers and views from it.Software supports MySQL versions 6.x, 5.x, 4.x and 3.x versions.

Price: $449

Key Features:
  • Customizable user interface.
  • It repairs all data types except spatial data types.
  • It also repairs MySQL database created on Linux platform.
  • Shows the details of ongoing process in a message log pane. 
Stellar Phoenix Oracle Database Recovery: Oracle database recovery process can be very straight forward with this tool because this tool perfectly repairs a corrupt.DBF file created in oracle 9i, 10g and 11g. It searches all Oracle databases present in the system and enables preview of recoverable database objects. Moreover, the tool creates the log report of the recovery process. A heavily damaged database can be optimally repaired and restored by this amazing tool.

Price: $799

Key Features:
  • Recovers schemas, triggers, roles, index, synonyms and sequence.
  • Recovers database query that links in .txt file.
  • Recovers cluster and cluster tables.
  • Recovers stored-procedures and user-defined data functions.
  • It is able to recover nested table, nested array type, nested object type etc.
  • It simply recovers package and package body.

»» 

Wednesday, May 13, 2015

Why Regular Consistency Check is Important?

Consistency Check always plays a very important role in every database administrator’s life.The number of individuals does not like to check it on the daily basis.For a regular user, this is the way of continuous checking of database corruption, which can happen due to numerous reasons and no-one can predict it. It checks the logical and physical integrity of the database objects in the specified database. The best point is that it also recommends suitable repairing options depending upon the level of corruption for repairing the selected database. Now the million dollar question comes in very DBA mind; what is the best suitable time-interval to run this consistency check on the database.

When a user should run consistency check:Well it depends on the situation. Here I have explained two examples.

  • Suppose you have an unreliable I/O subsystem and your database is running on the same machine then it is indicating that your database is on high risk. A database might be corrupt or damaged anytime. Running consistency check at the earlier stage helps you in finding the problem as it starts. A minor corruption can cause a big data loss from the database. 
  • Suppose you have an accurate I/O subsystem, with up-to-date drivers and firmware. You have an inclusive backup strategy which you have tested and you have full confident that, it will recover from the data corruption with minimum downtime and data loss. In this case a user may be comfortable with consistency check once in a week. 
The important factor is that, how many times user runs the consistency check and how much comfortable with I/O subsystems and capability to recover from corruption problem? Loss of integrity in your database today then, User probably runs DBCC CHECKDB daily.

Note:If a user does not aware about consistency check and its benefits; it can be digestive but, If the user is so much aware of it then, the consistency check is always expected. Even if you think that you cannot run it because it takes too much time/resources etc.

Detect corruption on other way:When a database corruption detected by the regular operation. It has been noticed that many databases do not use (read/update) their many parts on the daily basis. It means the unused parts of the database got corruption and user is not aware of it because user is not running the consistency check to know the problem. It means that users are not trying to know that I/O subsystem causing a problem. By this problem, the database can be corrupt and it affects the other part of it.

During improvement or migration: I have seen many times that people always use consistency check after migration but not before. After every update DBCC CHECKDB has been improved especially from 2000 to 2005. Suppose you run the consistency check on the migrated/upgraded database and got corruption then, you will perform following options like

  • Run repair options: repair_fast, repair_rebuild, and repair_allow_data_loss
  • Restore from the backups: it might be possible that your backup has been corrupt too. 
It has been noticed that, corruption always available on before but discovered after upgrades. So always run the consistency check before any upgrade.
»» 

Tuesday, May 5, 2015

Easily Share Excel Workbooks using Excel Services in SharePoint 2013

Excel services are very indispensable for many organizations and you can use these services with SharePoint 2013. It gives various facilities to users and can be easily manageable.It is a shared service; user can publish his/her Excel’s workbooks to SharePoint server 2013 very easily.

Note: Excel services are only available in the enterprise edition of SharePoint Server 2013.

Excel services is an advanced tool with significant merits. It can be connected to reports and external data sources. SharePoint has a document library where users can publish workbooks in this document library.Document library has numerous excel services like Excel Web Access, Excel Calculation services and Excel Web Services for Programmatic Access etc.

It supports Sharing, Security, Management, and Excel workbook services in the browser by providing the following:

  • Management of Workbook by Global Setting – It includes security setting, session management, load balancing, memory utilization, external data connection, and workbook cache. This option is available on the Manage Excel Service Application page.You must be the member of the Administrator for Excel Services service application that you are configuring.

  • Trusted File Location – It is used to define the location of Excel workbook. User can manage the workbooks because they are explicitly enabled and Excel Services only loads from trusted file location. In the figure, the red arrow shows the location of Trusted File Location on the page.

  • Sharing of Workbooks –Excel 2013 workbooks can be converted to SharePoint Server Document Library to give the access to other browser-based user. Excel services loads the workbook and refreshes the external data (If required) for the user. A user can interact with this data by expanding, filtering, sorting and by passing in parameters. It gives ability to do analysis of published workbooks.

  • Business Intelligence Dashboard – With the help of Excel and Excel Services we can build a browser-based dashboard library to store and create Web Part pages. It includes Web Part pages with status lists and PerformancePoint dashboards for SharePoint status indicators.  

  • Report Building –It is one of the most important features of Excel Services.We can build the reports that we have created in Excel and make it visible to others. 

»» 

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.

»»