Microsoft SQL Server is a well-known Relational Database Management System and due to its effectiveness, it commonly used for creation as well as maintenance of a huge database. SQL Server allow users to store and manage a large amount of data. SQL Server database file comprises of several pages to store its allocation structure along with Global Allocation Page. Entire information related to the extent is allocated in the file of SQL Server that contained in IAM (Index Allocation Map) pages. It contains the extent information about indexes and tables.

Problem Statement

Sometimes, because the presence of many IAM pages for a single point, a user might experience several errors. Such types of error indicating SQL server table corruption. Due to this corruption, SQL user will get an error 8947:

Reasons Behind SQL Server Error 8947?

When a user faces error code 8947 it means that an IAM page must have one-bit extent in GAM of SQL Server in a proper state and it will help to display which extent is allocated to which specific index. Now, due to unfavorable condition these two IAM pages like P_ID1 and P_ID2 contain the common GAM interval. Apart from this, in some situations, SQL error 8947 appears on multiple IAM pages within the server. Hence, the main cause of this error is that the IAM chain for particular index those posses 2 IAM pages which cover up the same GAM interval. Generally, the common cause of this problem is the failures of hardware. Thus, in that case, a user needs to recover the corrupted SQL database.

Understanding some terms to get the reason why this Error code 8947 take place

It is necessary to have knowledge related to Global Index Map (GAM), Index Allocation Map (IAM), indexes, interval and page in SQL server.

• IAM pages: It helps to store the detail related to the extent use through tables or indexes as per unit allocation.
• GAM Pages: It states either extent are allocated or not. Whatever has being allocated to any extent it will get recorded in these pages.
• Page: It is a primary unit to save or store data and its 8KB size. The disk space is divided into MDF and NDF files logically.
• Extent: It is a storehouse where continuous eight pages are physically used to manage the several pages in an effective style.
• Interval: It can be represented as the availability of total space in a file that helps to map a GAM page and its amount can be 4 GB approximately in size.
• Index: Every index has multiple extents that are allocated from GAM interval and make sure have an IAM page for particular GAM interval.

Manual Method to Fix SQL Server Error Code 8947

The following manual solutions required to overcome this SQL server error message:

1. Hardware Diagnostic and Repair

One of the major causes of corruption in a database table can be the hardware issue. The MS Windows NT system, application logs, and SQL server error log must be examined. Thus, if the hardware corruption issue raised then, the particular operation should be accordingly performed. As per users need, any particular corrupted component or complete hardware must be changed.

In case, if required then the disk drives might be reformatted and operating system must be installed again. Moreover, it should be assured that the server machine doesn’t have to enable caching on disk controller. This solution will become helpful if the issue happened in hardware.

2. Restore Backup

If the hardware repair is unable to resolve an issue, then users must use the different solution to eliminate SQL Server error 8947. Hence, if a clean and healthy backup is available then, restore SQL database from backup file for regaining the accessibility of the data items which is stored in SQL server database. It can be possible, only if the backup is available.

3. DBCC CHECKDB Execution

It is an existing utility that can be executed to restore or repair the SQL server database file. Still, in the beginning, it can be run without any need for repair clause thus, to verify the reason and damage level. It helps to run an appropriate restore clause to fix an error. The main reason is that DBCC CHECKDB helps to rebuilt the IAM chain which holds P_ID2 during detection of damaged IAM pages. It can fix other errors but the problem is that it justifies the data loss which is not suitable.

Alternate Solution to Resolve SQL Server Database Error 8947

The most suitable solution for fixing this error code 8947 is SQL Recovery. It is safe and secure style to troubleshoot this error with the help of following steps:

Step1: First, launch SQL Recovery, add MDF file and then, press Open button

Step 2: Once the files are added, the application will provide two options to scan SQL file i.e. Quick & Advance. After scanning, the application will Preview the completed recovered data of MDF and NDF files including tables, stored procedures, triggers, and views, etc.

Step 3: Now, select an option among two i.e., SQL Server Database & SQL Server Compatible Scripts for exporting the database and click on save option

Finally, a user will be able to get MDF or NDF file without any error with help of this solution.

Conclusion

This article describes the server error, which creates obstacle while accessing the SQL Server database. Also, we have discussed a best possible solution which helps to overcome this issue. Initially, it is recommended to restore the backup file of the database, if a user is already having it. Else, a user can go for another solution for resolving SQL server error 8947.

With time, multiple database application acquires grow, it will become more complicated and need to support many users. At some moment, a user able for importing access database into SQL Server Database. MS SQL Server Database helps to optimize the security, scalability, availability, performance, reliability and recoverability.

