A workaround for SQL Server 7.0
A little-known aspect of SQL Server 7.0 can prevent you from getting up-to-the-minute database recovery. But you can make some simple system-configuration changes to ensure that your organization's business data is protected.
The problem is that in SQL Server 7.0, if you can't access the primary data file for a database and you try to use backup log prod with NO_TRUNCATE, you'll see a series of error messages:
Server: Msg 3446, Level 16, State 1, Line 1
Primary file not available for database 'prod'.
Server: Msg 3013, Level 16, State 1, Line 1
Backup or restore operation terminating abnormally.
When you try to restore a broken database, a bug in SQL Server 7.0 prevents you from accessing or restoring any log records that SQL Server has written since the previous log backup. But don't panic. You can make some simple changes that will let you recover your data.
Follow the Rules
First, you need to ensure that you're following good database configuration practices. I follow three basic rules when I set up the physical file locations for a database.
- Keep the log and the data on separate physical drives.
- Keep the data and the master database on separate physical drives.
- Mirror the log.
These simple rules will help improve performance and form the foundation for no-data-loss recoveries.
Backing Up the Database
Suppose you back up your database weekly, on Sunday at midnight. Transaction log backups occur every six hours, at noon, 6:00 p.m., midnight, and 6:00 a.m. On Wednesday at 5:00 p.m., you discover that the data drive has failed, corrupting the database.
You need to recover the production database from the log and database backups. Because the last transaction log backup executed at noon and it's now 5:00 p.m., the transactions that have completed since noon are in the transaction log, but not on a tape. So, you can recover only transactions that committed before noon Wednesday, which is unacceptable. Wouldn't you love to have a transaction log backup from the point after the database broke?
An ordinary backup log scans the database's system files to discover the location of the log. But in this example, because the database is inaccessible, an ordinary backup log won't work. However, SQL Server lets you do a special log backup when you use the NO_TRUNCATE option of the backup command—even after the database is inaccessible.
How can SQL Server find the transaction log when the database is unavailable? In SQL Server 6.x and earlier, the master database can find the transaction logs by accessing the sysdevices and sysusages tables, which hold the log location information. When you use
backup log with NO_TRUNCATE
SQL Server retrieves the log location from the master database instead of the prod database system tables. The SQL syntax for SQL Server 6.5 and earlier is
dump log with NO_TRUNCATE
In SQL Server 7.0, Microsoft changed the syntax to
backup log with NO_TRUNCATE
This command bypasses the database and talks to the master database, which directs the log backup in the absence of the data files.
When a drive corruption invalidates the data files, you need to use
Backup log prod to <disk or tape>with NO_TRUNCATE
Then, you can load the database backup, each of the previously backed-up transaction logs, and finally, the last special log backup that you made after the disk crash. If you follow this procedure, you won't lose any committed transactions.
Prev. page
[1]
2
3
next page 
