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.”
Give the plan a name, like “Backup Database.” You can also schedule the backups to run on this screen by clicking “Change…”
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.
Next choose the order the two jobs will run in. We’ll leave this at the defaults.
Next we pick which databases the integrity check will run against. Choose “All databases.”
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.
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.
If this is the first time you’ve tried to run a Maintenance Plan on your SQL instance you might get the following error:
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.
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)”.
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.