In Search of Older Data

Congratulations to Igor Chernyakov of US Bank, in Minneapolis, Minnesota, and Darren Scott, an IT consultant from High Point, North Carolina. Igor won first prize of $100 for the best solution to the July Reader Challenge, "In Search of Older Data." Darren won second prize of $50. Watch SQL Server Magazine UPDATE for next month's Reader Challenge. In the meantime, here's the solution to July's Reader Challenge.


Bruce, who just started a new job as a DBA, is excited because he'll be working with the latest products and technology. His company uses SQL Server 7.0 but plans to migrate to SQL Server 2000 after the release proves stable—probably in the fall. Unfortunately, Bruce realizes that, because of his heavy workload, it might be a while before he can tighten the security policy as he'd like. For example, several developers' permission levels on production servers are too high. Bruce also needs access to a table's previous version in case someone performs a malicious delete or update operation. He knows SQL Server 6.5 lets you perform a table restore, with some restrictions, but he's not sure how to get the same results with SQL Server 7.0. Help Bruce plan for an individual table restore in his new SQL Server 7.0 environment.


SQL Server 7.0 doesn't have SQL Server 6.5's LOAD or RESTORE TABLE statements, which let you restore a table from either a table backup or a database backup. Bruce considers placing key tables in their own filegroups, but he realizes that when restoring a filegroup, he would also have to apply a consecutive series of transaction log backups up to the current transaction before the database would be accessible. Thus, with SQL Server 7.0, he can't access an older version of a table through filegroup backup and restore. (However, Microsoft plans to support partial restore at the file or filegroup level in SQL Server 2000.)

Instead, Bruce makes sure he has enough disk space to perform a full database restore into a database with a different name. Bruce then needs to perform regular database and transaction log backups for the databases. When he needs to access an older version of a particular table, he can restore the database, then restore the transaction log backups, using the STOPAT parameter to halt just before the operation that corrupted the table data. When restoring the database, Bruce must use the restore statement's MOVE option if the database copy is on the same server as the original; otherwise, the system will overwrite the original files. In some cases—for example, if Bruce lacks disk space—restoring the earlier database version on the same server as the original version isn't practical; for these situations, Bruce identifies an alternative SQL Server, running the same character set and sort order, for the restore.

Bruce then devises a plan for moving the older version of the table from the copy database into the original database. If both databases are on the same server, he prefers SELECT INTO or INSERT … SELECT to move the table. However if he has to move data between servers, he prefers Data Transformation Services (DTS). Here's a checklist that Bruce can use to plan and implement an individual table restore:

  • Make sure to perform regular database and transaction log backups.
  • Don't turn on the "select into/bulkcopy" or "truncate log on checkpoint" database options, which prohibit transaction log backups.
  • Make sure you have enough disk space available to restore the database. SQL Server creates database files as large as they were when you backed them up.
  • If you don't have enough local disk space, identify another SQL Server with the proper character set and sort order to restore into. Make sure the server can access and read the backup (either from a file or tape).

As an alternative, Bruce can schedule regular bulk copy program (bcp) or DTS jobs to transfer key tables to a file or another database. He can then use these table copies to access an older version of a table.

Hide comments


  • Allowed HTML tags: <em> <strong> <blockquote> <br> <p>

Plain text

  • No HTML tags allowed.
  • Web page addresses and e-mail addresses turn into links automatically.
  • Lines and paragraphs break automatically.