Unable to backup SQL server with BE2012
Hi
As title is stating, i am strugling big time backing up SQL server. Here is some information first:
Server in Question:
Running OS Microsoft Windows 2003 SP2
Running SQL 2005 with multiple databases.
Backup Environment:
Backup Exec 2012 with SP3 installed. This is again running on a Windows 2003 R2 SP2 server.
As already mention, i have a SQL environment with multiple databases. Some of them are running on a Simple Recovery mode and some on Full.
My backup setup is as follows:
Full backup on Friday, Log backup at 17:00 from Monday through Thursday and then a Differential backup at 18:00 from Monday through Thursdag.
Now when running the backups, it is creating multiple errors and exceptions. Below are a list of errors\exceptions it is creating:
For Full Backup:
- It is completing but with exceptions. It is saying that the Sharepoint agent does not support the transaction log backups, hence change the recovery model to Simple. This goes to 5 sharepoint databases. But problem is that i can not change the recovery model to Simple, as they are critical databases which needs to have in time recovery setup.
For Log
- For 5 of the Sharepoint database it is giving me the same error: The SharePoint database DATABASENAME is configured to maintain transaction logs. The SharePoint Agent does not support transaction log backups. Configure the database to use the simple recovery model using SQL Server Management Studio. Once you change the recovery model, you must run a full backup of the database before you can successfully run an incremental or a differential backup.
- And then it is giving me the following error on SQL databases: A log backup was attempted on database DATABASENAME that is not configured to support log backups. To change the configuration, use the SQL administration tools to set the recovery mode to Full. A new full backup should be performed if this setting is changed before a log backup is run.
Problem here is that i do understand that i need to deselect all the databases which have Simple recovery model, but some of them are greyed out so i am unable to deselect them.For Differential
- Giving me the same error for 5 sharepoint databases that it does not support the transaction log backups etc.
Apart from the issues above, i need to know the difference between the following 2 things which can be selected when setting up a SQL backup Job:
- Microsoft Sql Server Instances
- Microsoft Sharepoint Resources
I understand that Microsoft Sharepoint Resources lists the Sharepoint databases, but i also see them under Microsoft Sql Server Instances, does it mean that i am selecting them twice?
You cannot backup a Sharepoing farm by backing up the component SQL databases and restoring them. You would probably end up with an inconsistent farm. This is why you backup your Sharepoint farm using the Sharepoint agent. When you do a restore using the Sharepoint agent, you will end up with a usable farm. See my blog below in the details of why this is so.
https://www-secure.symantec.com/connect/blogs/can-i-backup-sap-database-sql-or-oracle-agent
By the same reasoning, you cannot do PIT restores of the component databases. As such, the document is correct in recommending that you switch all the Sharepoint component databases to simple recovery mode because there is no point in maintaining logs if you are not going to do PIT restores
With BE 2012, it is not possible to do log backups by themselves. You need to have two sets of jobs, with log backup jobs for the databases with full recovery mode and another job, without log backups, for the databases with simple recovery mode.
For your Sharepoint farm, you should select the resources from under Sharepoint and not under SQL databases.
