Summary: In this Blog Post, we will see why database corruption in SQL server happens during the Upgradtion from a Lower version of the database to a higher version and how to deal with it. This is one of the important reasons for the corruption of SQL Server database, Read on to know more.
Contents
The Copy Database Wizard is a utility used by hundreds of database administrators to move or copy databases and their objects easily from one server to another. A major high-point of using this wizard is that it allows you to perform a server upgrade without any downtime. It is claimed to be one of the ideal ways to migrate a small or medium-sized database between servers, especially between SQL Server 2005 to SQL Server 2008. In addition to copying the database, the wizard also enables administrators to copy extra database objects and metadata such as logins from the master database, SSIS Packages, objects required by a copied database, Agent Jobs, etc.
Although this tool is quite handy and competent, sometimes due to unprecedented events like power surges or abrupt system termination during the upgrade, the SQL database being migrated can end up getting corrupted. In such a case, repairing the corrupted database is a task only a proficient and reliable SQL repair software can perform. In this post, we?re listing the steps to upgrade SQL database using the Copy Database Wizard, and highlighting an application that can help you smoothly deal with the ?SQL database corrupt after upgrading? issue.
Steps to upgrade SQL Server
Follow the below-mentioned steps to upgrade SQL 2005 to SQL 2008:
- The Copy Database Wizard can be invoked through the SQL Server Management Studio (SSMS). Connect to SQL Server 2005 using SSMS and from it, right-click on the database name that you wish to upgrade.
- From the pop-menu that opens, select “Tasks”-> “Copy Database…”
- From the Copy Database Wizard that opens up, click on the Next screen to initiate the upgrade process. Then from the next screen, select the SQL Server 2005 source server name and the appropriate authentication mode.
- Click on Next and from the subsequent screen select the SQL Server 2008 destination server name and the appropriate authentication mode.
- Click on Next and choose one of the available transfer methods. Your options will be “Detach and Attach Method” or “SQL Management Objects Method”. The preferred method to upgrade large databases is the ?Detach and Attach Method?, but it requires the source database to go offline.
Note: User connections aren?t allowed on the database when this option is being used. To be on the safer side, select the checkbox “If a failure occurs, reattach the source database“. This will help to get the source database back online in case of a failure.
- Click on Next and from the subsequent screen select the database you wish to migrate from the list of databases. Here you?ll have the choice to ?Move? or ?Copy? the database or do both.
- Click on Next and specify database file names and also your choice whether to overwrite existing databases at the destination.
- Click on Next and select the additional objects (outside the SQL Server 2005 databases) that you want to copy to SQL Server 2008 server.
- Click on Next and specify the SSIS package and the logging option suiting to your setup. You can pick from writing to a Windows event log or to a Text File.
- Click on Next and from the subsequent screen select if you wish to run the SSIS package immediately or schedule it to some other time. Below the SSIS package running settings, you will also find the Integration Service Proxy account settings. These settings will specify the user account that will be used to run the package.
- Click Next and verify all your selections. This is the last screen where you can make modifications to your choices, so double-check. If everything seems fine, click Finish to start the migration.
- When the upgrade completes, a success screen will be displayed indicating that the process has completed.
Pros & Cons of the above-mentioned method
While the above mentioned manual procedure comes with the plus points of being free of cost and readily available, it suffers from the following limitations:
- It is too lengthy and complicated
- Configuration problems in any version of SQL Server can prevent you from upgrading
- You can never really be sure if and when any of the following SQL database upgrading issues may arise:
- Database Engine upgrade issues
- Late-breaking upgrade issues
- Replication Upgrade issues
- Full-text search upgrade issues
- SQL Server Agent upgrade issues
- Reporting Services upgrade issues
- Moreover, before performing this procedure, you must ensure that the account you?re using for the upgrade is a member of the sysadmin fixed server role on both the source and destination servers
These shortcomings of the procedure are sufficient to prompt the need of an easier method (third-party tool) to perform the upgrade. Moreover, the biggest risk that this method carries is that if the upgrade gets interrupted due to any reason, the SQL Server database will end up getting corrupted.
What should you do if SQL database corrupts during upgrade?
Many SQL administrators report that while SQL upgrading 2005 to 2008, they have faced the ?SQL database corrupt after upgrading? issue. In such an event, they have no option but to abort the migration, restore the whole database from backup or logs, and then restart with the migration.
We have a simpler solution. A corrupt SQL database needs expert repair procedures, and instead of manual unreliable methods, handing over the job to a competent SQL recovery software is a wiser choice. We recommend using Stellar Repair for MS SQL for repairing corrupt SQL databases. In addition to fixing databases damaged due to faulty upgrades, this software can deftly fix other complex issues like mounting problems, dirty shutdown state, database inconsistency due to missing logs, etc. Armed with powerful scanning capabilities and an easy to use interface, this software is the best bet when it comes to dealing with SQL database upgrading issue.
Read about MVPs and Database Admins’ Experience
Conclusion
Several organizations use the ?Copy Database Wizard? to upgrade their SQL servers. While mostly, the operation completes successfully, sometimes, the SQL database being migrated ends up getting corrupted. In such cases, SQL repair software often comes to the rescue to get the server up and running.
