{"id":13967,"date":"2017-12-11T08:23:49","date_gmt":"2017-12-11T08:23:49","guid":{"rendered":"https:\/\/www.stellarinfo.com\/blog\/?p=13967"},"modified":"2024-01-31T10:18:47","modified_gmt":"2024-01-31T10:18:47","slug":"sql-database-corrupt-during-upgrade-here-is-the-solution","status":"publish","type":"post","link":"https:\/\/www.stellarinfo.com\/blog\/sql-database-corrupt-during-upgrade-here-is-the-solution\/","title":{"rendered":"SQL Database Corrupt During Upgrade? Here is the Solution"},"content":{"rendered":"

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.<\/p>

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.<\/p>

Steps to upgrade SQL Server<\/strong><\/h2>

Follow the below-mentioned steps to upgrade SQL 2005 to SQL 2008:<\/p>

    \n
  1. The Copy Database<\/strong> Wizard can be invoked through the SQL Server Management Studio (SSMS)<\/strong>. Connect to SQL Server 2005 using SSMS and from it, right-click<\/strong> on the database name that you wish to upgrade.<\/li>\n\n\n\n
  2. From the pop-menu that opens, select “Tasks”<\/strong>-> “Copy Database…”<\/strong><\/li>\n<\/ol>
    \n
    \"SQL<\/figure>\n<\/div>
      \n
    1. From the Copy Database Wizard<\/strong> that opens up, click on the Next<\/strong> screen to initiate the upgrade process. Then from the next screen<\/strong>, select the SQL Server 2005 source server name and the appropriate authentication mode.<\/li>\n<\/ol>
      \n
      \"SQL<\/figure>\n<\/div>
        \n
      1. Click on Next<\/strong> and from the subsequent screen select the SQL Server 2008<\/strong> destination server name and the appropriate authentication mode.<\/li>\n<\/ol>
        \n
        \"SQL<\/figure>\n<\/div>
          \n
        1. Click on Next<\/strong> and choose one of the available transfer methods. Your options will be “Detach and Attach Method”<\/strong> or “SQL Management Objects Method”<\/strong>. The preferred method to upgrade large databases is the “Detach and Attach Method”, but it requires the source database to go offline.<\/li>\n<\/ol>
          \n
          \"SQL<\/figure>\n<\/div>
          \n

          Note:<\/strong> 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<\/strong>“. This will help to get the source database back online in case of a failure.<\/em><\/p>\n<\/blockquote>

            \n
          1. Click on Next<\/strong> 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.<\/li>\n<\/ol>
            \n
            \"SQL<\/figure>\n<\/div>
              \n
            1. Click on Next<\/strong> and specify database file names and also your choice whether to overwrite existing databases at the destination.<\/li>\n<\/ol>
              \n
              \"SQL<\/figure>\n<\/div>
                \n
              1. Click on Next<\/strong> and select the additional objects (outside the SQL Server 2005 databases) that you want to copy to SQL Server 2008 server<\/strong>.<\/li>\n<\/ol>
                \n
                \"SQL<\/figure>\n<\/div>
                  \n
                1. Click on Next<\/strong> 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.<\/li>\n<\/ol>
                  \n
                  \"SQL<\/figure>\n<\/div>
                    \n
                  1. Click on Next<\/strong> 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<\/strong>. These settings will specify the user account that will be used to run the package.<\/li>\n<\/ol>
                    \n
                    \"SQL<\/figure>\n<\/div>
                      \n
                    1. Click Next<\/strong> 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<\/strong> to start the migration.<\/li>\n<\/ol>
                      \n
                      \"SQL<\/figure>\n<\/div>
                        \n
                      1. When the upgrade completes, a success screen will be displayed indicating that the process has completed.<\/li>\n<\/ol>
                        \n
                        \"SQL<\/figure>\n<\/div>

                        Pros & Cons of the above-mentioned method<\/strong><\/h2>

                        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:<\/p>

                          \n
                        1. It is too lengthy and complicated<\/li>\n\n\n\n
                        2. Configuration problems in any version of SQL Server can prevent you from upgrading<\/li>\n\n\n\n
                        3. You can never really be sure if and when any of the following SQL database upgrading issues may arise:\n