SharePoint 2010 Datenbanken – SQL Server Backup und Recovery mit Quest Recovery Manager

Hach, ich liebe das Internet! .. Da ich heute auf der Agenda das Thema SQL Server Backup für SharePoint zu stehen habe (als Einweisung der neuen Kollegen in die Betriebsthematik) freue ich mich umso mehr über den Artikel von Todd Klindt in seinem SharePoint Admin Blog!

Der Artikel beschreibt kurz und knapp die Einrichtung eines Wartungsplans im SQL Server 2008 über den Wizard bei gleichzeitiger Integritätsprüfung. Der Kommentar „Nice start .. but .. “ ist darüber hinaus hilfreich da ich bei allen Kunden und auch intern die Transaktionsprotokolle mit sichere. Auf Basis dieser Backups kann ein effektives Backup & Recovery Management im Rahmen eines Desaster Recovery Plans mit Quest Recovery Manager for SharePoint realisiert werden. Mit der Möglichkeit die SQL Server eigenen Backups zu nutzen und trotzdem bei einer Wiederherstellung alle Informationen zurückholen zu können ist das Tool im Preis/Leistungsverhältnis die erste Wahl für mich!. So, aber jetzt zum Artikel:

Source: Todd Klindt´s Admin Blog / Author: Todd Klindt

by  Todd O. Klindt on 1/16/2011 7:29 PM

Category: SharePoint 2007SharePoint 2010Sharepoint

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.

tk

Comments

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

 

Hi,
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.

tk

Todd O. Klindt 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.

tk

Todd O. Klindt on 1/16/2011 9:11 PM

 

Über Erik Neumann
IT Consultant, MCP, MCTS, MCITP, ecspand your SharePoint! http://www.ecspand.de

One Response to SharePoint 2010 Datenbanken – SQL Server Backup und Recovery mit Quest Recovery Manager

  1. Eric – dank der Quest Recovery Manager for SharePoint Team!🙂

Schreibe einen Kommentar

Trage deine Daten unten ein oder klicke ein Icon um dich einzuloggen:

WordPress.com-Logo

Du kommentierst mit Deinem WordPress.com-Konto. Abmelden / Ändern )

Twitter-Bild

Du kommentierst mit Deinem Twitter-Konto. Abmelden / Ändern )

Facebook-Foto

Du kommentierst mit Deinem Facebook-Konto. Abmelden / Ändern )

Google+ Foto

Du kommentierst mit Deinem Google+-Konto. Abmelden / Ändern )

Verbinde mit %s

%d Bloggern gefällt das: