Restoring vCenter Database on SQL Server 2008

Hopefully you’ll never have a disaster hit your datacenter, but if you do, this guide can show you how to restore your vCenter database to your latest backup. Your vCenter database holds all the information you see in the vSphere Client and more. Although your VMs will still run your ESXi hosts without vCenter and its associated database, you lose a lot of enterprise functionality.

In my testing, I found the SQL server to which you recover can have a different name. I did not change the name of my vCenter server. All machines involved had different IP addresses and resided in a different domain. All domain service accounts were recreated in the test domain. I’m leaning towards the possibility that the vCenter server can have a different name, as well.

Verify vpxuser permissions

If you’re restoring the vCenter database to the same SQL box it was on before, you may have difficulties giving permissions back to the vpxuser local SQL account. You may come across an error when setting the User Mappings during the re-creation of the vpxuser SQL account.

If you find this happens, you can delete the vpxuser from Security > Logins as well as from Databases > System Databases > msdb > Security > Users, right-clicking the vpxuser and selecting Delete. The error I received was that the databases (msdb, in particular) already had security permissions for vpxuser. After deleting vpxuser from both spots, you can recreate it cleanly and give permissions back to each database. Since you’re re-creating the VCDB database, its security logins will have been deleted already.

Create a new VCDB database

First, perform a standard installation of SQL Server. You can use an existing box, as well. The point here is that you’ll be starting with an empty vCenter database – a brand new one, whether that is created in a new SQL instance or an existing instance, the VCDB database will be brand new.

This first part will be the same as if you were installing a new instance of vCenter. Right-click Databases from within SQL Management Studio and select New Database. Give it the name VCDB and close the window.

Recreate the vpxuser account

If you’re restoring to a new SQL installation, recreate the vpxuser SQL user account. This is just like the initial installation. Be sure to give dbo permissions for both the msdb and VCDB databases as shown below.

Copy vCenter database backups to SQL Server

You’ll need to copy as many database backups as you want to restore to the SQL server box. In my case, I copied the latest full and differential backups. You can place the backups in several locations that the SQL restore process can see, but I found copying them to the root of one of the drives was easiest, in my case, E:\, which is where I installed SQL. You could also use C:\ if you like.

Restore the latest full backup

Right-click the VCDB database and go to Tasks > Restore > Database. Under “Source for restore,” choose “From device:” and click the “…” browse button to the right.

In the Specify Backup window, leave the backup media selected to File and click Add. By default, SQL wants to restore from the default backup location. Browse to where you copied the vCenter backups, in my case, E:\, and highlight your Full backup file. Click OK.

To verify you’ve selected the correct backup file, in the Specify Backup window, highlight your backup file and click Contents. A new window will open and you can see which type of backup file you’ve selected. It will say Full or Differential under Component.

Click OK to exit the Specify Backup window. Then click on the Restore check box next to the backup filename.

We’re done on the General page. In the left pane, select Options. In the Restore options, check the box next to “Overwrite the existing database (WITH REPLACE).” Under Recovery state, select the radio button next to “Leave the database non-operational, and do not roll back uncommitted transactions. Additional transaction logs can be restored. (RESTORE WITH NORECOVERY) This option will enable us to restore a differential backup. However many backups you restore, do NOT choose this option for the last backup you restore. The last backup will use the option above this, the RESTORE WITH RECOVERY that leaves the database ready to use.

Press OK when satisfied and wait for the backup to be restored. You should get a success message like this:

After clicking OK, all the windows will close and you’ll be left with the VCDB database in a Restoring state.

Restore differential backups and transaction logs

Right-click the VCDB database again and repeat the restore procedure for the differential backups. Again, for the last restore, be sure to select the top-most radio button under the Recovery state options as shown below.

Note: When restoring transaction logs, instead of choosing to restore a Database, choose to restore Transaction Logs and then restore to a ready-to-use state.

Recreate/reconnect vCenter Server

You’re now done with the SQL server portion of the vCenter database restore. You’ll need to reconnect the vCenter server as well. It’s essentially the same procedure as if you were connecting a vCenter server for the first time. Just be sure to change your ODBC connection if your SQL server has changed hostnames or IP addresses.

If you rebuilt your vCenter server, you’ll have to download and install the SQL Native Client for whichever version of SQL you have, 2005 or 2008. Install it, and then create a 64-bit System DSN to your SQL server.

Once your ODBC connection is created, you can reinstall vCenter. Connecting to vCenter with the vSphere Client, you should see your familiar environment with all your clusters, hosts, VMs, logs, events, and licenses before your SQL crash.

Google