Congratulations to Thomas Durichen, DBA and Network Administrator for Florida-based Petplace.com, and Duc Do, DBA at Lyondell Chemicals in Houston, Texas. Thomas won first prize of $100 for the best solution to the March Reader Challenge, "Scaling Back the Schedule." Duc won second prize of $50. Here's a recap of the March Reader Challenge and the solution.

Problem


Mark is the DBA of a large SQL Server 7.0 system. The system's database size has rapidly reached its current 10GB, and Mark expects that figure to triple by year's end. Backups for a database of this magnitude command valuable resources, so Mark wants to pare down the backups in both size and duration. The current backup schedule consists of a nightly database backup and hourly transaction log backups during the day, 7 days a week. Help Mark design a backup schedule that

  • reduces the size of the database backups that SQL Server 7.0 performs during the week
  • reduces the time SQL Server 7.0 would take to restore the database after a failure

Solution


Mark decides that eliminating most nightly full database backups will accomplish the greatest reduction in size and duration of his backups, so he uses SQL Server 7.0's differential database backup feature to help him develop the following schedule:

  • Full database backups on Sunday and Wednesday nights
  • Differential database backups on Monday, Tuesday, Thursday, Friday, and Saturday
  • Hourly transaction-log backups, 24 x 7

Mark chooses to institute differential database backups because they instruct SQL Server 7.0 to retrieve only the changes SQL Server recorded since the last full database backup. The resulting backups are typically smaller in size and shorter in duration than full database backups.

If a failure occurs, Mark can take the following steps to restore the database:

  1. Restore the last full database backup,
  2. Restore the last differential database backup,
  3. Restore all transaction-log backups that took place after the last differential database backup.

By interleaving differential database backups between transaction-log backups, Mark can also reduce the number of transaction-log backups that he needs to restore in the event of a failure.

April Challenge Now, test your SQL Server savvy in the April Reader Challenge, "Moving to a New Filegroup" (below). Submit your solution in an email message to challenge@sqlmag.com by March 15. SQL Server MVP Umachandar Jayachandran, a SQL Server Magazine technical editor, will evaluate the responses. We'll announce the winner in an upcoming SQL Server Magazine UPDATE. The first-place winner will receive $100, and the second-place winner will receive $50.

Here's the challenge: Roger is the chief database architect for several SQL Server 2000 and 7.0 systems. To improve performance and accelerate backup and recovery on the machines, he wants to place several large tables and indexes in their own filegroups. These tables and indexes include tables with clustered UNIQUE constraints, tables with clustered indexes, and a few large nonclustered indexes. What steps must Roger take to move the tables and indexes from the PRIMARY filegroup to a newly added filegroup named FG_CRIT without dropping or recreating the tables?