MSEndpointMgr

Perform a test upgrade of the database before ConfigMgr 2012 R2 installation

Before you go ahead and begin the installation of ConfigMgr 2012 R2 in your ConfigMgr 2012 SP1 hierarchy, it’s recommended that you perform a test upgrade of the database. This post will help you along the way to successfully perform just that. Let’s get started.

Overview

  • Install a VM with SQL Server
  • Copy the database backup
  • Attach the database
  • Perform a test-upgrade

Install a VM with SQL Server

This blog post will not cover the steps of installing any supported SQL Server version. You can find guides on how to do that here:

What’s most important though is that the SQL Server version that you install on the new VM, must be the exact same version that you’re running in production with ConfigMgr 2012 SP1. So if you’re running the ConfigMgr 2012 SP1 database on a server with SQL Server 2008 R2 SP2, that’s the SQL Server version you’ll need for the test upgrade scenario. In this post I’ll be test upgrading a database that was created on SQL Server 2012 CU3.

Copy the database backup

1. Once you have a VM with the same SQL Server version as you’ve got running in production, it’s time to copy the database files from your Site Backup location. You’ll find that location if you go to AdministrationSite Configuration Sites and selects your Primary Site server. Click on Site Maintenance, select Backup Site Server and click Edit. You’ll see the location in the Backup destination field. In my case it’s \\CM01\ConfigMgrBackups.
2. Now copy the two files from \\CM01\ConfigMgrBackups\P01Backup\SiteDBServer to the newly created VM. I’ve choosen to copy the file to C:\Databases.
47_1

Attach the database

1. On your server with the test SQL Server installed, launch SQL Server Management Studio.
2. In he left pane, right-click Databases and click Attach.
47_2
3. Click on Add, expand C:\Databases (or the location that you chose to store the two MDF files in) and click OK.
47_3
4. If no error occured, click OK in the Attach Databases window.
47_4
5. In the left pane of SQL Server Management Studio, expand SecurityLogins. Right-click on the user or group that represents the currently logged on account (in my case CONTOSO\administrator) and select Properties.
47_5
6. Click on User Mapping in the left pane. Check the box in the Map column next to the newly attached database. In Database role membership for: <database_name>, select db_owner. Click OK.
47_6
7. Close SQL Server Management Studio.

Perform a test-upgrade

Since the ConfigMgr 2012 R2 RTM bits yet havn’t been released (they will soon), download the Preview bits and extract the contents to C:\Install\ConfigMgr2012R2Preview on the VM.
1. Open an elevated command prompt and navigate to C:\Install\ConfigMgr2012R2Preview\SMSSETUP\BIN\X64.
2. Run the following command:

setup.exe /testdbupgrade <database_name>

Remember to change <database_name> to the name of your attached database. In my case it was CM_P01.
47_7
3. In the window that appears, click Begin TestDBUpgrade. Click Yes if you get a prompt asking if you want to continue.
47_8
47_9
4. You can now follow the test upgrade of the database with CMTrace by opening C:\ConfigMgrSetup.log.
47_10
5. Once the test upgrade has completed, you should see a line with the following text:
INFO: Configuration Manager Setup has successfully upgraded the database.
47_11
That’s all. Good luck with your ConfigMgr 2012 R2 installation!

Nickolaj Andersen

Chief Technical Architect and Enterprise Mobility MVP since 2016. Nickolaj has been in the IT industry for the past 10 years specializing in Enterprise Mobility and Security, Windows devices and deployments including automation. Awarded as PowerShell Hero in 2015 by the community for his script and tools contributions. Creator of ConfigMgr Prerequisites Tool, ConfigMgr OSD FrontEnd, ConfigMgr WebService to name a few. Frequent speaker at conferences such as Microsoft Ignite, NIC Conference and IT/Dev Connections including nordic user groups.

