How to handle expanded tempdb after server disaster
Greg Winkler
First off I'm a fledgling SQL administrator. Being forced upon me as we
implement SMS <G>. I'm in the process of planning how to recover the SQL
server and having a problem understanding how to handle tempdb. Let me
explain.
After installing SQL server we expanded the tempdb database. In the process
we created a tempdata device for the data portion and a templog device for
the log portion. No sweat.
Now assume a failure of the disk subsystem requiring a re-install of all of
the SQL databases from the nightly dumps. I've read relevent articles from
the on-line companion, TechNet and SQL admin survival guide and am
comfortable restoring the master and other databases - except for tempdb.
When master is restored it will reflect the state of the tempdb database
*before* the failure - a data and log portion on the master device and
additionally the data and log portions on the devices we created during the
expansion. However, I haven't yet recreated the devices to which the
database was expanded and I certainly don't have a dump of tempdb that I
can restore.
What do I have to do to get the devices in line with what is reflected in
master? Do I just create the tempdata and templog devices? When does the
tempdb database get restored to these new devices? Do I have to shrink
tempdb and then re-expand it?
Many thinks from a SQL newbie,
Sanjay Akhare
Greg Winkler wrote:
>
<--deleted-->
>
> What do I have to do to get the devices in line with what is reflected in
> master? Do I just create the tempdata and templog devices? When does the
> tempdb database get restored to these new devices? Do I have to shrink
> tempdb and then re-expand it?
I have a script that recreates all my devices and user databases, and
sets the sp_configure parameters. Last time I had a failure, I ran this
script, and then restored all user databases from backups. Creation of
logins, dropping and recreating users, and then expansion of tempdb
followed. From what I know, there are no benefits of separating the log
and data of tempdb, and you never need to backup this database, as it
gets recreated during startup.
Regards,
Sanjay Akhare
DRT Systems International
Houston TX