Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1163
  • Last Modified:

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

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
PDSWSS
Asked:
PDSWSS
  • 17
  • 13
1 Solution
 
tearmanCommented:
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
 
Justin SmithSr. System EngineerCommented:
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
 
PDSWSSAuthor Commented:
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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
Justin SmithSr. System EngineerCommented:
It's something SQL related, not SharePoint related.

What is your process?  Are you using a remote SQL Manager?
0
 
PDSWSSAuthor Commented:
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
 
PDSWSSAuthor Commented:
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
 
Justin SmithSr. System EngineerCommented:
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
 
PDSWSSAuthor Commented:
Not completely following you.  Are you remote desktop into the SQL Box, then launching SQL Manager?    YES
0
 
Justin SmithSr. System EngineerCommented:
Are you sure your SharePoint instance (server\SHAREPOINT) is an R2 instance and not a SQL Express instance?
0
 
PDSWSSAuthor Commented:
Installed MS SQL 2008 R2. How could it be an SQL Express instance?
0
 
Justin SmithSr. System EngineerCommented:
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
 
PDSWSSAuthor Commented:
Not 100 % sure but most likely - the latter.  If Sharepoint did this on its own - would I not see maintenance plans? thanks
0
 
Justin SmithSr. System EngineerCommented:
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
 
Justin SmithSr. System EngineerCommented:
0
 
PDSWSSAuthor Commented:
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
 
Justin SmithSr. System EngineerCommented:
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
 
PDSWSSAuthor Commented:
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
 
Justin SmithSr. System EngineerCommented:
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
 
PDSWSSAuthor Commented:
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
 
Justin SmithSr. System EngineerCommented:
Yes.  The instructions were specific to 2005.
0
 
PDSWSSAuthor Commented:
Thanks
0
 
PDSWSSAuthor Commented:
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
 
Justin SmithSr. System EngineerCommented:
Yes, this is what the scheduler actually runs
0
 
PDSWSSAuthor Commented:

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
 
PDSWSSAuthor Commented:
Correction - left out quotes at beginning.

"C:\Program Files\Microsoft SQL Server\100\Tools\Binn\SQLCMD.EXE" -S .\sharepoint -i "C:\SQLscriptContent.sql"
0
 
PDSWSSAuthor Commented:
Should anything be changed in the attached config? Thanks
Picture-1.png
0
 
PDSWSSAuthor Commented:
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
 
Justin SmithSr. System EngineerCommented:
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
 
Justin SmithSr. System EngineerCommented:
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
 
PDSWSSAuthor Commented:
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
 
PDSWSSAuthor Commented:
Thanks again
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

  • 17
  • 13
Tackle projects and never again get stuck behind a technical roadblock.
Join Now