Tuesday, August 25, 2015

Backup and Recovery Options of SQL Server

Database corruption is the part of many database user lives and it gives the chances to explore new things every day. This blog is about the advanced backup and restore options of SQL server database. All DBA’s understand the importance of backup and restore command but sometimes a lesser-known option can be very useful. Let’s see the different options:

Verifying the Backup Integrity: Most of the time; I found that experts easily neglect backup integrity while it plays an important role in database. SQL server 2005 introduced page checksums feature and it works as the page corruption detector of the data file. This function is by default enable in the SQL server 2005 and if you need to enable them then upgrade the database from previous version.

You can use the BACKUP command with CHECKSUM option to confirm that page checksums are checked. This option is very important because it notifies you when your backup was corrupted. If it finds page corruption then backup will fail by default. At this time user get the following error message on the screen:

Msg 3043, Level 16, State 1, Line 1
Backup ‘broken detected an error on page (1:143)
in file ‘C:\.....\broken.mdf’.
Msg 3013, Level 16, State 1, Line 1
BACKUP DATABASE is terminating abnormally.

Some DBA’s use the RESTORE command with VERIFYONLY option to check the integrity of the database but, it only checks BACKUP header not the data inside BACKUP. You have to use RESTORE command with CHECKSUM and VERIFYONLY options to restore the process and recheck the all pages and entire BACKUP checksums.   

Minimize the Data Loss: When a corruption occurs in the database then, many users start restoring the database from backup. At this situation it is a possibility of the last portion of the transactional log disappears forever. This kind of backup is called tail-log backup. This backup is most useful when you are using synchronous database mirroring.

If you are using tail-log backup and the data files are missing then, you will get the following error message on your screen:

Msg 945, Level 14, State 2, Line 1
Database ‘DBMine2008’ cannot be opened due to
inaccessible files or insufficient memory or disk space.
See the SQL Server errorlog for details.
Msg 3013, Level 16, State 1, Line 1
BACKUP LOG is terminating abnormally.

In this case, you can use NO_TRUNCATE option with BACKUP statement because it allows the backup to proceed. 

Note: In SQL Server 2005 and later versions, a database that has tail-log backup cannot be overwritten by the restore option unless you have to use REPLACE option.

Point in Time Restoring: Let’s assume a scenario, you deleted the data and you need to restore the data from the log backup created right before the deletion. If you know the time of data deletion then simply you will choose the STOPAT option to restore it. If you don’t know about the timing of data deletion then, you have to use STOPAT and STANDBY options to progress through the log and check by small amount of time to reach on the correct point.

You can also use its alternative which is marked transaction. It creates log mark in transactional log. For example:


You can use this known point during database restoration. STOPATMARK can be used if you want to include marked transaction and STOPBEFOREMARK option to exclude marked transaction. To use these options you should have the information about the name of log mark. If you haven’t then you can find in the logmarkhistory table in msdb. If you lost this information then, it is difficult to find the log mark names manually. In this case you have to use a third party tool.

Restart Interrupted Restore Option: RESTORE command has many options and WITH RESTART is one of them. It is less-known option which uses to restart an interrupted restore option. This restores operation works on the checkpoint file which has the information of point to which restore has progressed. You can check this file on \InstanceName\MSSQL\Backup folder location.

A checkpoint file is updated by the following reasons:
  1. After the processing of each and every backup set.
  2. When the redo part of the recovery is finished.
  3. At the time of database file creation after completion of zeroing.

When you use this WITH RESTART option on the absence of checkpoint file then, you can get this error message:

The restart-checkpoint file ‘C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\Backup\RestoreCheckpointDB8.CKP’ was not found. The RESTORE command will continue from the beginning as if RESTART had not been specified. 

This option is best for the large backup which has many taps because you can restart the restore option without going back to the first tape. It is also suitable to restore disk-based backups.

Other Options: There are many options which are lesser-known option which I said at the starting of blog so, you should try these options. By using BUFFERCOUNT and MAXTRANSFERSIZE options you can get the great performance to modify the I/O buffer. PAGE and PARTIAL options reduce the downtime when disaster occurs and these are used to single-page and piecemeal.

Conclusion: I hope you like these options and curious to know more about them. Database backup and restore is the very vast topic and used by many database users. Few corruptions are very hard to solve and at this point I can only suggest any trustworthy SQL database repair tool to repair your valuable database.
»» 

Wednesday, June 17, 2015

Get the Solution of Error 3343 in MS Access Database

MS-Access is mostly used in the small organizations to manage the valuable data. A database user faces different database errors during run-time and error 3343 is one of them. Error 3343 occurs when the database has corrupted and a user tries to access the same database. Possibly following error message is display on the user’s screen:

Let’s walk through the different reasons behind this error:

Reason 1: Splitting of the database
As we all know that a database can be small or large in the size. In the most cases; user stores data in a single file of the database and this single database file grows day by day and can create trouble. Spitting of database can fix the problem, but it is not the concern here. When you try to split this database into two parts and corruption on a single part of the database may be the main reason of whole database corruption. This is very serious issue and complete database repairing is a very tough job when it large in size.

