SQL Server and MSDE Database Backup Recommendations

For SQL Server:

Microsoft SQL Server databases require special back-up procedures. This is because MS SQL Server runs as a "service" and the database is always in use.  If you simply copy a SQL database, or backup using traditional "file-by-file" backup software, you will be backing up the database in a "locked" or "inconsistent" state 1.  MS SQL Server software will only utilize "unlocked" and "consistent" databases. Therefore, if you try to restore this "locked/inconsistent" back-up file later on (i.e., just when you need it the most), MS SQL Server will NOT accept the restored file. 

To be safe, we recommend using the back-up options built into Microsoft SQL Server as found in Enterprise Manager (please refer to Microsoft's SQL Server Help for more information 2). These options allow you to create an unlocked and consistent back-up file. You can use MS SQL Server menus to back-up the database manually or, preferably, to schedule a recurring back-up at pre-defined times (e.g., every 1 AM). Another configuration option specifies where to place the back-up file (e.g., to tape, hard drive, etc.). We recommend having SQL Server place the back-up file on a hard drive, then having your normal back-up routine (e.g., tape, third-party backup software/equipment, or other backup routine) copy this "SQL Server generated" back-up file to another safe location. 

If the production database is ever damaged beyond repair, a recently backed-up (unlocked/consistent) database file can be restored to the pharmacy server, or on any other computer that has SQL Server installed.   (See 3 Note below for an additional option for Ascend-HI users)

For MSDE (Microsoft Data Engine):

As with SQL Server, you should never rely on simple "file-by-file" copy methods to backup an MSDE database. Users may have serious problems restoring files that have been backed-up in this manner. Instead, please follow Microsoft's recommendations to create a reliable back-up of your MSDE database. The following MS Knowledge Base article contains information about backing up with MSDE:

    http://support.microsoft.com/default.aspx?scid=kb;en-us;241397&Product=sql

When including our database name (i.e., Ascend-IP or Ascend-HI) in the command line, be sure to enclose it in brackets [ ]. This is necessary to compensate for our database name containing a hyphen. A sample backup command for Ascend-IP:

    OSQL -Usa -Pip -n -Q "BACKUP DATABASE [Ascend-IP] TO DISK = 'whateveryourfilenameis'"

Once the MSDE back-up file has been created using Microsoft's method, the back-up file can then be copied to tape, etc. for archive storage elsewhere using common third-party backup systems. Back-ups should be done at least daily, and include weekly/ monthly media rotation and off-site storage in accordance with normal IT standards of practice.   (See 3 Note below for an additional option for Ascend-HI users)

Notes:

1 Some sophisticated (i.e., more expensive) backup systems can directly back-up SQL Server databases in an unlocked/consistent state, but most can't. If you don't use MS SQL Server or MSDE options to create a unlocked/consistent backup file, then don't mistakenly interpret the presence of the database on your backup tape or disk as "proof" that you have made a good back-up. Don't assume anything...you really want to be sure of this!

2 Each version of SQL Server may employ different steps for creating manual and automated back-ups. For example, in SQL Server version 7, SQL Server Enterprise Manager allows you to "drill down" through the directory tree from the Console root, through "Microsoft SQL Servers", to "SQL Server Group", to "HANNSON" (example server name), to "Databases", and then to "Ascend-IP" (example database name). The right- mouse menu options for the database "Ascend-IP" lead you through "All Tasks" to several backup/restore options. Use "Backup database" for a manual backup, or use " Maintenance Plan" to employ a Wizard to help you set up an automated back-up routine.

3 Ascend-HI users can also create an initial back-up by using the "Backup" option under Ascend-HI's "Utilities...Database Tools" menus. This should back-up all file types (SQL Server, MSDE and .MDB). To restore the SQL Server or MSDE backup, you can use the "Restore" option under the same Ascend-HI menus. As with external SQL/MSDE backup options described above, Ascend-HI users should not rely on this feature as their only backup option. They should also copy the backed-up file to another safe location (e.g., to tape, another disk drive, etc.) using standard third-party backup software and procedures.

Sitemap
Hann's On Software Resources