?
Solved

How do a backup a Sharepoint Foundation 2010 SQL 2008 R2 database with a Maintenance Plan?

Posted on 2011-03-11
31
Medium Priority
?
1,148 Views
Last Modified: 2012-05-11
I would like to schedule a weekly backup for the Content SQL 2008 database (Sharepoint Foundation 2010). However, I do not see the option to choose Maintenance Plan under Management in SQL SMS.  I use Maintenance Plans to backup other SQL DBs.  Is this not an option with Sharepoint DBs? Where do I find this function for Sharepoint SQL DBs?  Thanks
0
Comment
Question by:PDSWSS
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 17
  • 13
31 Comments
 
LVL 3

Expert Comment

by:tearman
ID: 35109475
You should be able to back up the Sharepoint databases like any normal SQL database.  Also, you might consider looking into 3rd party backup solutions like Iderasoft.  Inexpensive and allows some additional functionality with Sharepoint.
0
 
LVL 38

Expert Comment

by:Justin Smith
ID: 35110012
Do you have proper permissions in SQL?  This isnt a sharepoint issue.  SharePoint db's are treated like any other db in SQL.  Did you install the full management software when you installed SQL?
0
 

Author Comment

by:PDSWSS
ID: 35110059
Achilles:

I backup other non Sharepoint SQL DBs using Maintenance Plans in other instances.  In the Instance where I can see the Sharepoint DBs in   SQL Management Studio there is not choice of Maintenance Plans under Management.  Could this be a permissions issue? Thanks
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 38

Expert Comment

by:Justin Smith
ID: 35110107
It's something SQL related, not SharePoint related.

What is your process?  Are you using a remote SQL Manager?
0
 

Author Comment

by:PDSWSS
ID: 35110139
Just noticed that SQL Server Agent also does not appear when I view the Sharepoint DBs. However, it does appear when I view the other local SQL DBs.

I am using Remote Desktop as Admin to do this.  Thanks
0
 

Author Comment

by:PDSWSS
ID: 35128559
Achilles:
When I attempt to start SQL Server Agent (Sharepoint) - it starts for a second and stops and I get the attached  message. Any advice?   thanks
Picture-1.png
0
 
LVL 38

Expert Comment

by:Justin Smith
ID: 35128732
Not completely following you.  Are you remote desktop into the SQL Box, then launching SQL Manager?

SQL Agent will stop automatically if it's not set to "automatic".
0
 

Author Comment

by:PDSWSS
ID: 35128788
Not completely following you.  Are you remote desktop into the SQL Box, then launching SQL Manager?    YES
0
 
LVL 38

Expert Comment

by:Justin Smith
ID: 35128813
Are you sure your SharePoint instance (server\SHAREPOINT) is an R2 instance and not a SQL Express instance?
0
 

Author Comment

by:PDSWSS
ID: 35128830
Installed MS SQL 2008 R2. How could it be an SQL Express instance?
0
 
LVL 38

Expert Comment

by:Justin Smith
ID: 35128842
Depends if you did a BASIC install or not.

When you installed the R2 instance, did you specifically tell it to use the instance name SHAREPOINT.  Or did SharePoint do this on it's own when you installed?
0
 

Author Comment

by:PDSWSS
ID: 35128865
Not 100 % sure but most likely - the latter.  If Sharepoint did this on its own - would I not see maintenance plans? thanks
0
 
LVL 38

Expert Comment

by:Justin Smith
ID: 35128875
I'm thinking you can't manage Express instances using the full version of SQL Manager (not confirmed).  You might try to install the SQL Server Manger Express and see if you can use it to manager server\SHAREPOINT instance.

http://www.microsoft.com/downloads/en/details.aspx?FamilyID=08e52ac2-1d62-45f6-9a4a-4b76a8564a2b&displaylang=en
0
 
LVL 38

Expert Comment

by:Justin Smith
ID: 35128884
0
 

Author Comment

by:PDSWSS
ID: 35128946
Thanks for the info. Question - Why do you think Sharepoint is using SQL Express when I installed SQL 2008 R2 full version?

I need to be able to make scheduled backups of the Content DB. I thought that a Maintenance plan would be the simplest way to do this.
Could you suggest an alternative.  Do you have a script that could be coupled with Windows Scheduling?
0
 
LVL 38

Accepted Solution

by:
Justin Smith earned 2000 total points
ID: 35128965
I'm guessing you did a BASIC install, which installs SQL Express.

If it truley is an Express instance, you won't be able to create maintenance plans.

See this article for a work around:  http://social.msdn.microsoft.com/Forums/en-US/sqltools/thread/b65be001-eebe-40b9-b2fc-5f1970ee5cdd/
0
 

Author Comment

by:PDSWSS
ID: 35129180
Thanks for the work around.
Some more questions.

1)  So I have both SQL Express and SQL 2008 R2 on my Server?

2) Are there any known issues with having both  SQL  Studio Mgr and SQL Express Studio Mgr on the same server?  

3)  When I install Sharepoint Foundation 2010 on a server with SQL 2008 R2, how would you suggest installing so Sharepoint uses the full SQL version?
0
 
LVL 38

Expert Comment

by:Justin Smith
ID: 35129207
1. I'm guessing, yes.
2. Not that I know of, but if Express Manager doesn't give you anytihng that you can already see in the full version, I would uninstall and just go with the full version.
3. Always always always do a FARM install, complete.  This is a best practice, especially for a Prod environment.  It allows you to connect to yoru existing SQL install.
0
 

Author Comment

by:PDSWSS
ID: 35130065
I am following the instructions to do the suggested workaround - Please see attached -

They say to schedule a task and when asked to select a program browse to  C:\Program files\Microsoft SQL Server\90\Tools\...

There is no tools  folder in  "90." In 100 there is a tools folder>BINN>sqlcmd. Is this the same program?  Please advise. Thanks
Picture-3.png
0
 
LVL 38

Expert Comment

by:Justin Smith
ID: 35130068
Yes.  The instructions were specific to 2005.
0
 

Author Comment

by:PDSWSS
ID: 35130081
Thanks
0
 

Author Comment

by:PDSWSS
ID: 35133024
In the screen shot attached, Picture 3 above would   #2 "... Edit the run command as such" all be included in one Action in the Task scheduler?  Thanks
0
 
LVL 38

Expert Comment

by:Justin Smith
ID: 35133590
Yes, this is what the scheduler actually runs
0
 

Author Comment

by:PDSWSS
ID: 35138504

Achilles: Tested your solution from this link
 http://social.msdn.microsoft.com/Forums/en-US/sqltools/thread/b65be001-eebe-40b9-b2fc-5f1970ee5cdd/


My script from  SQLscriptContent.sql works

However, this script which I added to actions in the Task Scheduler does not work. Failed a couple of times and do not know why? Any ideas.
Wrote according to instructions. However, they indicated  folder 90 not 100. Perhaps does not work with SQL 2008 only 2005.

C:\Program Files\Microsoft SQL Server\100\Tools\Binn\SQLCMD.EXE" -S .\sharepoint -i "C:\SQLscriptContent.sql"
0
 

Author Comment

by:PDSWSS
ID: 35138516
Correction - left out quotes at beginning.

"C:\Program Files\Microsoft SQL Server\100\Tools\Binn\SQLCMD.EXE" -S .\sharepoint -i "C:\SQLscriptContent.sql"
0
 

Author Comment

by:PDSWSS
ID: 35138535
Should anything be changed in the attached config? Thanks
Picture-1.png
0
 

Author Comment

by:PDSWSS
ID: 35143191
Achilles:Any ideas in regards to my last posts. Thanks

1) The task scheduled to run this fails even though  SQLscriptContent.sql  works (backs up the SQL.bak to the external F: drive)
"C:\Program Files\Microsoft SQL Server\100\Tools\Binn\SQLCMD.EXE" -S .\sharepoint -i "C:\SQLscriptContent.sql"

2) Says that the task associated with this Action (Copy SQL.bak file from F drive to remote drive) = success although nothing is copied. Why would I get a success message
if nothing happened?  

C:\CopySPointbackupto226.cmd    
0
 
LVL 38

Expert Comment

by:Justin Smith
ID: 35143530
Not really, I've pointed you in the correct direction.  It seems that the issue is now with the scheduled task, and not in SQL.  

You may need to open a new question.

I will say, verify the account you are running the task as has proper rights on your SQL instance.
0
 
LVL 38

Expert Comment

by:Justin Smith
ID: 35143541
Your original question was where do I find the maintencne functionality on my SQL instance.  I've clearly answered that it's not available on SQL Express and pointed you to another option.
0
 

Author Comment

by:PDSWSS
ID: 35143592
I appreciate your pointing me to another option. You did give me what appeared to be an excellent solution. However, I followed the instructions exactly and it won't work for my situation. This is the reason why I asked for additional help.  But I do see your point as you provided a solution and you should not be penalized, if I need more information to make it work.

 It looks like it should work. So I will give you the points. Thanks again



0
 

Author Closing Comment

by:PDSWSS
ID: 35143594
Thanks again
0

Featured Post

Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article we will learn how to backup a VMware farm using Nakivo Backup & Replication. In this tutorial we will install the software on a Windows 2012 R2 Server.
Your data is at risk. Probably more today that at any other time in history. There are simply more people with more access to the Web with bad intentions.
This tutorial will walk an individual through setting the global and backup job media overwrite and protection periods in Backup Exec 2012. Log onto the Backup Exec Central Administration Server. Examine the services. If all or most of them are stop…
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

752 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question