Automating Prolog Manager Database Backups

Setting up a backup process is often an after thought which most of us probably say "I'll get to that later", but never actually do. The fact is a computer's hard drive is the most likely device to fail on your computer, yet it houses all your data.

While RAID configurations provide disk failure protection, they are not a substitute for proper backups. Database corruption, while unlikely, can happen at any time and are not protected by a RAID setup. A virus, flakey network connection or even something as simple as a sudden power outage can be enough to cause damage to and corrupt your database. Because of this, properly backing up your Prolog Database is a must.

A common misconception of backing up your Prolog Manager SQL Databases is using "live copy" backup, utilized by popular backup solutions, to copy the MDF and LDF data files is enough. According to this Microsoft KB article (the same information also applies to SQL 2000/2005), this practice only works if either the database or SQL Server is gracefully shut down prior to copying the files. Of course, this defeats the entire "live copy" idea as your database would have to be shut down in order to properly copy the files. On top of this, failure to shut down the database or services could result in a corrupt backup file.

The recommended method for backing up your Prolog SQL Databases is by performing a full SQL backup (the extension for these files is usually .BAK). This offers many advantages over a simple data file copy, including:

• Backups can safely be performed while the database is still live, so there is no down time.
• Fast.
• Reliable.
• Native function of SQL Server and recommended by Microsoft.
• Resulting backups (.BAK files) can be safely copied and archived to tape.
• No need for 3rd party software, all the tools you need are already available.

While there are a variety of tools available to perform full SQL backups, we are going to focus on using the tools available with Prolog Manager as well as Microsoft for installations of:

• SQL Server 7 / SQL Server 2000 / MSDE / MSDE 2000
• SQL 2005 / SQL Express

Creating a backup is only the first part of having a successful backup plan. Periodic tests should be performed to ensure the backups you create are valid and can be used to perform a restore in the event of a disaster. At the end of this article, I will show you how to test your database backup.

SQL Server 7 / SQL Server 2000 / MSDE / MSDE 2000

This information is only valid for the SQL / MSDE versions listed above.

Prolog Manager Administrator provides a function for creating scheduled backups of your Prolog Manager Databases. This feature utilizes the SQL Server Agent which ships with SQL Server / MSDE, however by default this service is not set to run automatically when you install SQL Server / MSDE and must be turned on in order for the Prolog backup scheduler to work.

Here is how to make sure the SQL Server Agent is configured to run automatically:

1. On the machine which has SQL Server / MSDE installed, log on to Windows as the Administrator.
2. Open Start > Run.
3. Type in "services.msc" and click Ok.
   
   
4. Locate the service named SQLSERVERAGENT in the list of services.
5. Right click on this and select Properties.
6. Set the startup type to Automatic and if the Start button is enabled, click it to start the service. The status should then say Started.
   
   
7. Click Apply and then Ok. Now the SQLSERVERAGENT will be set to run automatically whenever you reboot Windows.
8. Close the Services window.

Now that the SQL Agent is properly configured, you can set up a scheduled job for Prolog to automatically backup your databases. This is done through Prolog Manager Administrator:

1. Open Prolog Manager Administrator via Start > Programs > Meridian Systems > Prolog Manager > Administration > Prolog Manager Administrator.
2. In the server name box, type or select the name of your SQL Server / MSDE machine which hosts your Prolog databases.
3. For the user name, enter "sa" and type the appropriate password.
4. Click Ok to log in to SQL Server / MSDE.
5. Open the backup scheduler via Database > Backup Database > Schedule Backups.
6. Click New to create a new scheduled job.
7. Enter a job name, for example "Prolog Database Backup".
8. Click the Schedule button to configure the times when the backup process will run. This example shows a daily backup at 2 AM.
   
   
9. Click Ok once you have configured your schedule.
10. Select the Prolog Databases to include in the backup job. You can either pick and choose the databases or check the Include all Prolog Manager Databases to have all databases backed up.
    
    
11. On the Options tab, you can configure the location where the resulting backup (.BAK) files will be output. Prolog automatically will append the date information on the backup file name so you can easily tell when it was produced. Once produced, these .BAK files are safe to copy and archive.
    
    
12. Click Ok to save the scheduled job.
13. The scheduled job list should now reflect the information you just configured.
    
    
14. Click Ok to accept all changes.

After setting this backup process it is always good to check the location you configured in the Options screen, after the process has run, to make sure the .BAK files are indeed being produced. Once you have verified this, all that is left is for you to do is test your backup files.

SQL Server 2005 / SQL Express

This information is only valid for the SQL versions listed above.

Unlike the previous versions of SQL / MSDE, SQL Server 2005 and SQL Express do not ship with a SQL Agent which is compatible with Prolog Manager Administrator's scheduler function. So in the case of these, I am going to use a command line backup with Windows Task Scheduler. This process is very easy and here is exactly how to do it:

