Check your backup strategy for SQL Server 7.0
September 13, 1999
Do you have to review your backup
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:
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:
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:
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.
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:
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.
Then restore the database, logs and the final log that you have just saved.
6 rules you might find useful
If you have any suggestions, comments concerning this article, please mail to sven hammesfahr.