Moreover, we will discuss the access upsizing wizard process which is used regularly to upsize the small and large Access databases. In the following session, we are considering several steps which help to upsize the process.

Convert MS Access Database to SQL Server

There are following steps to transfer Microsoft Access Database to SQL Server those are mentioned below:

Create a SQL Database and Multiple Log Devices

Consider, if a user may create a database twice the size of Access file, then the estimation is incorrect due to the presence of Memo fields. Memo field, which is used to upsize the amount of space because this upsizing Wizard helps to create a timestamps column for every field. Thus, we have created a database and log devices five times bigger than the MDF file.

Create New Data to Upsizing

A user has to create database and an ODBC connection for existing SQL Server database. The ODBC data source is obtained to store the information while creating the database.

Gather the table to Upsize the Database

In this step, a user must select Access Database Documenter that help to gather the entire information to upsize the SQL server database. Make sure a user has to install the documenter from MS Office setup.

Follow some points to gather data on table relationships, Select Tools, Analyse, Documenter from database menu bar. After choosing the current database option, select Relationships and press OK to display the entire detail of all relationship in the database.

Make MDB files More Manageable

If the name of table or field names is not valid for SQL server identifiers then, upsizing Wizard tries to force the table to become more manageable. Due to some situations, the Wizard creates an aliasing query, that takes the name of the original table and translate each SQL server field names to their original names.

Delete Recursive Relationships

Recursive relationships are the medium between two fields within the same table that users have applied. If a user tries to upsize this table, then, the upsizing Wizard doesn’t have any problem to create the structure of a table. However, Wizard will encounter various difficulties while loading the data because it must load all supervisors before it load the other table data. Thus, a user must remove all recursive relationships.

Determine the Order of Table

When a user has to load the data, make sure first to load the table along with no foreign keys. After that, load all the tables which depend on first as well as second level tables and so on. Table dependency order is most important because if a user wants to load over-sized data then, loading becomes fail.

Check Entire Default Values

It is necessary to check all default values, especially if a user has number fields along with default values o in the table that a user don’t want to allow o values. For example, if a user mentions the Identity field of SQL server parent table within child table, the default value comes as o, even if a user allows only null values in a parent table. This issue happens, access shows the default value before saving the list of records. Therefore, a user might view o in Access interface and delete it for setting the field to null. However, When a user wants to attach SQL server tables, MS Access can not display the default values. Hence, it aspects like a user entered a null, but when a user wants to store the record, SQL server attempts to insert the o value and the updated records become fail.

Move Data Tables into Code File

A user must perform this step only if users data and code are present in separate databases. While running the upsizing Wizard, it must attach all SQL Server tables to the database from which the Wizard runs. If a user might leave the tables in a separate database and try to run the Wizard from the table database, when a user wants to link the code file to new SQL server tables, the Wizard represents the names of all newly linked SQL Server as tables dbo_TableName. Apart from this, a user can rename each table manually. A user can ignore this hassle by importing all table from .mdb file into code file before running the upsizing Wizard. Make sure to include the data, relationships, and structure.

Running the Upsizing Wizard

While running the upsizing Wizard, make sure a user must upsize each table at once to assure that the table relationship acts together. In case, if Table X has a relationship with particular Table Y and a user can upsize Table X but not Table Y then, the Wizard breaks the relationship between both tables.

Let the upsizing Wizard helps to specify the attributes of the table to upsize, including the data options and modification. The following points clearly helps to run the upsizing Wizard:

• Using Declarative Referential Integrity (DRI), not triggers for several relationships. If a user doesn't have more experience with SQL Server, then, DRI is much predictable and easier as well for using as compared to the triggers. A user might face problems with referential integrity if a user choose triggers, insert and delete operations must behave uncertainly. Nevertheless, if a user uses DRI, a user can write triggers for cascading deletes.
• Make sure, Wizard don’t need to decide where putting the timestamps if a user doesn't have a powerful preference. The upside to timestamps helps to track the sequence of changes in users database if a user must have the time and expertise to figure out how they do their work. The downside to timestamps is dual.
• First, timestamps add storage data space to a user database.
• Second, they try to make it more difficult to bcp data from MS Access tables to SQL Server database tables. If a user wants Wizard to place the timestamps, a user has essentially done more advanced bcp formatting thus SQL Server easily knows how to represent the various fields from a text file to the database fields. Moreover, advanced bcp formatting is a time-consuming process.
• Create only the structure of table no need to upsize any desired data. Especially, it is necessary to tell the Wizard to create the table structure if a user has a computer system with referential integrity. If a user lets the wizard upsize the data, it may be upsized the child tables before the parent tables, it may cause to become fail while loading the data for those tables.
• Create the link with new SQL Server tables and save the relevant Password information with linked tables. Otherwise, when a user tries to open an Access file, a user will get a prompt for a password to re-establish a user connection along with SQL Server.

