DATABASE ADMINISTRATION
Database Backups
Database Management Systems (DBMS) have a set of files
within a set of directories that are always opened while the database is
available to the users. The standard system backup procedure
of backing up files is insufficient to backup the database
application. One needs to use the backup utility within the DBMS
itself or a third-party backup utility that can explicitly handle this
type of DBMS (ex. Microsoft SQL Server or SyBase, or Oracle).
Since third-party applications like CA's ArcServeIT
and Veritas' Backup Exec command around $500 for the file system backup
utility AND an additional $500 for the DBMS backup option, we recommend
using the DBMS backup utility. By scheduling a DBMS backup to create
a daily differential and weekly full backup file on disk and then
scheduling the file system backup to follow the DBMS backup, you are
capturing the closed DBMS backup files with your normal backup. If
your system backup utility and your DBMS backup utility can both write to
tape and coexist on the same tape, then you have the additional option to
do your DBMS backup directly to tape and not waste disk space on
backups.
There are other benefits to having DBMS backups on
disk beyond just a backup. We maintain a test environment for each
of our client databases that allows us to add enhancements that the client
requests and test them on a test copy of the database. With the DBMS
backups on disk, we can easily restore the last full production backup
into our test database, so we're working with real data that provides a
better test platform than older data or simulated data.
Restoring to another database
A
production database restore into a test database
is a simple process that only takes a minute or two. However, there
are different quirks you need to know to accomplish this in the different
versions of MS SQL Server:
-
SQL
7.0: The restore recovers the production database's user index numbers
which is not useful in the test database, so you need to go into the
sysusers table for the test database and delete the user ids for that
database table, then using the normal security functions in the
Enterprise Manager, add users to the test database. If you are not
allowed to edit the sysusers table, then you need to go into the test
database SQL server's property page that oversees all databases on
that SQL server and enable 'Allow modifications to be made directly to
the system catalogs' checkbox in the Server Settings
tab.
|