How to fix: MS-Access has inbuilt repair tool to repair the database and it is called Compact and Repair utility. With this utility, you can easily repair the corrupt database.

Note: If the user is unable to repair the database using above utility then can download other tool which can repair the database.

Reason 2: Opening a database in previous version
It is the second most possible reason behind the error 3343 in MS Access database. Default file format supported by the Access 2007 and 2010 is .accdband if you want to open the Access 2010 file into Access 2003 then, you have to convert the file of 2010 to 2003 by MS- Access. Mostly you convert the database into the earlier version for sharing with others.

How to fix: Split the database into parts and open the database file in the previous version are the main reasons of this error. To fix this error you can take help from reliable commercial software
»» 

Monday, June 15, 2015

Shaking Hands with SQL Server 2016

I am a SQL Server lover and I was eagerly waiting for the SQL Server 2016. It is good to hear that, its public preview is available to download. Mr. Satya Nadella, CEO of Microsoft announced SQL Server 2016 during ignite conference. If you thinking about to upgrade the SQL Server then, it is a best reason to start budgeting and planning for the upgrades in your organization. It has the ability to deliver mission-critical capabilities with operational analytics built-in. Top capability for the SQL Server 2016 is the always encrypted behavior and mainly designed to protect data in motion or rest.

Always Encrypted and Secure: This feature is the highlight of the SQL Server 2016. With this feature, SQL Server can perform operation on encrypted data and encryption key can be resided with the application in the customer trusted environment. Encryption and decryption of the data happens clearly inside the application which reduces the changes that have to be made to existing applications.

In-Memory OLTP & Real Time Operational Analytics:  In-Memory Online Transactional Processing (OLTP) is a major focus is fast performance. Maximum size of the durable table in the SQL Server 2016 is 2 TB where as in 2014 it is 256 GB. It also supports nested native procedure calls and natively-compiled scalar UDFs. It has new syntax extension to ALTER your bucket count values with the simple rebuild:
ALTER TABLE dbo.InMemoryTable

Mobile BI and PolyBase: SQL Server 2016 expands its scope beyond data warehousing, transaction processing and business intelligence to deliver the advanced analytics to know deeper insights of the data. Additionally, we are creating PloyBase into the SQL Server to expand the power of extract value structured and unstructured data using existing T-SQL skills. With this feature user can get the faster insights by rich visualization on different devices including mobile application on Windows, iOS and Android.

Stretch Database:  This awesome technology allows you to dynamically stretch your cold and warm transactional data into the Azure cloud platform, so your important operational data is always at hand without any worry of size. Always encrypted feature can be used with stretch database to extend the data in a more secure manner.

Additional Features

  • Security enhancements for the Row-level security and Dynamic Data Masking to round out security investment.
  • Partially support DML triggers.
  • Supports indexes on NULLable columns.
  • Supports foreign key.
  • Analysis Services and SQL Server Enterprise Information Management (EIM) tools to get an upgrade in performance, usability and scalability.
»» 

Wednesday, May 27, 2015

Easy to Deal with MySQL Error #1045

When a user formatted PC and re-installed local server (XAMPP) then sometimes user got 1045 error message. This occurs when root@localhost wasn’t granted the essential rights a user tries to access phpMyAdmin after successful installation of the local server. Here is the complete error message which occurs on screen:
#1045 Access Denied for user 'root'@'localhost' (using password: YES)

We have different servers to use MySQL database like: WAMP, MAMP, LAMP and XAMPP. WAMP is for Windows, LAMP for Linux, MAMP for Mac OS X and XAMPP for all operating systems. It also supports perl, mercury mail, filezilla and some scripts. In this blog, I will try to help users to get rid from this error for any local host server by the use of phpMyAdmin.

User can try following methods to resolve this error. Your problem might be fixed using the first method or you have to perform all three.

First Method

  1. Start the command prompt and go to the XAMPP directory: cd ../../Program Files/xampp
  2. Now go to xampp->mysql->bin
  3. Now run the following command: mysql.exe --user=root --password= 
  4. After this, MySQL command line will start and we can change the –user= and –password= accordingly.
Second Method: In this method we create the batch file to make it easier. To do this just follows simple steps:

  1. Create a file mysql_command.bat in the XAMPP folder and edit this file with any text editor and paste in this:
      @echo off
      echoMySQL command line...
      mysql\bin\mysql.exe --user=root --password=
  2. Save the file and change the username and password if you changed username and password. Now run command.bat and command prompt will display with MySQL command line in running condition.
Third Method:Open the config.inc.php file which is located in localhost ->xampp ->phpmyadmin->config.inc.php

  1. Open this file using any text editor.
  2. Now find the code $cfg['Servers'][$i]['password'] = ''; // MySQL password
  3. Now change the password which you have created in step 1.
  4. Click on the Save button.
»» 

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.
»»