Check your backup strategy for SQL Server 7.0


Do you have to review your backup
strategy?

Information in this article applies
to: Microsoft SQL Server Version 7.0

Microsoft SQL Server makes many tasks
easier. Especially
organizing the backup and recovery process has become much easier, because
no devices have to be created and the database itself can also be rebuild
automatically. 

if you plan to be able to recover your
data up to the point of failure then be aware of some
restrictions
!

 

When you create a database you can specify one or more
database and log files for the database. In SQL Server 7.0 every database
uses at least 2 files: One for the data and one for the transaction log.
With that in place you can backup the whole database and then continue
with backing up the transaction logs.

In case of a failure in the data
part you can save the latest
transactions (that is all transactions that took place between the last
backup and the point of failure) and then restore the first the data, then
all previously saved logs and
finally the changes that occured between the last backup (either database,
incremental or log).

Sounds good, but your database has to follow a
certain layout to make this possible. If you have a look in Books Online
you’ll find the following description for backing up the active part of
the transaction log: 

Backing Up the Active Transaction Log

It is possible to back up the currently active transaction log
even if the database is inaccessible, for example due to media
failure, …

Do not stop reading! The important part comes now:

providing both the primary data file and the
transaction log files are accessible.

So, if you want to maintain a system that can be restored
up to
the point of failure, you need at least three files:

  • The primary file in filegroup PRIMARY for all
    system objects.
  • One or more datafiles for table data, indexes
    and text data in filegroups other than PRIMARY
  • One or more transaction log files

What
you should do

Create one or more filegroups for all the
data part of your database. Make sure you put all tables, indexes and text
data on this new filegroup. You can use ALTER DATABASE to make this new
filegroup the default. That will make things easier.

Here is an example script for doing this:

USE master
GO
/* Make Filegroup DATA the default filegroup in database Example: */
ALTER DATABASE example 
MODIFY FILEGROUP data DEFAULT
GO
USE example
GO
— Check whether status has been updated in sysfilegroups:
SELECT groupname FROM sysfilegroups WHERE status  = 16

 

When you create new objects in Enterprise Manager,
make sure you specify the correct filegroup. Enterprise Manager tends to
put all objects on the PRIMARY filegroup even if you specified a different
default filegroup.

Check the database for objects that have by accident
been placed on the default filegroup. The following script will help you
with this task:

PRINT ‘The following objects are not set up for “POINT OF FAILURE” recovery:’


SELECT ‘Warning’ =
        CASE
          WHEN i.indid < 2 THEN ‘Table ‘ + o.name + 
                                    
‘ is located on the primary filegroup’
          WHEN i.indid = 255 THEN ‘Text data of table ‘ 
              
+ o.name + 
              
‘ is located on the primary filegroup’
          ELSE ‘Index ‘ + i.name + ‘ (Table ‘ + o.name 
              
+ ‘) is located on the primary filegroup’
        END,
        o.name as Tablename, 
        i.name as Indexname, 
        s.groupname, 
        i.indid FROM sysfilegroups s 
          INNER JOIN sysindexes i
               
ON s.groupid = i.groupid
               
INNER JOIN sysobjects o
                     
ON i.id = o.id
        WHERE o.type =’U’ 
        AND s.groupname = ‘PRIMARY’

 

If this scripts issues warnings, you should recreate
the objects on a different filegroup (Enterprise Manager can here be
helpful).

Now that you have got everything settled, plan your
backup strategy. I suggest to include the following items.

  • Full backup (to start with)
  • Filegroup backups of PRIMARY and your own filegroups.
  • Regular transaction log backups

In case of a failure you can then recover up to the
point of the failure:

Scenario 1: PRIMARY filegroup is damaged

Restore the filegroup backup and any transaction log
backups you have

Could look like this:

— First restore system tables from
backup:
RESTORE DATABASE Example
        FILE = ‘Example_SYS’,
        FILEGROUP = ‘PRIMARY’ 
        FROM DISK=’G:mssql7backupprimary.bak’
        WITH NORECOVERY, REPLACE


— Then restore the log backup.
RESTORE LOG Example FROM DISK=’G:mssql7backuplog.bak’

 

This should bring your database back to a consistent
state.

 

Scenario 2: Your data filegroup(s) become corrupt

Save the active part of the transaction log,
specifying the NO_TRUNCATE option.

Example:

BACKUP LOG Example 
       TO DISK=’G:mssql7backuplastlog.bak’ 
       WITH NO_TRUNCATE

Then restore the database, logs and the final log
that you have just saved.

 

 

6
rules you might find useful

  • Create at least three files/filegroups
    for every database
    (for System tables, User tables, Transaction log)
  • Do not store tables, indexes or text data on the
    primary filegroup
  • Make a filegroup other than PRIMARY the default
    filegroup for your database
  • Take care in Enterprise Manager when creating
    objects. EM usually defaults to PRIMARY when creating new objects
  • Use filegroup backup to backup the PRIMARY filegroup
  • Do not forget to backup the active part of the
    transaction log when your data filegroup(s) become damaged (WITH
    NO_TRUNCATE)

If you have any suggestions, comments
concerning this article, please mail to sven
hammesfahr
.

Latest Articles