1. On the machine which has SQL Server / MSDE installed, log on to Windows as the Administrator.
2. Open Notepad.
3. Copy and paste this text into Notepad:

   REM Prolog Database backup script for SQL 2005 / SQL Express
   REM By: Jason Faulkner (jfaulkner[at]hardhattechnologies.com)
   REM HardHat Technologies: http://www.hardhattechnologies.com/
   REM put date in the format: MM-DD-YYYY
   for /F "tokens=2,3,4 delims=/ " %%a in ('date /t') do set filedate=%%a-%%b-%%c
   REM Example:
   REM Assuming the date is 1/1/2007 and we want to backup PM7Database on SQL Server "MyServerName", this command will produce a SQL backup file: C:\Backups\01-01-2007_PM7Database.BAK
   REM This command assumes the directory C:\Backups exists.
   REM Note: DO NOT include REM in front of the line which actually does your backup.
   REM sqlcmd -E -S MyServerName -Q "BACKUP DATABASE PM7Database TO DISK='C:\Backups\%filedate%_PM7Database.BAK'"
   REM Replace your database information into the line below per the example above
   REM the values enclosed in [ ] should be replaced with your information
   REM Additionally, if you have your Prolog databases on a named instance of SQL,
   REM you will need to enter your SQL Server name after the -S switch like so:
   REM -S MyServerName\PrologInstanceName (of course, replace these appropriately)
   sqlcmd -E -S [Your_Prolog_SQL_Server] -Q "BACKUP DATABASE [YOUR_prolog_database_name_here] TO DISK='[location]\%filedate%_[YOUR_prolog_database_name_here].BAK'"
    

4. Per the instructions in the sample script, replace your database information as appropriate on the last line. You can also add additional databases to backup by copying and pasting the line and replacing the respective fields to match your additional databases.
5. Select Files > Save As.
6. Change the file type to "All Files".
7. Enter "PrologDatabaseBackup.bat" as the file name.
8. Click Ok to save.
9. Close Notepad.

Now you have a backup script which you need to schedule to run daily through Windows Task Scheduler:

1. Open Control Panel.
2. Double click on Scheduled Tasks.
3. Double click on Add Scheduled Task.
4. Click the Browse button when the dialog asking for the program to schedule appears.
   
   
5. Browse to and select "PrologDatabaseBackup.bat" which was created above.
6. Select Daily for the frequency. Click Next.
   
   
7. Set the time to run the backup. Click Next.
   
   
8. Make sure you enter the Windows Administrator account information when prompted for the user to run the task as. This will ensure all commands are executed properly. Click Next.
   
   
9. Click Finish to create the task.
10. You will now see the task, "PrologDatabaseBackup" in the list of scheduled tasks to run at the time you specified.

It is best, after the task has run at its scheduled time, to make sure a backup was created. Once you have verified this, it is good to periodically verify your Prolog Database backup.

Verifying Your Prolog Database Backups

Having an automated backup process is only half of a proper backup procedure. You should also periodically test your backups by restoring the database backup into a test database. This procedure is very easy and gives you peace of mind you are ready when you need it.

To restore a backup database into a test database, do the following on your SQL Server / MSDE machine:

1. Open Prolog Administrator via: Start > Programs > Meridian Systems > Prolog Manager > Administration > Prolog Manager Administrator.
2. Since you are already on the SQL Server / MSDE machine, select "(local)" for the server name.
3. Enter "sa" as the user name and type in the appropriate password.
4. Click Ok to log in.
5. Select Database > Restore Database.
6. Browse to and select the Database Backup File. This is the .BAK file created during the backup procedures.
7. In the Database To Restore drop down list, type a new database name to restore this backup as, for example: "Backup_Test".
   DO NOT select your production database from the list or type it into the box, otherwise the backup database would overwrite it.
8. Click Ok to restore the backup. You will be notified when the restore is complete. Answer no when you are asked to restore another database.
9. Click Cancel to close the restore dialog.
10. In Prolog Administrator, select View > Refresh.
11. Select Database > Reset Permissions To Default. Answer yes to any prompts.
12. Click Ok when the completion notification appears.

Now all that is left is to open the database in Prolog Manager to make sure everything looks in order:

1. Open Prolog Manager.
2. Select File > Open Database.
3. Click the Setup button.
4. Click the Add button on the Connection Setup Wizard.
5. Enter "Prolog Backup Test" for the connection name.
6. Enter "(local)" for the server name. This name will change to the name of the machine when you navigate away, this is normal.
7. Click List Databases. This make take a minute to run.
8. Select the test database name you restored from the steps above.
9. Click Test Connection.
10. You should get a success message.
    If not something went wrong during the backup process and you should repeat the steps from above.
11. Click Save.
12. Click Close.
13. Log into the database as if you were logging into your production database.
14. Portfolio Manager should appear and list all your projects.
15. Congratulations! You have verified your Prolog Database is valid and can be used to restore your data in the event of a disaster.

I hope this article has been helpful in walking you through the proper way to backup, restore and test your Prolog Databases. By taking just a few minutes to both setup a backup process and a few more minutes to test it, you can rest assured you will be ready in the event of a disaster.