February 25, 2007 at 9:39 pm
SQL Server 2000, SP4.
Does anyone have a robust method for recovering tempdb on a different server when the tempdb has multiple logical filenames? This is required for out Server Recovery/Disaster Recovery plan.
Eg tempdb has been defined with 4 logical files tempdev, tempdev2, tempdev3 and templog.
The standard 'alter database' method works fine on tempdev and templog, but it ignores the existence of tempdev2 and tempdev3.
Otherwise I am going to do have to do the unthinkable and fix sysaltfiles directly to get around this tempdb bootstrap issue.
February 26, 2007 at 12:00 am
Tempdb can't be restored or recovered...
Tempdb gets recreated every time when restart sql services...
You have to use Alter database tempdb....
MohammedU
Microsoft SQL Server MVP
February 26, 2007 at 3:05 pm
Alter database cannot be done on tempdev2 and tempdev3 - it says they do not exist.
The entries do exist in sysaltfiles. What I suspect is happening is the tempdev2 and tempdev3 physical files are invalid at startup, so it ignores them when it creates tempdb. Or the 'minimal mode' startup creates a default version of tempdb (this is why tempdev and templog do exist and can be altered).
The 'file fix' workaround on sysaltfiles did work, but it will always be suspect in my opinion.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply