Solved

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

Posted on 2011-03-11
31
1,089 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
  • 17
  • 13
31 Comments
 
LVL 3

Expert Comment

by:tearman
Comment Utility
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
Comment Utility
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
Comment Utility
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
 
LVL 38

Expert Comment

by:Justin Smith
Comment Utility
It's something SQL related, not SharePoint related.

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

Author Comment

by:PDSWSS
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
Are you sure your SharePoint instance (server\SHAREPOINT) is an R2 instance and not a SQL Express instance?
0
 

Author Comment

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

Expert Comment

by:Justin Smith
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
0
 

Author Comment

by:PDSWSS
Comment Utility
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
Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

 
LVL 38

Accepted Solution

by:
Justin Smith earned 500 total points
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
Yes.  The instructions were specific to 2005.
0
 

Author Comment

by:PDSWSS
Comment Utility
Thanks
0
 

Author Comment

by:PDSWSS
Comment Utility
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
Comment Utility
Yes, this is what the scheduler actually runs
0
 

Author Comment

by:PDSWSS
Comment Utility

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
Comment Utility
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
Comment Utility
Should anything be changed in the attached config? Thanks
Picture-1.png
0
 

Author Comment

by:PDSWSS
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
Thanks again
0

Featured Post

Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

Join & Write a Comment

Workplace bullying has increased with the use of email and social media. Retain evidence of this with email archiving to protect your employees.
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
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.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

763 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

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now