12 comments

  • This issue was due to corrupt SQL tables. We moved the ConfigMan_DB to a new SQL Server, obtained a clean DB copy, successfully ran the test database upgrade procedure.
    KACouillard

  • I am running this as descripted in this excellent article. We want to update from 1511 to 1610. Using a backed up DB from the SQL server and source files copied from the downloaded package on the SCCM server, all mounted on a separate test computer. The SQL version matches the production server.
    From ConfigMgrSetup.log below, not sure what this error message is telling me. During “PreUpgrade Cleanup scripts”.
    I have checked the SQL Server and Agent logs, they are normal.
    Thank you so much for your assistance!
    KACouillard
    SQL MESSAGE: – End – Creating SchemaChanges Trigger
    Configuration Manager Setup 4/3/2017 3:43:22 PM
    10052 (0x2744)
    INFO: Beginning SQL Server script execution. Configuration Manager Setup 4/3/2017 3:43:44 PM
    10052 (0x2744)
    INFO: Executing PreUpgrade Cleanup scripts.
    Configuration Manager Setup 4/3/2017 3:43:44 PM
    10052 (0x2744)
    *** ~– adjusting the index to reduce table size for bug 309097 ~IF EXISTS (SELECT * FROM dbo.SMSData WHERE SMSBuildNumber < 7730) ~BEGIN ~ IF EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[INSTALLED_SOFTWARE_DATA]') ~ AND name = N'sw_data_hash_code') ~ DROP INDEX sw_data_hash_code ON INSTALLED_SOFTWARE_DATA ~ ~ IF EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[INSTALLED_SOFTWARE_DATA]') ~ AND name = N'sw_data_idx1') ~ DROP INDEX sw_data_idx1 ON INSTALLED_SOFTWARE_DATA ~ ~ IF EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[INSTALLED_SOFTWARE_DATA]') ~ AND name = N'sw_data_idx2') ~ DROP INDEX sw_data_idx2 ON INSTALLED_SOFTWARE_DATA ~ ~ IF EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[INSTALLED_SOFTWARE_DATA_Summary]') ~ AND name = N'INSTALLED_SOFTWARE_DATA_Summary_idx_ByCode') ~ DROP INDEX INSTALLED_SOFTWARE_DATA_Summary_idx_ByCode ON INSTALLED_SOFTWARE_DATA_Summary ~END
    Configuration Manager Setup 4/3/2017 3:43:59 PM
    10052 (0x2744)
    *** [HY000][0][Microsoft][SQL Server Native Client 11.0]Unspecified error occurred on SQL Server. Connection may have been terminated by the server.
    Configuration Manager Setup 4/3/2017 3:43:59 PM
    10052 (0x2744)
    ERROR: SQL Server error: [HY000][0][Microsoft][SQL Server Native Client 11.0]Unspecified error occurred on SQL Server. Connection may have been terminated by the server.
    Configuration Manager Setup 4/3/2017 3:43:59 PM
    10052 (0x2744)
    *** ~– adjusting the index to reduce table size for bug 309097 ~IF EXISTS (SELECT * FROM dbo.SMSData WHERE SMSBuildNumber < 7730) ~BEGIN ~ IF EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[INSTALLED_SOFTWARE_DATA]') ~ AND name = N'sw_data_hash_code') ~ DROP INDEX sw_data_hash_code ON INSTALLED_SOFTWARE_DATA ~ ~ IF EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[INSTALLED_SOFTWARE_DATA]') ~ AND name = N'sw_data_idx1') ~ DROP INDEX sw_data_idx1 ON INSTALLED_SOFTWARE_DATA ~ ~ IF EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[INSTALLED_SOFTWARE_DATA]') ~ AND name = N'sw_data_idx2') ~ DROP INDEX sw_data_idx2 ON INSTALLED_SOFTWARE_DATA ~ ~ IF EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[INSTALLED_SOFTWARE_DATA_Summary]') ~ AND name = N'INSTALLED_SOFTWARE_DATA_Summary_idx_ByCode') ~ DROP INDEX INSTALLED_SOFTWARE_DATA_Summary_idx_ByCode ON INSTALLED_SOFTWARE_DATA_Summary ~END
    Configuration Manager Setup 4/3/2017 3:43:59 PM
    10052 (0x2744)
    *** [22007][211][Microsoft][SQL Server Native Client 11.0][SQL Server]Possible schema corruption. Run DBCC CHECKCATALOG.
    Configuration Manager Setup 4/3/2017 3:43:59 PM
    10052 (0x2744)
    ERROR: SQL Server error: [22007][211][Microsoft][SQL Server Native Client 11.0][SQL Server]Possible schema corruption. Run DBCC CHECKCATALOG.
    Configuration Manager Setup 4/3/2017 3:44:00 PM
    10052 (0x2744)
    INFO: Executing SQL Server command: < ~– adjusting the index to reduce table size for bug 309097 ~IF EXISTS (SELECT * FROM dbo.SMSData WHERE SMSBuildNumber
    Configuration Manager Setup 4/3/2017 3:44:00 PM
    10052 (0x2744)
    ERROR: Failed to execute SQL Server command ~– adjusting the index to reduce table size for bug 309097 ~IF EXISTS (SELECT * FROM dbo.SMSData WHERE SMSBuildNumber < 7730) ~BEGIN ~ IF EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[INSTALLED_SOFTWARE_DATA]') ~ AND name = N'sw_data_hash_code') ~ DROP INDEX sw_data_hash_code ON INSTALLED_SOFTWARE_DATA ~ ~ IF EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[INSTALLED_SOFTWARE_DATA]') ~ AND name = N'sw_data_idx1') ~ DROP INDEX sw_data_idx1 ON INSTALLED_SOFTWARE_DATA ~ ~ IF EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[INSTALLED_SOFTWARE_DATA]') ~ AND name = N'sw_data_idx2') ~ DROP INDEX sw_data_idx2 ON INSTALLED_SOFTWARE_DATA ~ ~ IF EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[INSTALLED_SOFTWARE_DATA_Summary]') ~ AND name = N'INSTALLED_SOFTWARE_DATA_Summary_idx_ByCode') ~ DROP INDEX INSTALLED_SOFTWARE_DATA_Summary_idx_ByCode ON INSTALLED_SOFTWARE_DATA_Summary ~END
    Configuration Manager Setup 4/3/2017 3:44:00 PM
    10052 (0x2744)
    Failed to create process of SetupWpf.exe. return value 1
    1/1/1601 12:00:00 AM
    2003259403 (0x7767500B)
    Setup cannot upgrade the ConfigMgr database. Contact your SQL administrator.

  • Used these instructions for 2012R2 to upgrade to Current Branch and it worked great. Thanks

  • Thanks for your article.
    Kindly add the Information, that you require a Default instance at SQL Server. It is not possible to perform the upgrade with a named instance.
    Warm Regards
    Mr. Pablo

  • Great article!
    Does the test upgrade make changes to the test database? In other words: is it possible to first run the test-upgrade on the test database and then run the actual upgrade again on the test database?
    Greetz,
    Joost

  • Great article. Your blog is my starting point. However, do you know what we need to do on DPs post this upgrade.

    • Hi Durgesh,
      Thank you!
      DP’s will automatically be updated in an in-place upgrade, for instance when installing a Service Pack or new release.
      Regards,
      Nickolaj

  • Hello,
    Thanks for this.
    However when I try to do exactly what you’re doing, I’m getting this error message in the log :
    ERROR: SQL Server error: [42S02][208][Microsoft][SQL Server Native Client 11.0][SQL Server]Invalid object name ‘dbo.vSMS_ConfigurationItems’.
    And that’s because I have much less views in my copy database than in my working database.
    For instance “dbo.vSMS_ConfigurationItems” is present in my working database but not present in my copy database…
    And yet I’m using the files in the SCCM site backup location.
    I’ve seen another person getting the same problem here :
    https://social.technet.microsoft.com/Forums/en-US/74c2522d-ac03-491e-b93a-9750d1d965a4/invalid-object-sql-error-when-running-setupexe-testdbupgrade?forum=configmanagerdeployment
    Do you know from where this issue can come ?
    Best regards.

  • Thank you for this. I’m actually considering restoring my DC, SQL and SCCM VMs, assigning a private network and performing the update on the restored versions in a test environment.

    • Hi Miha,
      Thank you! It is possible to do this, but why not just perform it on a member server? Then you don’t have to restore everything to a test environment. The setup will only run a test upgrade of the database specified in the command line.
      Regards,
      Nickolaj

      • Sounds good as well, might just go that route, thank you.

Sponsors