By default the Sophos database will restore to the C drive on the server, this cannot be changed during installation. In order to change the drive that the database files are located it is necessary to backup the database, drop it, then restore to the new location. This article will guide you through each of these required steps and should be used when a specific storage location is needed for the database other than the default set by Microsoft SQL Server.
This article does not apply if you want to move the database to another computer, this is covered in the following articles:
- Default installation: 28276
- Distributed installation: 22468
Known to apply to the following Sophos product(s) and version(s)
Sophos Control Center
What To Do
Before proceeding you will need to have installed SQL Server Management Studio Express and have Sophos Enterprise Console closed to avoid database connection errors during the steps.
1. Backup the database
This will create a backup (.bak file) which we will restore in the desired location in step 3. Refer to Sophos article 17323 to obtain a list of database names based on the installed version, make a note of these as they will be required at various stages in the article.
- On the Sophos Management server open a command prompt (Start | Run | cmd.exe)
- Browse to the following location:
- Sophos Enterprise Console:
- Sophos Enterprise Manager:
- Run: '
BackupDB.bat /?'to show the usage options, for further information see Sophos article 110380
- Run BackupDB.bat with the required parameters to create your database backup. If performing these steps for Enterprise Console 5 and above you will need to repeat the steps for the additional databases.
2. Drop the database
This will delete the database and any connections to the database, it would be advisable to do this out of hours to avoid any possible disruption. The below assumes you are using the default SQL instance named Sophos, please amend .\SOPHOS if you are using a different named instance
- Refer to Sophos article 17323 to get the database name for your install.
- On the Sophos Management server open a command prompt (Start | run |
- Run the below command replacing
SOPHOSXXwith the name of the database to drop obtained from step 1:
sqlcmd -E -S .\SOPHOS -Q "DROP DATABASE SOPHOSXX"
If performing these steps for Enterprise Console 5 and above you will need to repeat the above steps for the additional databases.
3. Restoring the database
- Create the folder where you want the database files to be stored. You will need to ensure that the 'SYSTEM' account has full control of this folder and may also require 'NETWORK SERVICE' to be included with full control.
- Open SQL Management Studio (Start | All Programs | Microsoft SQL Server 2005/2008 | SQL Server Management Studio)
- On the left hand view, right click Databases and select 'Restore Database'.
- Under the 'Destination for Restore' section, enter the database name as obtained in Section 2 step 1 above (e.g. SOPHOS52)
- The restore is being performed using a backup so select 'From device'.
- Click the '…' button.
- Navigate to the .bak file created in Section 1 and double click.
- Check the 'Restore' box to select this backup set to restore.
- To change the physical path of the database files click the 'Options' button at the top left.
- In the column named 'Restore As', modify each file to reflect the new folder path for the files (as specified in step 3.1), by default these will be:
- Rows Data:
C:\Program Files\Microsoft SQL Server\MSSQL10.SOPHOS\MSSQL\DATA\databasename.mdf
C:\Program Files\Microsoft SQL Server\MSSQL10.SOPHOS\MSSQL\DATA\databasename_1.ldf
- Check overwrite existing database to ensure that any legacy files are overwritten.
- Click OK to perform the restore.
- Once complete, the database has now been moved to the designated drive/folder.
- If performing these steps for Enterprise Console 5 and above you will need to repeat the above steps for the additional databases.
- Restart the Sophos Management Service on the management server before attempting to open the Sophos Enterprise Console.