After this, click on Next to get final Wizard screen. This screen represents an option to create the log report. After selecting, a user must get this report and choose Finish to run the upsizing Wizard.

Check the Log

After running the access upsizing Wizards, indicate the log immediately because the Wizard might remove this log. Also check the log for aliasing queries and other unnecessary errors. If a user receive the errors, then must decide either to work around them or redo the data upsizing procedure. If a user has decided on later, a user must remove the newly created database and then, revert to .mdb file, Fix the problem on Access side and re -test the upsizing Wizard.

Load the Access Database into SQL Server

Now, load the Access data within SQL Server through several tables. If a user has a database without any Auto number fields, then the data should migrate from Access database to SQL server with few issues. Simply, a user can allow appending several queries for each table. If a user holds large tables, then, a user must need to run smaller queries for every table.

Need to Write Triggers in SQL

For relationships with cascading events, a user must remove the DRI foreign keys and write all triggers. A user can write triggers for recursive relationships and also for table-level validation rules. Triggers help to assure the data integrity.

Run New SQL Server System

Multiple users have to test the SQL database server system several times. Add data to each table and make sure the Desktop acts predictably (must break each rule).

Conclusion

Here we are ending with the steps recommended to convert MS access database to SQL Server without any consequences.

SQL Server is a platform where recovery is considered the last resort. When a condition takes place, it is first examined and understood. In case of an issue, the DBAs either perform troubleshooting using integrated tools or apply backups. However, when all else fails commercial utilities play a vital role. Therefore, the following review will be detailing about the information gathered about one such solution namely; MDF Recovery Software based on the testing performed on it. The application claims to be a pioneer in the MDF recovery domain based on the features it offers. Read on to know whether the application is what it claims to be or not.

MDF Recovery Software Overview

• The software is a standalone and works without SQL Server configuration
• With a user friendly and self-explanatory UI, the software is universally usable
• Supportive featured besides the primary options, offer convenient performance
• Preview of records within recovered database tables ensures assured restore

A Deeper Analysis of the MDF Recovery Software

There are plenty of applications claiming to be the greatest recovery solution for SQL Server. However, what makes this software a better option is listed as follows:

• Works as a Standalone:

There is no dependency on SQL Server environment. The software ran well without the availability of SQL Server and recover corrupt MDF file as a standalone.

• Database Recovered:

Recovery of primary and secondary databases in SQL Server is supported. The software was capable of processing an MDF file along with its corresponding NDF file in multiples.

• Restore Deleted Records:

Not just corrupted MDF file, but deleted records are also restored by the software. When tested, the tool capably offered successful results for recovery of deleted records from one of the tables in the database.


• Customized Export:

Besides selective table export, MDF recovery software offers another customization in the export. You can choose whether to export just the schema or schema along with the data while exporting a recovered database.


• Variety of Exports:

Once recovered the records from a database, one can choose to restore them into either an SQL database during server availability or a Server compatible script file when the server is not available.


• Option to Save Scan:

When SQL Server is concerned; database sizes generally range above 100 GB. MDF Recovery Software maintains Recovery Time Objective by allowing the scanned state of a database to be saved to avoid rescanning it in future.


• Varied Data Type Support:

Multiple data types are stored in databases of the SQL Server. Thus, the software ensures support for them, a few of which were tested includes, geometry, hierarchyid, and geographical data types.

• Platforms Supported:

The software is Windows based and supports all available versions of the Operating System. In addition, full support is provided for the latest versions of SQL Server too for efficient recovery.

Available Versions:

The setup version of the software currently available and used during the testing of MDF Recovery Software was v6.2.1. Meanwhile, to get the software, you can either download its freeware copy and test it or directly go for the purchase instead.

The purchase on the other hand is further divided into four licenses - Personal, Corporate, Technician, and Enterprise, which can be chosen according to the requirement.

Verdict of the MDF Recovery Software

Software is programmed to deal with corrupted MDF and NDF files. In addition to which, it was found that the software also successfully renders restoration of deleted records from tables, if any. Moreover, software features the freedom to acquire a customized export of recovered data. This is feasible with the help of checkboxes provided for selective table restoration, export of schema only or schema plus data from the database, and with the option to save the output in server database or compatible script file. On an overall basis, MDF Recovery Software offers features that make it a decent recovery tool specialized for SQL Server database processing. Thus, the software based on its testing can be rated 9.5 out of 10. The 0.5 rating is cut down, as the software isn’t capable of processing multiple ‘primary’ databases. Other than that, the tool is perfectly fit for MDF file recovery of an SQL Server DBA.