I am running Crystal Reports Server XI with a SQL Server 2000 database. These two components are running on two different servers, but it should not change the procedures below much.
Procedures for Scheduled Daily Backups
On the database server, I set up a batch file that utilizes SQL Server 2000’s osql command to perform backup of the Central Management Server (CMS) database; I scheduled it to run nightly. The content of the batch file is as follows. Note that you should substitute in your server name, user name, password, and file paths. Also, note the copy command makes a copy of the backup file to another server, which is highly recommended; how good is a backup if the system failure is caused by a hard disk burning out and the backup resides on the same hard disk, right?
osql -S [serverHostName] -U [databaseUsername] -P [myPassword] -Q "begin declare @CMSBackupFile varchar(255) set @CMSBackupFile='C:cms.bak' backup database [cmsDatabaseName] to disk = @CMSBackupFile end" xcopy.exe /c /d /e /h /r /y C:cms.bak \anotherServerbackupscms
On the Crystal Reports Server XI server, there is a folder that stores the actual reports; Crystal Reports Server XI refers to this folder as the Input File Repository. By default, it is located at C:Program FilesBusiness ObjectsBusinessObjects Enterprise 11FileStoreInput, which is what the example below will make use. The example is another batch file, scheduled to run nightly, that makes a backup copy of the reports to another server.
xcopy.exe /c /d /e /h /r /y "C:Program FilesBusiness ObjectsBusinessObjects Enterprise 11FileStoreInput*.*" \anotherServerbackupsfileStore
Restore Procedures
My plan to deal with the unthinkable is listed below.
For the SQL Server, if needed, install SQL Server and then copy the backup file (“cms.bak” in this example) to the new database server. Next, issue the following two osql commands in the command prompt to create/recreate the CMS database.
osql -S [newServerHostName] -U [databaseUsername] -P [myPassword] -Q "drop database [cmsDatabaseName]" osql -S [newServerHostName] -U [databaseUsername] -P [myPassword] -Q "restore database [cmsDatabaseName] from disk='c:cms.bak'"
Next, we will likely find an orphaned user in the restored database; this can be seen in SQL Server Enterprise Manager’s “Users” section for the CMS database; you will see an user without “Login Name”. To fix this, run the following osql command in the command prompt.
osql -S [newServerHostName] -U [databaseUsername] -P [myPassword] -d [cmsDatabaseName] -Q "exec sp_change_users_login 'Auto_Fix', '[crystalReportsDatabaseUser]', NULL, '[passwordHere]'"
For the Crystal Reports Server XI reports, if needed, they can be copied directly from your backup to the new Input File Repository location.
When you have completed the restore, you may wish to restart the CMS and Input File Repository services in the Central Configuration Manager.