SharePoint 2013/ SQL 2014 - content database backup and restore from Prod to Dev

hi everyone,

I have SharePoint 2013 and SQL 2014 server in PROD and DEV environments (separate systems for each environments). I wanted to know the best practice for backing up the SharePoint content database from Prod and restoring to Dev environment (refreshing the data). My understanding is that when refreshing the SQL databases the content will be overwritten but can still use the same site within SharePoint - please help!

Thanks in advance.
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Walter CurtisSharePoint AEDCommented:
Basically, refreshing "data" is a dangerous proposition. However, going from Prod to Dev may not be too destructive if you are prepared to lose some data.  You should never go from Dev to prod though, the risk of overwriting production data it too high.

Here is why:

IF both farm have the same site collections, restoring a backup of prod in to Dev via a SQL restore will add any data in Dev that Prod has and Dev does not. If data has been deleted in Prod that was also in Dev, a restore will not remove that data from Dev. This could be a problem, maybe not a problem. If sites have been added in Prod and not in Dev, then they should be available in Dev after the restore. Conversely, if site have been deleted in Prod, they will not be deleted in Dev.

So basically, a restore is not a good way to keep the two farms in "sync", even if the goal is to do a one way refresh. Of course restore with overwrite will do just that to the content database in Dev.

What are you trying to do? Do you just want to keep the two environments in sync? Do you want to restore so that you can recover data? What is the goal? There are other options dealing with backups and restores that don't involve working directly with the SQL databases.

Hope that helps...
Greg BurnsSQL / SharePoint EngineerCommented:
If you are restoring a content database from prod to dev you need to be aware of a few things:

1. The content database has to have been previously registered with the SharePoint farm. This is accomplished by using the mount-spcontentdatabase command, which registers the unique signature of the database to the SharePoint configuration database. In other words you can't just restore a dissimilar database over an existing one and expect it to work, even if it has the same name. After you've done this, successive restores will have the same signature so SharePoint will see it as the same database.
2. Any Prod farm solution, features, web parts, content types, etc.  must be matched on the Dev farm.  An obvious example is if you installed a solution that adds branding to you sites, that solution must also be installed on dev, otherwise the site UI is broken. Sometimes the differences are subtle. In any case if you have broken references SharePoint will eventually complain about missing server side dependencies in the health monitor. And that can sometimes cause patching to fail.
3. Farms must be identically patched.  There may be some flex here, but I'd certainly expect errors if you restored a db from a farm running the February 2017 build to a farm running the November 2015 build.
4. You need to make sure the farm and web app pool identities have access to the restored database. More here: This won't be much of an issue if you are using the same service accounts on dev and prod (so the database user SID will match the sql login SIDs on the destination sql server). But if your dev farm using different service accounts you'll need to grant them access after the restore.

That's all I can think of off the top of my head. But you can see that a routine database restore needs to be carefully managed. You could probably automate it with tsql and powershell.

But you might also look into a script to do all of this via the SharePoint API. You could:

1. Enumerate and backup all site collections in a content database.
2. Move the backup files to the dev farm
3. Restore each site collection to a web app in the dev farm, with overwrite.

The benefit of that method is that SharePoint is aware of all the objects being restored and will register them correctly. Whereas restoring a sql database is transparent to SharePoint, so any missing dependencies won't be noted until SharePoint tries to use the database.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Walter CurtisSharePoint AEDCommented:
Again, what are you trying to do?
Ensure Business Longevity with As-A-Service

Using the as-a-service approach for your business model allows you to grow your revenue stream with new practice areas, without forcing you to part ways with existing clients just because they don’t fit the mold of your new service offerings.

Greg BurnsSQL / SharePoint EngineerCommented:
He said he wants to refresh the content on dev with data from prod. I think a site collection restore would accomplish the same thing without having the overhead of a sql restore.
Walter CurtisSharePoint AEDCommented:
@ Greg - Thanks for the info, there are a lot of other possibilities however, so just wondering what he has in mind. Might help in offering him an accurate solution.

Greg BurnsSQL / SharePoint EngineerCommented:
Of course. There's lots of ways to accomplish this. You could also look at third party products like DocAve Replicator. But it's spendy.
BeerTimeAuthor Commented:
Hi Walter and Greg, thanks so much for your feedback. We are currently having some search related issues with SharePoint in PROD, the purpose of this exercise is to refresh the content database so we can troubleshoot the issue safely in DEV environment. Thanks for pointing out that the SharePoint version, sites and overwrite options will have to be considered to accomplish this task. I am leaning toward Greg's suggestion of doing.

1. Enumerate and backup all site collections in a content database.
 2. Move the backup files to the dev farm
 3. Restore each site collection to a web app in the dev farm, with overwrite.

Please let me know if there is anything else I should watch out for to carry out this task. Thanks again!
Walter CurtisSharePoint AEDCommented:
My answers were geared to your question.

Now knowing what you exactly want to do, yes SharePoint backup and restore would do what you want to do, but would require a lot of manual labor. If you have a large farm, and you don't care if data is overwritten, you could do a db copy and restore from your prod farm to your dev farm. You could also do some things with a backup file of your productions database, attaching it to your dev farm and mount the database in your dev farm. Of course, PowerShell would be very useful while you do this.

After evaluating how many site collections you have will be useful to determining what direction to go in.

Good luck...
BeerTimeAuthor Commented:
Thanks again for your help guys!
Walter CurtisSharePoint AEDCommented:
Hope all worked out well for you!

It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SharePoint

From novice to tech pro — start learning today.