How do I back up the Apex database?
When a file based backup is done while the SQL Server (or MSDE) is running, you may see messages that the Apex.mdf and Apex_Log.ldf files are skipped. These are the SQL Server database files for Apex. Since the SQL Server is always running and holding these files open, the database files can not be accessed properly from a backup utility.
Windows 2003 and later
Windows 2003 offers a new capability which can back up files while they are opened. This is called Volume Shadow Copy (VSS). SQL Server has a volume shadow copy provider that will allow the Apex database to be successfully backed up even if the SQL Server is running.
A good backup plan is vital to protect your Apex data. Vulcan Software suggests four ways to backup your Apex database data:
- You can use Apex 8 or above to create, restore, or schedule regular backups.
- A database administrator can use the SQL Server Enterprise Manager to administer backups and maintenance plans.
- You can make a file-based backup using a normal backup utility. Note that the SQL Server service may need to be stopped for this to work.*
- You can use a third-party backup tool to create or schedule your backups.
* As noted above if the database is on a Windows Server 2003 or above you don't have to stop the SQL Server. Volume Shadow Copy services will be able to safely back up the database while it is running.
Apex 8 and newer Backup
Beginning with Apex you can create, restore, and schedule database backups through the File/Backup menu selection. Anyone can make a backup, but only an administrator (that knows the "sa" password on the database server) can restore or schedule a backup.
A database administrator can use the SQL Server Enterprise Manager to create backups, or schedule backups as part of a database maintenance plan.
Stopping and restarting the SQL Server
You can shut down the SQL Server when no one is using Apex, make a file backup while the Apex database files are accessible, and then restart the SQL Server after the backup completes.
Stopping the SQL Server service can be done by using the SQL Server Service Control Manager, which is normally found on the task tray. To automatically stop and restart it, you could put the instructions into batch files which are scheduled to run before and after the backup. Here is an example:
1. Create two text files using a text editor such as Notepad. For this example we will name the files c:\admin\Stop.bat and c:\admin\Start.bat.
Add the following lines to the Stop.bat file:
net stop sqlserveragent
net stop mssqlserver
Add the following lines to the Start.bat file:
net start mssqlserver
net start sqlserveragent
2. Schedule your Stop.bat file to run. (NOTE: If you are just using NTBackup, it may be easier if you can combine the Stop.bat, tape backup, and Start.bat all in one batch file.)
For example, if you want to stop the SQL Server services at 8:00 P.M. every night, type the following:
AT 20:00 /every:M,T,W,TH,F,S,SU "c:\admin\stop.bat"
3. Once SQL Server is stopped, you can use whatever backup utility you want to make backups of the SQL Server database files. By default the Apex database file is named Apex.mdf, and the Apex transaction log is named Apex_Log.ldf.
4. Once the database files have been backed up you should restart SQL Server. For example, if you want to start the SQL Server services at 11:00 P.M. every night, type the following:
AT 23:00 /every:M,T,W,TH,F,S,SU "c:\admin\start.bat"
Third-party Backup Tools
There are very good third-party backup tools such as Backup Exec that will back up the SQL Server as part of your company backup plan.
After you have implemented your backup plan be sure to monitor that it is working correctly.