Monday, 20 January 2014

Backup and Restore SQL Server Databases Using SQL Server Management Studio

Backing up SQL Server database is a good precautionary move against SQL server disasters that can happen due to various reasons such as media failures, user mistakes, hardware damages etc. This is a widely used SQL Server Recovery method. It is possible to backup and restore SQL Server databases using the SQL Server Management Studio. Follow the steps given below to backup SQL Server database. Here we assume that you are using SQL Server 2012.
Full Database Backup – Using SQL Server Management Studio
  1. In the Object Explorer, expand databases, select a user database (or expand system databases and select a system database).
  2. Right-click the database, click Tasks > Backup.
  3. Enter details (as described below) when the Databases dialogue box appears (by default General in the Select a page pane is selected).

Source Section
  • Check the Database list box and verify the database name (change the database name if required).
  • Enter the recovery model (FULL, BULK_LOGGED, or SIMPLE) in the Recovery model text box.
  • Select the backup type as Full in the Backup type list box.
Note - With the Full option selected above, you can optionally create copy-only back up by checking the checkbox Copy Only Backup.
  • Choose Database as the Backup component.
Backup set Section
  • Enter a Name for the backup set (you can use the default name suggested the software).
  • Enter description in the Description text box (this step is optional).
  • Enter the backup set expiry information: choose After / On and enter the required values.
Destination Section
  • Choose Disk or Tape as the destination, and click Add; use the Select Backup Destination dialogue box to select the required destination. The added destination appears in the Back up to list box.

  1. Click Options in the Select a page pane on the left of the dialogue box. This page shows the advanced options. Enter the details (as described below) as required.

Overwrite media Section
  • Select an Overwrite media option:
  • Backup to the existing media set
After selecting this option, select Append to the existing backup set OR Overwrite all existing backup sets.
Note: - Check media set name and backup set expiration is an additional option. It verifies the expiry information of the backup set. You can optionally provide the media set name in the Media set name text box.
  • Back up to a new media set, and erase all existing backup sets.
After selecting this option, enter the media set name in the New media set name text box (optionally, you can add a description in the New media set description text box).
Reliability Section
Select the required options from the three:
  • Verify backup when finished
  • Perform checksum before writing to media
  • Continue on checksum error
Tape drive section (This section is enabled only if the destination selected is Tape drive)
It has two options:
  • Unload the tape after backup
  • Rewind the tape before unloading
Compression section
  • SQL Server 2012 supports backup compression. You can select the required options in the Set backup compression list box. The options are shown below.

  1. Now, click General in the Select a page pane on the left of the dialogue box. Click OK.
  1. Click OK when a message appears after the completions of the database backup.

Restore SQL Server Databases Using SQL Server Management Studio
Backing up SQL databases is a routine process in organizations, owing to the criticality of data carried by them. After events of data losses, users can restore the lost data from the backups. Users can use SQL Server Management Studio for restoring a full backup. The steps for SQL Server Recovery are given below (for SQL Server 2012):
  1. In the Object Explorer, expand databases, select a user database (or expand system databases and select a system database).
  2. Right-click the database, click Tasks > Restore > Database.
  3. Enter the details (as described below) when the Restore Databases dialogue box appears (by default General in the Select a page pane is selected); click OK.

Source Section
  • Select the source database from the Database list box.
  • Click the browse button (…). Enter the details when the Select backup devices dialogue box appears. Select device type from the list in the Backup media type box (you can use Add to add more devices); click OK.
  • Select the database to be restored in the Database list box (it is available only after the selection of Device in the previous step).
Destination Section
  • Verity the name of the database to be restored in the Database box (you can change the name if required).
Timeline Button
  • Click the Timeline button.
  • Enter the details (as described below) when the Backup Timeline dialogue box appears.

  • Click Specific date and time. Enter the time and date using the Date and Time boxes (you can also use the slider bar for it); click OK.
Restore plan Section
  • Backup set to restore grid now displays the backups (that are necessary to perform the restore action).
  1. Click Options in the Select a page pane on the left of the dialogue box. The Options page shows the advanced options. Enter the details (as described below) and click OK.

