Wednesday, September 15, 2010

How to restore a sharepoint web app from a SQL backup

You may want to restore data for 2 general reasons:
1. Something terrible happened on Prod and you need to bring the system up from the backup.
(the principle to backup sql databases stays the same as for app - the full backup once a week, and differential - once a day)
2. You need to have Staging to testing purposes (which is highly recommended!)

Whatever reason you have the process of restoring will look like this:
1. On env where you want to restore - restore the sql backup into new sql database.
2. On sharepoint env where you want to have the restored web app - go to the Central Administration - Create web application - !Choose the sql database which you have got after the first step. 
3. Create a new site collection on the top level ( it's a kind of magic for me (I took it from Restore a SharePoint Site from a Database Backup).

   It will help you to identify permission problem on SQL side. At this step you may see the error in ULS log:
 Cannot open database "[Database Name]" requested by the login. The login failed.  Login failed for user '[user_name]'. That's simply means that you have to add your application pool account to the SQL db.

4. Go to the restore site collection administrators - and check if those administrator still valid in new env, or change them as you wish to have it
5. BINGO!) You are done)
If for some reason you don't want to restore the way I describe , you are welcome to leave your suggestion and read the following related articles:

P.S. Experiencing the error "has been upgraded to a newer version of SharePoint. Please upgrade this SharePoint application server before attempting" - refer to the post Where to look for SharePoint version?

P.S.S. If you do such restore on the regular basis, this simple truth makes you life easier:
1. Use the same port for restored web application as you use in source (assuming, prod) web application;
2. Use the same name of db on the restored env as you use in the source environment.