Monday, 20 January 2014

Backup and Restore SQL Server Databases Using SQL Server Management Studio

2 comments
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:
  • RESTORE WITH RECOVERY
  • RESTORE WITH NORECOVERY
  • RESTORE WITH STANDBY
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 http://downloads.softwaredatarecovery.net/download-sql.php to download Kernel for SQL Recovery Tool
Read More...

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 http://order.softwaredatarecovery.net/buy-sql.php to buy Kernel for SQL Recovery Tool
Read More...