We help IT Professionals succeed at work.

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

395 Views
Last Modified: 2017-03-20
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

Walter CurtisSharePoint AED
CERTIFIED EXPERT
Distinguished Expert 2019
Commented:
This problem has been solved!
(Unlock this solution with a 7-day Free Trial)
UNLOCK SOLUTION
Greg Burns (poortatey)
Commented:
This problem has been solved!
(Unlock this solution with a 7-day Free Trial)
UNLOCK SOLUTION
Walter CurtisSharePoint AED
CERTIFIED EXPERT
Distinguished Expert 2019

Commented:
Again, what are you trying to do?
Greg BurnsGreg Burns (poortatey)

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
CERTIFIED EXPERT
Distinguished Expert 2019

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 BurnsGreg Burns (poortatey)

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
CERTIFIED EXPERT
Distinguished Expert 2019

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
CERTIFIED EXPERT
Distinguished Expert 2019

Commented:
Hope all worked out well for you!

Thanks