Restore options Section
Select appropriate option from the three:
  • Overwrite the existing database (WITH REPLACE)
  • Preserve the replication settings (WITH KEEP_REPLICATION)
  • Restrict access to the restored database (WITH RESTRICTED_USER)
Select an option in the Recovery state box from the three:
Tail-Log backup Section
There is no need to modify the settings in this section. However, you can
- Check the checkboxes:
  • Take tail-log backup before restore
  • Leave source database in the restoring state
- Select the Backup file path
Server connections Section
  • Check the checkbox Close existing connections to the database.
Prompt Section
  • Optionally, you can check the checkbox Prompt before restoring each backup.
  1. Click OK when the confirmation message appears after restoring the database.
Visit this link to download Kernel for SQL Recovery Tool

Sunday, 19 January 2014

Repair SQL Database undergoing corruption issues in GAM, GAM, and SGAM Pages

1 comment
MS SQL is the RDBMS and it is widely used throughout the world for reliable, consistent, and fast data managements for small and large organizations. MS SQL server uses .MDF file format to store data within its database. The SQL server database files are categorized into two types: Master Data File (.mdf) and Secondary Data File but only one Master Data File is responsible for each database as it keeps track of all the other files in the database. In SQL server, the fundamental unit of data storage is the page. The space allocation in SQL Server is managed in portions called “extents”

SQL Server uses three special types of pages to record extents: GAM, SGAM, and IAM.

GAM (Global Allocation Map) Pages: GAM pages record what extents have been allocated. Each GAM covers 64,000 extents, or almost 4 GB of data. The GAM has one bit for each extent in the interval it covers. If the bit is 1, the extent is free; if the bit is 0, the extent is allocated.

SGAM (Shared Allocation Map) Pages: SGAM pages record which extents are currently being used as mixed extents and also have at least one unused page. Like GAM, each SGAM also covers 64,000 extents, or almost 4 GB of data. The SGAM has one bit for each extent in the interval it covers. If the bit is 1, the extent is being used as a mixed extent and has a free page. If the bit is 0, the extent is not used as a mixed extent, or it is a mixed extent and all its pages are being used.

IAM (Index Allocation Map) Pages: The function of IAM pages is to track the extents that belong to tables or indexes. The IAM is a bitmap that refers extents to objects. This kind of page covers a 4-GB range and an IAM page is required for each 4-GB range of data, partition, and allocation unit type.

While working with MS SQL server, you may come across issues resulted due to corruption of SQL server pages: GAM, SGAM, and IAM. Now, you cannot perform your queries to get desired result. Suppose, you run a query on your Server in order to count the amount of rows present in one of the primary tables and get an unexpected error message as follows:

“Attempt to fetch logical page (1:42724) in database ‘test’ belongs to object ‘report-table’, not to object ‘RRR’.Connection Broken.”

To fix such errors, you try DBCC CHECKDB. This utility performs systematic data integrity checks throughout the data files and identifies infected areas. But, it is not enough to resolve all issues related to GAM, AGAM and IAM pages of SQL server. If you have backup copy then you can restore your database. Unfortunately, in absence of backup or having corrupted backup, you should look for a third party tool.

To resolve your critical situation of SQL server corruption (GAM, AGAM and IAM), Kernel for SQL database has been developed. With the help of this powerful SQL recovery tool, you can successfully fix all existing SQL server issues and recover your crucial database. It supports MS SQL Server 2000, 2005, 2008, 2008 r2 and 2012.
Visit this link to buy Kernel for SQL Recovery Tool

Monday, 26 August 2013

How to recover corrupt MDF & LDF files

The importance of owning a sturdy Database Management System is evident for personal or professional usage. Other than managing official work, it helps in keeping track of administrative records as well. However, there could be umpteen reasons that corrupt database and lead to data loss irrupting work! Most of the times, under MDF file corruption, DB admin encounters the following error when tried to execute DBCC CHECKDB command. The error also occurs when admin tries to restore file from an upgraded backup!
DBCC CHECKDB command generally helps to repair corrupt MDF files. It recovers MDF files damaged due to logical corruption and in cases when it fails; the admin restores data from updated backup as and when required. Trouble arises when there is not any database backup to be restored from. 
Have a look at the error below: 
Error 5101: Device activation error
In this case, the physical file name 'C:\%path to db%\db_log.LDF ' may be incorrect. When corrected you might face the error, “db Attach failed”. 

