Backup your system both full and partial. Use the full backup for corrupted and not working BI systems. Use the biar backup for working proper systems.
If you confused about full and biar backup please read this article:
http://bobj.sapbiblog.com/2012/11/29/backing-up-and-restoring-sap-businessobjects/
1.- Full Backup
Step1: Backup your CMS database.
In default the BI system comes with the default SQL 2008 Express Edition. Express edition does not support SQL Jobs. So you have to write your own backup procedure and then schedule it via windows task scheduler. Here is the SQL backup code:
USE [BOE140]
GO
/****** Object: StoredProcedure [dbo].[sp_BackupDatabases] ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: Microsoft -- Create date: 2010-02-06
-- Description: Backup Databases for SQLExpress
-- Parameter1: databaseName
-- Parameter2: backupType F=full, D=differential, L=log
-- Parameter3: backup file location
-- =============================================
CREATE PROCEDURE [dbo].[sp_BackupDatabases]
@databaseName sysname = null,
@backupType CHAR(1),
@backupLocation nvarchar(200)
AS
SET NOCOUNT ON;
DECLARE @DBs TABLE
(
ID int IDENTITY PRIMARY KEY,
DBNAME nvarchar(500)
)
-- Pick out only databases which are online in case ALL databases are chosen to be backed up
-- If specific database is chosen to be backed up only pick that out from @DBs
INSERT INTO @DBs (DBNAME)
SELECT Name FROM master.sys.databases
where state=0
-- CMS AND AUDIT DATABASE NAMES
AND name in ('BOE140','BOE140_Audit')
-- Declare variables
DECLARE @BackupName varchar(100)
DECLARE @BackupFile varchar(100)
DECLARE @DBNAME varchar(300)
DECLARE @sqlCommand NVARCHAR(1000)
DECLARE @dateTime NVARCHAR(20)
DECLARE @Loop int
-- Loop through the databases one by one
SELECT @Loop = min(ID) FROM @DBs
WHILE @Loop IS NOT NULL
BEGIN
-- Database Names have to be in [dbname] format since some have - or _ in their name
SET @DBNAME = '['+(SELECT DBNAME FROM @DBs WHERE ID = @Loop)+']'
-- Set the current date and time n yyyyhhmmss format
SET @dateTime = REPLACE(CONVERT(VARCHAR, GETDATE(),101),'/','') + '_' + REPLACE(CONVERT(VARCHAR, GETDATE(),108),':','')
-- Create backup filename in path\filename.extension format for full,diff and log backups
IF @backupType = 'F'
SET @BackupFile = @backupLocation+REPLACE(REPLACE(@DBNAME, '[',''),']','')+ '_FULL_'+ @dateTime+ '.BAK'
ELSE IF @backupType = 'D'
SET @BackupFile = @backupLocation+REPLACE(REPLACE(@DBNAME, '[',''),']','')+ '_DIFF_'+ @dateTime+ '.BAK'
ELSE IF @backupType = 'L'
SET @BackupFile = @backupLocation+REPLACE(REPLACE(@DBNAME, '[',''),']','')+ '_LOG_'+ @dateTime+ '.TRN'
-- Provide the backup a name for storing in the media
IF @backupType = 'F'
SET @BackupName = REPLACE(REPLACE(@DBNAME,'[',''),']','') +' full backup for '+ @dateTime
IF @backupType = 'D'
SET @BackupName = REPLACE(REPLACE(@DBNAME,'[',''),']','') +' differential backup for '+ @dateTime
IF @backupType = 'L'
SET @BackupName = REPLACE(REPLACE(@DBNAME,'[',''),']','') +' log backup for '+ @dateTime
-- Generate the dynamic SQL command to be executed
IF @backupType = 'F'
BEGIN
SET @sqlCommand = 'BACKUP DATABASE ' +@DBNAME+ ' TO DISK = '''+@BackupFile+ ''' WITH INIT, NAME= ''' +@BackupName+''', NOSKIP, NOFORMAT'
END
IF @backupType = 'D'
BEGIN
SET @sqlCommand = 'BACKUP DATABASE ' +@DBNAME+ ' TO DISK = '''+@BackupFile+ ''' WITH DIFFERENTIAL, INIT, NAME= '''+@BackupName+''', NOSKIP, NOFORMAT'
END
IF @backupType = 'L'
BEGIN
SET @sqlCommand = 'BACKUP LOG ' +@DBNAME+ ' TO DISK = '''+@BackupFile+ ''' WITH INIT, NAME= ''' +@BackupName+''', NOSKIP, NOFORMAT'
END
-- Execute the generated SQL command
EXEC(@sqlCommand)
-- Goto the next database
SELECT @Loop = min(ID) FROM @DBs where ID>@Loop
END
Just change the CMS AND AUDIT DATABASE NAMES and then run it under master database. This will create the [dbo].[sp_BackupDatabases] procedure.
After you create your procedure, now you have to schedule it. To do this first create a batch file and put this code:
sqlcmd -U SQLUSERXXX -P PASSWORDXXX -S .\BOE140 -Q "EXEC BOE140.dbo.sp_BackupDatabases @backupLocation ='D:\', @BackupType='F'"
You have to change SQLUSERXXX and PASSWORDXXX which are your SQL Server account credentials. Change the db name and instance name (.\BOE140 in this example) and the destination folder too if needed. This procedure creates backup files with datetime stamp for each database you have defined.
Last step, schedule this batch file with windows task scheduler.
Step2: Backup your Filestore.
Create a batch file with this code:
set year=%date:~-4%
echo year=%year%
set month=%date:~3,2%
if "%month:~0,1%" == " " set month=0%month:~1,1%
echo month=%month%
set day=%date:~0,2%
if "%day:~0,1%" == " " set day=0%day:~1,1%
echo day=%day%
set datetimef=%year%%month%%day%
cd\
cd "C:\BI40\SAP BusinessObjects Enterprise XI 4.0\FileStore"
"C:\Program Files (x86)\WinRAR\rar.exe" a -r "d:\"%datetimef%"_filestore.rar" *.*
Change the source of your Filestore path and the destination. Change the rar.exe path if needed. This batch file will create a rar file with the datetime stamp of your filestore folder.
Step3: Backup your server configuration.
To do this go to the CCM and click the "Backup Server Configuration" button.
Thats all. You have finished your full backup.
2.- Biar Backup
Biar backup is more for partial backup. I mean if you lost a folder or a report it's the easy way to restore it from biar backup. Will explain it shortly.
No comments:
Post a Comment