This question
can be used to assess the skills of candidates of any skill
level of Microsoft SQL Server database administration experience.
Candidates should have actual experience administrating
Microsoft SQL Server, not just classroom experience.
As
a SQL Server administrator you will encounter a scenario
in which you must restore a single table to a database.
Assuming you have a table that is being reported as being
corrupted, how would you restore this single table to your
production database?
The answer to
this question has several layers with the most experienced
candidates being able to correctly identify all layers.
All candidates should discuss the fact that SQL Server databases
are backed up at the database level and not at an individual
table. Experienced candidates might discuss that it is possible
to back up individual tables by using file/filegroup backups
but due to the complexity of running file/filegroup backups
the majority of DBAs have not implemented these types of
backups
Most candidates
should answer this question by asking a few questions of
their own. One of the questions should be: Does another
copy of the database (table) exist in a different location
that so they can transfer the data over from that location?
Another question they might ask is: Does the data exist
in text files that can be used to reload the table? The
last question the candidate might ask is: Is a database
backup available to restore?
Restoring a full
database backup to a different server or with a different
name and rebuilding the corrupted table from that restored
database will be the most common answer given because it
is the textbook answer. Junior and senior level candidates
will draw upon experiences with systems that have multiple
copies of the database or that are loaded from text files
on a scheduled basis. All candidates should follow up their
answer by describing how they would rebuild the table from
a restored database, an existing copy of the database or
from text files by scripting out the table schema, dropping
the table, using DTS, BCP, or INSERT INTO to move the data
over and checking the table and table permissions by running
existing stored procedures, etc. to double check everything.
Candidates should also mention that if the table is large,
it might pay to drop the indexes (and likely constraints
on a table) prior to the reload. Also, a senior DBA will
mention the relative performance (time constraints) of DTS/BCP/Insert
INTO if the tables are very large.
The most experienced
level candidates will go a step further and explain what
actions can be taken if another copy of the table cannot
be obtained from backups, existing databases, or text files.
Candidates who have worked with large systems know that
space is not often available to restore an extra copy of
their database and should have an alternate plan for these
situations. These candidates will know that sometimes all
of the data or a majority of the data, in the table can
be saved by using index hints on SELECT statements. By forcing
a SELECT statement to use non-clustered indexes, some or
most of the data can often be saved by working around the
corrupted part of the table.
The most experienced
candidates will also mention the effects of dropping and
recreating a table that is involved in replication. These
experienced candidates may also discuss how you will need
to script out all referential integrity constraints the
table participates in and drop those constraints from the
database before you can actually drop the table. These candidates
may also discuss the likelihood of constraint violations
that may occur if not all of the data was successfully saved
and reloaded into the table. Experienced candidates will
also mention that prudent DBAs will force all stored procedures
to recompile and refresh all views referencing the table
to minimize future problems. These experienced candidates
may also mention that if the table contains identity columns,
those columns may need to be reseeded to account for any
recent data that may have been lost
About
the author
Randy Dyess' background includes working with Microsoft
SQL Server for nearly six years as a full-time development
or production DBA and two years as a Visual Basic/Microsoft
Access developer building front ends for SQL Server. He
is currently working as a senior-level development DBA developing
and refining code for one of the world's largest SQL Server
databases. Past experiences have included running over 70
web databases at one time, migrating various database platforms
to SQL Server and working on projects to tune/optimize existing
SQL Server installations. He wrote one book on Transact-SQL
and dozens of articles for web based newsletters. Other
projects include running his own web site devoted to Transact-SQL
and SQL Server administration (http://www.transactsql.com/).
His current email is RandyDyess@TransactSQL.Com.