LDF is the Log files associated with MDF DB files. It is one of the most common errors that Admin face when they try to open MDF file in order to access the Database objects. In order to repair this error, admin executes DBCC CHECKDB ('dbname', REPAIR_REBUILD). However, admin usually gets another error message, shown below:
Server: Msg 945, Level 14, State 2, Line 1
Database 'dbname' cannot be opened due to inaccessible files, insufficient memory, or disk space. See the SQL Server error log for details.

These few common errors keep coming and disrupting work. In worst-case scenarios, user ends up losing loads of information just like that. Under similar circumstances, admin must trust third party MDF and LDF Recovery tool. It not only eases out the damage issues but also performs complete and accurate repair of corrupt MDF as well as LDF files. Easily available, Kernel for SQL Recovery Software is the appropriate way for recovering SQL damage files. Proficient in repairing MDF files, the software completely recovers tables, views, indexes, stored procedures, unique/primary keys etc from the damaged SQL database. Moreover, the software is available in free trial edition that users can download to try it before purchase. Providing full support to MS SQL Server 2000/ 2005/ 2008/ 2008 r2 and 2012, this software is worth a try. Click here

Monday, 5 August 2013

How to Eliminate Consistency Error of the SQL Server 2012 Database?

SQL Server 2012 is integrated with advanced features that ensure high security for SQL databases. In spite of advanced security features, SQL Server 2012 databases or .MDF files may generate corruption issues due to multiple reasons as mentioned below:
  • Corrupt hard disk
  • Slow running of MS SQL Server 2012
  • Forced shutdown of MS Windows due to power fluctuation
  • Abnormal growth of the SQL 2012 database
  • Migration of SQL 2012 database into another SQL Server version
  • Virus and spyware intrusion into a computer
  • Defects in other computer hardware components

Database inconsistency is the most common issue in the SQL Server 2012 or any other SQL Server version. In database inconsistency state, both transactional log and primary database of MS SQL Server shows different data. Consistency issues on MS SQL Server are generated mainly due to non-availability of disk space and crash of the SQL Server. Database inconsistency may appear in some or all pages of the SQL database file. You shall see following messages when SQL Server finds inconsistency or page mismatch in the SQL database:
  • Error "0xe0008443 - One or more SQL Database consistency checks have failed
  • Replica is inconsistent
  • Data row does not have a matching index row in the index
  • SQL Server detected a logical consistency-based I/O error
Consistency errors in the SQL database are reported either during backup generation or consistency check process. 

Microsoft SQL Server 2012 is equipped with set of DBCC scripts (T-SQL) that can detect and fix SQL database consistency issues. DBCC scripts can be accessed in the SQL Server Management Studio or Studio Express. You can use following DBCC scripts or commands of MS SQL Server 2012 to fix SQL database consistency errors:

DBCC scripts help SQL administrator in performing a comprehensive consistency check of the SQL database in disk allocation structures, catalogue, index pages and tables. Suppose, you want to recover database table named XYZ production, then use following T-SQL script:
This script will rebuild missing or corrupt indexes of the XYZ Production table. It will restore all missing rows of non-clustered indexes in XYZ Production table. If you want to recover XYZ Production table then use REPAIR_ALLOW_DATA_LOSS in the T-SQL script as shown below:
However, certain amount of data in the XYZ Production may be lost while using REPAIR_ALLOW_DATA_LOSS Database recovery with DBCC scripts is time consuming. If consistency issue in a SQL 2012 database or database group is generated due to hardware malfunctioning, it is better to restore database file using either SQL backup Exec or third party software.

Kernel for SQL database recovery is an extraordinary tool to recover corrupt SQL databases. It swiftly recover inaccessible SQL MDF databases either from the local disk or Live Server. This MDF file recovery software restore every items from a corrupt SQL database that includes indexes, primary keys, tables, triggers, stored procedures, foreign keys and views. MDF file recovery tool successfully restore large SQL Server databases. It smoothly fix consistency errors of the SQL database. MDF file recovery software is compatible with all SQL Server versions including SQL Server 2012. See more at