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.



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


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


SharePoint 2010 Backup and Restore – What’s New

Kurz vor dem Wochenende ist Backup & Recovery mein heutiges Thema. Unsere alte SharePoint 2007 Umgebung (Company Web / Knowledgebase / Blogs / MySite) haben wir mit DocAve for SharePoint gebackupt und ggf. Sites, oder Files wiederhergestellt. Jetzt suche ich neben den SharePoint eigenen Bordmitteln nach neuen Alternativen und teste gleichzeitig auch die Bordmittel im Detail.

Meine 2010er Umgebungen werden zurzeit via Content Database SQL Server Backup gesichert und, zum Glück musste ich das nur testen, wiederhergestellt. Zurzeit ist diese Methode noch ausreichend, für einen Restore auf File bzw. Elementebene in Kundenumgebungen später jedoch nicht vorteilhaft. Zur Auswahl stehen die bekannten Tools DocAve for SharePoint 2010 von AvePoint, Recovery Manager for SharePoint von Quest und Backup Exec 2010 & System Recovery von Symantec. Einen ausführlichen Testbericht gibt’s dann etwas später – jetzt richte ich erst einmal alle Tools ein, dann heißt es: testen bis der Arzt kommt 😉

Aber was ist jetzt eigentlich neu bzw. anders bei den SharePoint 2010 eigenen Bordmitteln als in SharePoint 2007? Dazu habe ich einen kurzen und doch recht informativen Artikel auf gefunden:


Author: Admin

SharePoint 2010 Backups are a major advance over the backup facilities in SharePoint 2007. SharePoint can now easily maintain full backups and even granular backups, where only selected data can be restored. For this article I will demonstrate how to use PowerShell to backup and restore SharePoint applications as well as the standard the SharePoint web interface.

Content Database Recovery

In SharePoint 2007, my preference for SharePoint backup  was to perform a database backup. It was easy to maintain and backup/restore over many SharePoint farms, and it needed little administrative work in case when I had to move the sites to different farms, or restore broken farms to new hardware. To the experienced SharePoint administrator, with the content databases only, it didn’t take long to restore the content. But the real disadvantage of SharePoint backup via database backup was not in the hard recovery, where you had to deal with the entire server failures – the pain was recovering a single file from a document library that was accidentally deleted. The only way to recover that file using the database backups, was to fully restore it on different farm, attach it to any temporary SharePoint site and browse to the file to restore it from the library. Now this is no longer an issue, since we have Unattached Content Database Recovery.
Unattached Content Database Recovery allows you to browse through the content database using SharePoint, navigate to a list or document library and save the list or library into .cmp file which can easily be moved between sites.

Unattached Content Database Recovery

To be able to use this feature, you simply need to attach the content database backup with a different name to a SQL server instance and then use the screen (shown above) to retrieve any content from the attached database backup. You do not need to attach that database to any site inside a farm anymore. This feature is a part of the Granular Backup functionality in SharePoint. You can store that way entire content databases using MS SQL management tools or decide to backup only the site collection.
Backup and Restore feature
The stsadm powered backup and restore features from SharePoint 2007 are still here, but they have changed and now they can be helpful rather than painful for the administrator.

Backup in Central Administration

Central Administration now checks if the required services are running and gives the choice to backup the entire farm, or only selected applications or services. Backup and restore from Central Administration performs the same operations as the command line and stsadm tool. When you choose the content to backup, you may decide to perform full or differential backup and provide the path to the storage location for the backup.

Backup Using StsAdm

To perform the backup using StsAdm (which may be helpful when configuring automatic backups) you should first type in the backup command itself, to see what it is capable of.
To be able to use StsAdm, open the CMD window and browse to C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\14\bin directory and type in the stsadm command for backup as shown below:

The backup command for SharePoint 2010 is very similar to 2007 but now it is now much more efficient. For example, you can now easily backup search indexes and restore which was impossible in SharePoint 2007 and was a big issue for companies that had millions of documents and needed instant search access in their production environments.

Sample command to perform a farm backup with stsadm:
Stsadm –o backup –directory \\myServer\backup -backupmethod full

Using Powershell to Perform SharePoint Backups

To be able to use SharePoint commands with PowerShell, we first need to load the SharePoint snap-ins into the PowerShell console.

Now in a similar way to using StsAdm , we can test what PowerShell is capable of by typing the get-help Backup-SPFarm command. The PowerShell cmdlet allows for creating a backup of an individual database, web application or the entire farm. It is able to backup individual components or entire farm settings at once, but if we want to backup the site contents, we should check the Backup-SPSite cmdlet instead.

Examples of Backup-SPFarm:

Make full backup of configuration settings of the farm to the Backup directory:
Backup-SPFarm -Directory \\file_server\share\Backup -BackupMethod full -ConfigurationOnly

Backup the farm using 10 Backup threads and place the farm backup into C:\Backup folder. (the force parameter will force the backup even if there’s is insufficient space for the backup files:
Backup-SPFarm -Directory C:\Backup -BackupMethod full -BackupThreads 10 -Force

Backup a specific site collection to the C:\Backup folder:
Backup-SPSite http://server_name/sites/site_name -Path C:\Backup\site_name.bak


When I was using SharePoint Server 2007 the out-of-the-box backup and restore mechanisms were inadequate for backing up a large corporate SharePoint installation. Instead I stayed with the trusted the database backup, however there is always a need minimize the effort to accomplish a task, and and SharePoint 2010’s backup and restore are now enterprise ready.