How to Back Up a Multi-User Project (Microsoft SQL Server)

The project files—including the database file—can be backed up by your regular tape back-up procedures as long as the project database file is not open and the server is not running.

However, you may need a different procedure for working with database files that are used by many people. Because the database server program is continually running, it always has the database file locked so that other processes, such as the tape back-up program, cannot access it. As such, you cannot copy it directly unless you stop the database server program. This may not be practical, especially for production databases that are constantly in use and backed up several times each day.

In such cases, you will want to use the back-up utility provided by Microsoft SQL Server that allows you to back up your database while SQL Server is running.

To use this utility:

  1. Start Microsoft SQL Server Enterprise Manager by selecting Start / Programs / Microsoft SQL Server / Enterprise Manager .
  2. Locate the database you wish to back up. Expand a server group and then expand a server.
  3. Expand the databases and select the database you wish to back up. Right-click, select "All Tasks", and click "Backup Database."
  4. In the SQL Server Backup dialog box specify the following:
  • Database - Select the name of the database you wish to back up.
  • Name - Enter a name for the back-up file.
  • Description - Describe the backup.
  • Backup - Select "Database - complete."
  • Destination - Click "Add" to create a new destination, or select an existing destination from the list box.
  • Overwrite - To add the back-up files to the destination without overwriting any existing files, select "Append to media." To overwrite existing files with the new back-up files, select "Overwrite existing media."

One strategy for setting the destinations is to select a file as your destination (e.g. \archibus\projects\hq\data\HqBackup.Mdf ) and make certain that this file is in your regular tape back-up schedule, along with your other Archibus project files. Since this file is a copy of the active database, it has all of the same data; however, since this file is not in active use, you can back it up with your regular tape back-up software.

  • Schedule - Leave this box unchecked to back up the database files immediately.

Optionally, schedule the backup for later or periodic execution by selecting the Schedule option, which presents the Edit Schedule dialog box. Setting this option is important for scheduling backups that you want to occur weekly, nightly, or even more often.

  1. Select OK and the program writes the back-up files to the selected destination.

Note --If you plan to use this type of back-up strategy, you should have the "Truncate log on checkpoint" option on your database set to the default, which is "Yes." To check this value, start the SQL Server Enterprise Manager, select your database, and select Properties from the right-click context menu to access the Options dialog. For more information, see the "Truncating the Transaction Log" topic in the SQL Server Books Online documentation.