STG Forums

[Mike Barskey]

...when your server isn't automatically backed up.

If you are hosting CP in Microsoft SQLServer on a computer that does not get automatically backed up every night, you can manually back up your server using the steps below. You might also be able to paritally automate the steps below using a batch file.

1. Backup the database by entering the following command. You can enter the command in the Interactive SQL wizard, if you have access, or on the server computer itself in the command prompt (see below for instructions on this).

   Code:

   backup database [cp] to disk='c:\cp_backup_06-07-04.bak'

   (Replace "cp" with the name of your database on the SQL Server; replace "06-07-04" with the date you are making the backup)
   
2. Copy the newly-created backup file to a hard drive that gets automatically backed up, or to a foreign disk (like a zip disk, or burn it to a CD).

To enter the backup command on the MS SQLServer server computer in the command prompt (using this method, the process might be automated in a batch file):

1. In the command prompt, enter "osql -E" (without the quotes), then hit Enter. This loads the command line interface to MS SQLServer.
   
2. Type "backup database [cp] to disk='c:\cp_backup_06-07-04.bak'" (without the double-quotes - but do include the single quotes!), then press Enter, then type "go" and press Enter again.
   
3. To exit the MS SQLServer command line interface, type "exit" (without the quotes) and press Enter.

[Mike Barskey]

Thanks to Ron Angert at Virginia Polytechnic Institute for creating this working automated backup.

While this script works for him and will probably work for you (although you will likely need to modify the scripts first), Seattle Technology Group, Inc. does not support or endorse this backup method. We provide it for your convenience.

This process is designed for CP running in MSSQL (or MSDE) under Windows, and it assumes that the c:\backup directory (created in step #1) gets automatically backed up by your university's backup system, and that your MSSQL Server (or MSDE) does not get automatically backed up).

1. Create a directory called "backup" on the C: drive of your MSSQL Server (or MSDE) computer
   
2. Create a file in this directory called "backupCP.bat" whose contents should be:

   Code:

   c: cd \backup xcopy /y today.bak yesterday.bak xcopy /y outputfile.txt yesterdayoutputfile.txt del today.bak del outputfile.txt osql -E -i "c:\backup\backupCP.sql" -o outputfile.txt -n

3. Create another file in this directory called "backupCP.sql" whose contents should be:

   Code:

   backup database [cp] to disk='c:\backup\today.bak'

4. Configure Windows on this computer to automatically run "backupCP.bat" once per day (probably in the middle of the night, like 3:00a, preferably before your automated backup runs)

Description of what happens:
Automatically, once per day at 3:00a, Windows runs "backupCP.bat". This file renames yesterday's backup and tells MSSQL Server (or MSDE) to run the SQL command in the "backupCP.sql" file. This SQL command tells MSSQL Server (or MSDE) to create a new backup of your CP database in the "c:\backup" directory. Windows automatically backs this directory up each night. The process repeats each day, so each night's backup will contain the previous day's CP backup.

- Mike