How to Restore Tridion Databases


Posted on November 30, 2014 at 10:32 PM


For this post lets make a reminder on how to restore tridion databases using SQL Server. I have done this many time in the past and probably will in the future so this post will be a good source of information.

Before starting database restore, the services below needs to be stopped (on CM machine):

  1. COM+ Applications (Administrative Tools -> Component Services -> Computer -> COM+ Applications)
  2. com+
    • Right click on SDL Tridion Content Manager, select Disable.
    • Right click on SDL Tridion Content Manager, select Shut down

  3. IIS (Administrative Tools -> Internet Information Services (IIS) Manager)
  4. iis
    • On the left side click on server name just above Application Pools.
    • On the right side, a panel Manage Server will appear.
    • Click Stop

  5. Tridion services (Administrative Tools -> Services)
    • Find the Tridion services and stop them all, one by one.
    • After you stop Tridion services, they should look like this:
    services

Restore Tridion database from the backup, one at a time.

  1. Righ-click on Databases and choose Task\Restore\Database
  2. restore
  3. You should see the following. Fill out the fields as folloing:
  4. restore
  5. Click on Device and choose a path by clicking Add and finding backup file.
  6. restore
  7. In the Options tab select Overwrite existing database and Close existing connections to destination database
  8. restore
  9. Click OK.

To complete the restore some scripts must be executed using SQL Server Management Studio in order for Tridion to work properly.

  1. Run the following SQL script to recover orphaned db users:
  2. 	
    USE Tridion_cm
    GO
    sp_change_users_login 'Auto_Fix', 'TCMDBUser'
    GO
    USE Tridion_Broker
    GO
    sp_change_users_login 'Auto_Fix', 'TridionBrokerUser'
    GO
    					
  3. Clear the columns which store published pages on Tridion_cm database
  4. 	
    USE Tridion_cm;
    
    TRUNCATE TABLE dbo.PUBLISH_STATES;
    TRUNCATE TABLE dbo.QUEUE_MESSAGES;
    TRUNCATE TABLE dbo.QUEUE_FILTERS;
    TRUNCATE TABLE dbo.PUBLISH_TRANSACTIONS;
    					
  5. Restart services

After logging to Tridion update configuration for publication target if needed and clean the web server from pages that were published before database restore was done.


Site search