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

BeerTime
BeerTime used Ask the Experts™
on
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.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Walter CurtisSharePoint AED
Distinguished Expert 2018
Commented:
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...
SQL / SharePoint Engineer
Commented:
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: https://technet.microsoft.com/en-us/library/cc262792.aspx. 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.
Walter CurtisSharePoint AED
Distinguished Expert 2018

Commented:
Again, what are you trying to do?
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Greg BurnsSQL / SharePoint Engineer

Commented:
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 AED
Distinguished Expert 2018

Commented:
@ 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 Engineer

Commented:
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.

Author

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 AED
Distinguished Expert 2018

Commented:
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...

Author

Commented:
Thanks again for your help guys!
Walter CurtisSharePoint AED
Distinguished Expert 2018

Commented:
Hope all worked out well for you!

Thanks

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial