none
SCCM 2007 R3 SQL database restore

    Question

  • Hi

    We have a single SCCM 2007 R3 site infrastructure with a separate server hosting the Management Point role and the SQL 2005 database hosted on a SQL cluster separate to the site server. All other sites roles are on the site server. We're in the unfortunate position of having had a catastrophic failure of the SQL database cluster.

    Firstly I'd just like to verify that in this scenario I'm still going to have to do a full site restore using the last backup created by the site maintenance task. There is a separate backup available of the SQL database (although I'm aware it's not recommended, there are a couple of other SQL instances on the cluster that need backing up). Presumably if this is restored when the site server attempts to communicate with the database again it'll be out of sync so will fail?

    Secondly, given this scenario, I'm still a bit unclear about how the SQL environment needs to be on the cluster before performing the restore. Do we need to manually create a database with the same name/settings as the original database or will the restore job attach the database from backup given the relevant SQL environment is available on the cluster?

    Thanks in advance

    Stewart

    Monday, September 12, 2011 3:54 PM

All replies

  • Yes, the maintenance task will allow you to restore the site. What you do in the event of a restore, is install new "new" ConfigMgr. site using the same name, sitecode etc as the old one. After that, run the restore wizard and it will guide you thru the process.
    Kent Agerlund | My blogs: http://blog.coretech.dk/author/kea/ and http://scug.dk/ | Twitter @Agerlund | Linkedin: /kentagerlund
    Monday, September 12, 2011 4:15 PM
    Moderator
  • Hi Kent

    Thanks for your reply. I've been reading the documentation here http://technet.microsoft.com/es-pe/library/bb680456(en-us).aspx and while it tells me how to follow through the site repair wizard, I can't see any information about how the server(s) need to be prepared beforehand. Is the approach for repairing a site the same irrespective of which component is down? Are you saying that rather than run the site repair wizard from my central server in its current state (bearing in mind it's just the database that's broken), I first need to fully uninstall ConfigMgr and reinstall it using the same settings, names etc as previously, then run the repair wizard?

    Many thanks

    Stewart

    Tuesday, September 13, 2011 9:56 AM
  • Kent was talking about site restore and you are talking about site repair.

    If you have a good SCCM backup then I would go with site restore option.


    Anoop C Nair - Twitter @anoopmannur

    MY BLOG:  http://anoopmannur.wordpress.com

    SCCM Professionals

    This posting is provided AS-IS with no warranties/guarantees and confers no rights.

    Tuesday, September 13, 2011 10:13 AM
  • Secondly, given this scenario, I'm still a bit unclear about how the SQL environment needs to be on the cluster before performing the restore. Do we need to manually create a database with the same name/settings as the original database or will the restore job attach the database from backup given the relevant SQL environment is available on the cluster?

    Thanks in advance

    Stewart


    As Kent alluded too, reconfigure your site as you did before, and that would mean putting the newly built ConfigMgr servers SQL into the cluster and preparing it as before. once you have a site operational again, and in the same configuration (I don't mean client agent settings etc, just the infrastructure) you can then do a restore and that'll take care of everthing for you.
    Tuesday, September 13, 2011 12:06 PM
    Moderator
  • Thanks for your responses.

    I think I'm getting confused with the terminology here and was using the terms 'repair' and 'restore' interchangeably. 

    I'm still unclear though, and please accept my apologies but I want to get this absolutely clear in my head. Being as my central site itself is fine, is it necessary for me to do any reconfiguration on the site server before doing the restore? As all the site settings, name, sitecode etc are intact and haven't changed, will the restore option simply overwrite the existing data with that from the backup or does the restore have to be run against an essentially 'vanilla' ConfigMgr installation? Could I simply ensure that the SQL infrastructure (the bit that broke) is as it was before the failure then run the restore?

    Also what would need to be done with the third server hosting the management point role?

    Thanks again for your help

    Stewart

    Tuesday, September 13, 2011 4:04 PM
  • May be the below guide will help you to get more details ?

    http://www.windows-noob.com/forums/index.php?/topic/750-how-can-i-backup-my-sccm-server/


    Anoop C Nair - Twitter @anoopmannur

    MY BLOG:  http://anoopmannur.wordpress.com

    SCCM Professionals

    This posting is provided AS-IS with no warranties/guarantees and confers no rights.

    Tuesday, September 13, 2011 5:22 PM
  • I still can't see anything in that guide about whether I can perform a site restore over my existing site settings or whether it's necessary to do a site reset first, or reinstall ConfigMgr on my site server before the restore?
    Wednesday, September 14, 2011 2:07 PM
  • I still can't see anything in that guide about whether I can perform a site restore over my existing site settings or whether it's necessary to do a site reset first, or reinstall ConfigMgr on my site server before the restore?

    I would always prefer to have a clean one rather than using the existing one to restore.

    Anoop C Nair - Twitter @anoopmannur

    MY BLOG:  http://anoopmannur.wordpress.com

    SCCM Professionals

    This posting is provided AS-IS with no warranties/guarantees and confers no rights.

    Wednesday, September 14, 2011 2:16 PM
  • I still can't see anything in that guide about whether I can perform a site restore over my existing site settings or whether it's necessary to do a site reset first, or reinstall ConfigMgr on my site server before the restore?

    As said above, build out a new server, same server name, host name, configure as was before, but no need to adjust client agent properties etc, make sure all roles installed and working, make sure you've got SQL off system and working, then restore over the top of it. The documentation for restore does state all of this.
    Friday, September 16, 2011 1:03 PM
    Moderator
  • Hi SMS Marshall

    Thanks for your reply. Can you please send me a link to the area which holds the information specific to how the server environment must be prepared prior to a restore or whether it is possible to carry out a restore over an existing site given that the database only has failed?

    I have been searching here and cannot find this information. 

    Friday, September 16, 2011 2:10 PM
  • Hi SMS Marshall

    Thanks for your reply. Can you please send me a link to the area which holds the information specific to how the server environment must be prepared prior to a restore or whether it is possible to carry out a restore over an existing site given that the database only has failed?

    I have been searching here and cannot find this information. 


    You need to follow the same process as the installtion of the new site however just use the same site code, Hostname etc... as mentioned by Rob.

    Anoop C Nair - Twitter @anoopmannur

    MY BLOG:  http://anoopmannur.wordpress.com

    SCCM Professionals

    This posting is provided AS-IS with no warranties/guarantees and confers no rights.

    Friday, September 16, 2011 2:20 PM
  • Hi SMS Marshall

    Thanks for your reply. Can you please send me a link to the area which holds the information specific to how the server environment must be prepared prior to a restore or whether it is possible to carry out a restore over an existing site given that the database only has failed?

    I have been searching here and cannot find this information. 


    You need to follow the same process as the installtion of the new site however just use the same site code, Hostname etc... as mentioned by Rob.

    Anoop C Nair - Twitter @anoopmannur

    MY BLOG:  http://anoopmannur.wordpress.com

    SCCM Professionals

    This posting is provided AS-IS with no warranties/guarantees and confers no rights.

    Thanks An00p

    I have read the previous post, I'm just interested in where this is detailed in the Microsoft documentation as SMS Marshall tells me it's all in there yet I'm struggling to find it.

    Friday, September 16, 2011 3:03 PM
  • Hi SMS Marshall

    Thanks for your reply. Can you please send me a link to the area which holds the information specific to how the server environment must be prepared prior to a restore or whether it is possible to carry out a restore over an existing site given that the database only has failed?

    I have been searching here and cannot find this information. 


    You need to follow the same process as the installtion of the new site however just use the same site code, Hostname etc... as mentioned by Rob.

    Anoop C Nair - Twitter @anoopmannur

    MY BLOG:  http://anoopmannur.wordpress.com

    SCCM Professionals

    This posting is provided AS-IS with no warranties/guarantees and confers no rights.

    Thanks An00p

    I have read the previous post, I'm just interested in where this is detailed in the Microsoft documentation as SMS Marshall tells me it's all in there yet I'm struggling to find it.


    Hi - I hope you are looking for this?

    Recovering Configuration Manager Sites

    http://technet.microsoft.com/en-us/library/bb680751.aspx


    Anoop C Nair - Twitter @anoopmannur

    MY BLOG:  http://anoopmannur.wordpress.com

    SCCM Professionals

    This posting is provided AS-IS with no warranties/guarantees and confers no rights.

    Friday, September 16, 2011 4:37 PM
  • Hi SMS Marshall

    Thanks for your reply. Can you please send me a link to the area which holds the information specific to how the server environment must be prepared prior to a restore or whether it is possible to carry out a restore over an existing site given that the database only has failed?

    I have been searching here and cannot find this information. 


    You need to follow the same process as the installtion of the new site however just use the same site code, Hostname etc... as mentioned by Rob.

    Anoop C Nair - Twitter @anoopmannur

    MY BLOG:  http://anoopmannur.wordpress.com

    SCCM Professionals

    This posting is provided AS-IS with no warranties/guarantees and confers no rights.

    Thanks An00p

    I have read the previous post, I'm just interested in where this is detailed in the Microsoft documentation as SMS Marshall tells me it's all in there yet I'm struggling to find it.


    Hi - I hope you are looking for this?

    Recovering Configuration Manager Sites

    http://technet.microsoft.com/en-us/library/bb680751.aspx


    Anoop C Nair - Twitter @anoopmannur

    MY BLOG:  http://anoopmannur.wordpress.com

    SCCM Professionals

    This posting is provided AS-IS with no warranties/guarantees and confers no rights.

    Hi

    Yes, that's precisely what I have been reading. There's no information within that documentation that answers the specific questions I've asked. However, I'm now satisfied given the responses from yourself and others what is required.

    Many thanks

    Friday, September 16, 2011 5:16 PM
  • Ok, :)

    To install SCCM you can go through STEP by STEP http://www.windows-noob.com/forums/index.php?/topic/1064-sccm-2007-guides/ 

    To Back UP and Restote you can go through

    http://www.windows-noob.com/forums/index.php?/topic/750-how-can-i-backup-my-sccm-server/

     

     


    Anoop C Nair - Twitter @anoopmannur

    MY BLOG:  http://anoopmannur.wordpress.com

    SCCM Professionals

    This posting is provided AS-IS with no warranties/guarantees and confers no rights.

    Friday, September 16, 2011 5:24 PM
  • Hi SMS Marshall

    Thanks for your reply. Can you please send me a link to the area which holds the information specific to how the server environment must be prepared prior to a restore or whether it is possible to carry out a restore over an existing site given that the database only has failed?

    I have been searching here and cannot find this information. 


    You need to follow the same process as the installtion of the new site however just use the same site code, Hostname etc... as mentioned by Rob.

    Anoop C Nair - Twitter @anoopmannur

    MY BLOG:  http://anoopmannur.wordpress.com

    SCCM Professionals

    This posting is provided AS-IS with no warranties/guarantees and confers no rights.

    Thanks An00p

    I have read the previous post, I'm just interested in where this is detailed in the Microsoft documentation as SMS Marshall tells me it's all in there yet I'm struggling to find it.


    Hi - I hope you are looking for this?

    Recovering Configuration Manager Sites

    http://technet.microsoft.com/en-us/library/bb680751.aspx


    Anoop C Nair - Twitter @anoopmannur

    MY BLOG:  http://anoopmannur.wordpress.com

    SCCM Professionals

    This posting is provided AS-IS with no warranties/guarantees and confers no rights.

    Hi

    Yes, that's precisely what I have been reading. There's no information within that documentation that answers the specific questions I've asked. However, I'm now satisfied given the responses from yourself and others what is required.

    Many thanks


    Well I had a look through the documentation quickly and I cannot find the reference I've told you to find. I'll look again, and ping an email to smsdocs@microsoft.com to ask where it has gone :-)
    Wednesday, September 21, 2011 12:25 PM
    Moderator
  • Sorry to bring an old thread back but it was the closest one to my situation. I wanted to thank the contributors in this thread as it allowed me to restore my SCCM environment and share my own experience.

    We suffered a loss of our standalone SQL 2008 box with otherwise intact SCCM 2007 R3 Server. I had a good backup of the SCCM site and DB from a week ago. The suggestion to uninstall SCCM, reinstall and then do a restore didn't make much sense to me, so I went and tried to run the Restore wizzard with a newly rebuilt SQL 2008 server in place. First it failed on verifying backup phase complaining about SQL login, even though both sccm computer and user running wizard had local admin rights on sql and in the DB engine. So i put a copy of the backed up DB on the SQl server and then ran the wizard and this time got past the verification stage. Then wizard hung one time on stopping processes. After two more runs in went forward. Next wizard failed on restoring registry and corrupted the site. From this point on it was not possible to run wizard as it was not recognizing the existing sccm installation. I ran the SCCM SP2 setup which passed the prerequisites but failed when it had to empy the existing DB, I guess I should have selected a new DB instead. From here on, the only option was to uninstall SCCM site. Installed with same site code, same installation directory as original install and new DB. Restore worked perfectly this time restoring my SCCM environment to the time of the backup.

    I have no idea why a restore should cause this many complications but it did. I am planning on migrating to Config MGR 2012 SP1 pretty soon.

    Friday, February 22, 2013 4:20 PM