Skip Ribbon Commands
Skip to main content

Quick Launch

Todd Klindt's home page > Todd Klindt's Office 365 Admin Blog > Posts > Scheduling SQL backups for SharePoint
January 16
Scheduling SQL backups for SharePoint

I do a lot of SharePoint installs and one question I always ask before the engagement is over is “How are you going to back this all up?” More often than I’m comfortable with, the answer is, “I don’t know.” In those cases I tell them the very, very least they can do is to do database level backups with SQL. Many times I hear silence on the other end of the phone. Seems many SharePoint folks just aren’t comfortable in SQL and aren’t sure how to do backups. I decided to write up this quick walk through to send to folks to get them started. I hate it when people lose SharePoint data.

To do this you’ll need to an account that is a serveradmin on the SQL server and you’ll need to log in as that account and start SQL Server Management Studio (SSMS). This walk through was done on SQL 2008 R2, but it’s very similar on SQL 2008 and SQL 2005, so if you’re using either of those you should be able to follow along. Under Management right click on “Maintenance Plans” and click “Maintenance Plan Wizard.”

SharePoint 2010 1-16-2011 11.28.16 AM

Give the plan a name, like “Backup Database.” You can also schedule the backups to run on this screen by clicking “Change…”

SharePoint 2010 1-16-2011 11.28.39 AM

Next we’ll pick the things our maintenance plan will do We’ll check “Back Up Database (Full)” as well as “Check Database Integrity.” Corrupt databases back up just as well as uncorrupted databases. It’s good to run an integrity check just to make sure you’re backing up something that will actually help you if you need to do a restore.

SharePoint 2010 1-16-2011 11.28.53 AM

Next choose the order the two jobs will run in. We’ll leave this at the defaults.

SharePoint 2010 1-16-2011 11.29.02 AM

Next we pick which databases the integrity check will run against. Choose “All databases.”

SharePoint 2010 1-16-2011 11.29.25 AM

Next we get to configure the backups. We are also going to back up “All databases” to make sure we get everything. I also leave the defaults that create one file per database but do not create a folder for each backup. If your SKU of SQL supports backup compression you can also enable it here. I highly recommend it if it’s available to you.

SharePoint 2010 1-16-2011 11.30.12 AM

Tada! The Maintenance Plan is created. Of course it needs to run before it does us any good. To run a Maintenance Plan right click on it and click Execute.

SharePoint 2010 1-16-2011 11.31.55 AM

If this is the first time you’ve tried to run a Maintenance Plan on your SQL instance you might get the following error:

SharePoint 2010 1-16-2011 11.32.19 AM

Just like the error says, this is because the SQL Server Agent is not started. To fix that, right click on the SQL Server Agent and click Start.

SharePoint 2010 1-16-2011 11.32.31 AM

Now try to execute your Maintenance Plan again. It should work. Your next question should be, “This is freakin’ cool! How do I schedule this?” I’m glad you asked. To add a schedule right click on the Maintenance Plan and click Modify. At the top of the modify screen click the calendar to the right of the line that says, “Not scheduled (On Demand)”.

SharePoint 2010 1-16-2011 7.28.22 PM

This will bring up a scheduling dialog that will let you schedule when your Maintenance Plan will run.

Thanks for reading this far, no go out there and back up some database.



Nice start ... but

A couple of points that I would add;
- include another job for backing up of the Transaction Log.  If no one is able to provide direction, a good general timeframe is Daily for Full Backup and Hourly for Transaction Log Backup
- add a cleanup job otherwise you're going to run out space ... especially with SharePoint.  Retain as long as you can, but allow for growth.
- I prefer to use the "Create a sub-dir for each database".  With all of the SharePoint DB's, Transaction Logs. etc, it can get pretty messy
- Change the path that the backups are made to different to where the databases are stored (if possible)
- Notification is also a good idea ... but beyond the scope of a comment!
 on 1/16/2011 8:35 PM

Need to backup Logs after full backup

you also need to backup logs as well if its SQL 2008/R2.
Other wise your log files will grow up like a hell.

 on 1/16/2011 8:36 PM

Re: Nice start ... but

Thanks for the comment.

- I purposely avoided the topic of Recovery Models.
- Probably not a bad idea
- Cool
- It is a good idea to save the backups to a different drive or machine. I should have mentioned that.
- I didn't want to cover operators either, so I ignored notifications.

Todd O. KlindtNo presence information on 1/16/2011 9:09 PM

Re: Need to backup Logs after full backup

I didn't want to cover transaction logs or recovery models in this blog post. Maybe I'll cover it in a later one.

Todd O. KlindtNo presence information on 1/16/2011 9:11 PM

Re: Scheduling SQL backups for SharePoint

have you ever been able to backup and restore configuration database as well? I read some articles contradicting each other..
 on 1/17/2011 10:50 PM

911 Class

I'd like to get more information after last nights webcast announcement.
Follow-up to yesterday's tweets. In moving from SQL 2000 to 2005 the idea of using instances in test was rejected, but in reality that only caused a 24 hour delay in my hitting the wall.
After some quality time, mine not theirs, with Sean & JD, plus some Q&A with you and Shane in Columbus I have pinned a lot of hope on getting a 70gb content DB split before migrating to 2010.  SP staff is me, and a manager who supports a bunch of other systems.  I do have DocAve for about 4 months now only because Idera wouldn't run on our 32 bit 4gb front end with a SQL instance on a virtual server running multiple other applications.  Mr McDonough has kept pointing me at you for months as the source of all practical knowledge.
 on 1/18/2011 7:10 AM

Re: Scheduling SQL backups for SharePoint

You would only ever restore the config database if you were restoring it back to the exact environment it came from. For instance, if your SQL server's drives all died and you had to reload your databases from backup, you could restore your Config DB. However, if you were building a test farm with different machine names and accounts, you would NOT restore your Config DB.

Todd O. KlindtNo presence information on 1/18/2011 9:10 AM

Creating a testing environment with a database backup?

Based on your comment about not restoring your config DB on a test farm with different machine names and accounts, I'm wondering how to solve a quandary.

I need to create a VM for testing/development. SP staff is me, myself, and I and a sys admin who is responsible for ALL servers.  We currently have a MOSS 2007 farm with a single Sharepoint WFE and a single SQL server. 

I realize we need a test environment, but we do not have the resources for a new server.  So I'm looking to recreate our current production environment in a VM setting for testing.  Any guidance on how to do that?

 on 1/24/2011 12:57 PM

Re: Creating a testing environment with a database backup?

When building a test farm for SharePoint 2007 really the best you can do is build the test environment by hand and then attach your production content dbs to your newly built farm. There's no good way to migrate the settings from one farm to another, unfortunately. :(

Todd O. KlindtNo presence information on 1/28/2011 9:13 PM


Thanks todd!!!!  

 on 2/14/2011 8:17 AM
1 - 10Next

Add Comment

Items on this list require content approval. Your submission will not appear in public views until approved by someone with proper rights. More information on content approval.


Body *

Today's date *

Select a date from the calendar.
Please enter today's date so I know you are a real person


Want a message when I reply to your comment? Put your Twitter handle here.