Recover Site collection to a Farm using only the SQL Content Database from a different farm

Hi all,

first of all, it has been a while since I wrote something on my blog here. Well, here I am again.

This time a had an issue that after I migrated a bunch of site collections (about 200 site collections) from farm1 to farm2, I realized that I had forgotten one site collection. Of course by the time the owner of the site found out, farm1 had been completely decommissioned and there was no way to get it back online. 

The only thing I could get was a backup of the SQL databases from our SQL guys. Well, after a little research on the net, etc. I managed to recover the site collection quite easily.

Basically this is what I did:

First of all I used a test MOSS server that I have running here in the same Active Directory environment (for the permissions sake). If you don’t have one running, then install one and have it on the same Service Pack as the original farm

1. Restore the content Database containing the site collection to the database server of the test MOSS server. (or have it restored by your SQL guys, like I did)

2. Simply create a new Web application, which does not need to have the same name, port, etc. as the original web application in Central Administration and have that Web Application point to the restored database instead of creating a new database.

3. At this point you can already access the site using the parameters of the new web application. it will also show up when you use stsadm -o enumsites for you web app or show up in the Site Collection List of your web application in Central Admin. In my case I had an error at first when loading the site the first time, but then I never saw it again.

4. Now, backup the site using stsadm -o backup -url <your url> -filename <yourfile.dat>

5. and recover it on your farm2 environment with stsadm -o restore -url <your url> -filename <yourfile.dat>

that’s it. Sounds simple, is simple and it worked for me [H]


16 thoughts on “Recover Site collection to a Farm using only the SQL Content Database from a different farm

  1. Hi Dirk, STSADM rules, as usual! 🙂

    Just wanted to add a note that there are 3rd party products that could possibly make this task easier for you. And as I happen to work on one of such products, I cannot stay silent when you describe exactly the situation we designed it for! 🙂

    The product I work on is called Quest Recovery Manager for SharePoint (, and it allows you to granularly restore SharePoint objects (sites, lists, documents, etc.) from a SQL backup, even when the original farm is gone like in your case.

    So… even though real admins use stsadm this could probably save you some time 😉 by skipping steps 2, 3 and 4 in your procedure. And almost skipping step 1 (you would still need to get the .bak file from tape though).

    Ilia Sotnikov

  2. Hi,

    any problems if we create the new webapplication

    on the production server with all post steps ?

    We have on the test system newer  webparts as example.

    I think it’s no problem or ?

    Best regards


  3. Hi Heiko,

    You should not have any problem restoring a content database form one web application to a complete new web application on the same farm.

    The only restore that will not work is when you try to restore a site collection from the same web application and content database in the same web application and content database because of duplicate id’s.

    best regards,


  4. Hi,

    I completely understand this process and have used it in the past. However, in my current farm, when I restore a content database from one web app to another, it seems that no sites are migrated. The Applicaiton Mgmt >Content Databases page shows “Current Number of sites = 0”.

    Any idea why this should be happening.

  5. Awesome post. I had a test wss setup that didn’t really have anything crucial, mostly my implementation notes. So being able to restore was helpful but not required. The server it was running on went fubar, but since I still had the sql files and this post, I am now back in business.


  6. Unfortunately doesn’t work if the collection isn’t a top level one.
    “The specified URL is not a top-level Web site. The backup and restore operations work only for top-level Web sites. nter the URL for a site to try the operation again.”
    So still should use -o export/import =)

  7. Hi Alexander,

    this indeed only works for entire site collections and not for individual webs inside the site collection.

    best regards,


  8. Hi Alexander,

    then you most probably do not have a managed path “sites” defined in the web application where you are restoring the content database to.

    If you have managed paths defined in your original web application, such as “sites” where you took the backup from, then these managed paths must also exist on the web application where you are restoring to. Otherwise SharePoint will not recognize the path indicated in the content database a site collection.

    If you have issues with specific managed paths then: — create the new web application on the testfarm with a dummy content database
    – create the custom managed path
    – Add the restored content database through Application Management -> Content Databases -> select new web application -> Add a content database

    Otherwise I see no reason why this should not work if you are sure that portal/sites/sitecollection is really a site collection and not a subsite.

    I’ll explain using a sample scenario:
    original site collection url:
    original web application = http://portal/
    managed path in this web application: /sites/ – Wildcard inclusion

    I lost this site and I have a backup of its content database

    Now I also have a test farm. I create the following web application pointing to the restored content database:
    url: http://testfarm/
    define managed path: /sites/ – Wildcard inclusion (if not already there by default)
    Add the restored content database to this web application.
    So now I should be able to get my site back using the url http://testfarm/sites/sitecollection1

    At this point I want to back it up and restore to my original farm.

    then I just simply run:
    stsadm -o backup -url http://testfarm/sites/sitecollection -filename yourfile.dat

    on the other farm, make sure that there is a managed path for “sites” in the web application where you are restoring to and then run
    stsadm -o restore -url http://portal/sites/sitecollection1 -filename yourfile.dat (-overwrite if there is already a version there that you want to overwrite)

    Hope this helps,



  9. Dirk,

    Here’s the problem I have. I hope you can help. I accidentally deleted the sharepoint portal by going into Delete this web site>.
    I only have the sql server backup of the content database with me to restore.
    I need to get it back to the same state as before.

    As per your solution, I need to create a new web application in the same farm, but this creates a new database. Can you tell me how will I be able to use my old content db in this new web application and then how will I be able to use my old site collection.

    Thanks for your help!

  10. Hi Saffron,

    just create the web application as you normally would and you may even let it create a new database. All you need to do is go to Central Admin -> Application Management -> Manage Content databases. Select your new web application and add a content database specifying the same database name that you restored and your portal should be back.



  11. It works for me too!!!

    Just in case you need it…it is possible to restore the database in the same farm/server. Perform the next steps before the step 2 posted by Dirk!

    1.1 check this link to understand how SharePoint configuration databases and SharePoint content databases works.

    1.2. Open a new query window:

    select id from site
    delete * from sitemap where id = ‘use the id mention above’

    1.3 Continue with step 2.

  12. Dirk,

    I follow the path you desctibed but after creating a new web application with the original content database, I get “Internal Server Error” when I try to access any of the sites. The sites are listed in “application management” of the sharepoint central admin though.

    Any clues?


Leave a Reply

Your email address will not be published. Required fields are marked *