April 29, 2009 at 3:40 am
Hi Folks,
I have struggled with this for days.
it seems that you cannot restore a sql 2000 master database on a different server and change the location of the tempdb database has changed. Is that everyone elses experience?
The reason I believe that is is that sys.sysfile entry for tempdb db is the tempdb database not the master so if those files are not there tempdb can't start. ALTER database will not work because it needs tempdb to work in the first place for it to happen.
detaching and attaching will not work because it needs the tempdb database to be database id 2. To achieve that you would need to detach model database but you need the model database to create tempdb so it needs to be attached before you can attach tempdb meaning that model would need to be dbid 2 and master would be 3.
April 29, 2009 at 6:54 am
If you are going to restore the master database to another server the directory structure should match. does the directory where tempdb is expected to be exist?
the location of the tempdb files is stored in master.dbo.sysaltfiles
---------------------------------------------------------------------
April 29, 2009 at 11:36 pm
so you are saying that there isn't a way to change the directory of tempdb on a different server if it does not exist in the current directory where it is suppose to reside?
April 30, 2009 at 3:26 am
are you not able to recreate the same directory structure on the target server as the original server?
Why do you want to restore the master to a different server?
---------------------------------------------------------------------
April 30, 2009 at 9:19 am
Master database is the startup database.... so when you restore the master database means you are making the same system for another database...
hence you have to make the same setup for new database, and if require you change change the location....
Also If you are moving master database only as it has logins you can script the login to new databse.
HTH.
Vinay
Thanx.
Vinay
http://rdbmsexperts.com/Blogs/
http://vinay-thakur.spaces.live.com/
http://twitter.com/ThakurVinay
April 30, 2009 at 7:22 pm
I am moving my master database to a different server to test disaster recovery. Our 2000 boxes have no high avialibility so I need to get them up ASAP. If a server dies (say by blow up power supply) it the databases will need to me moved to another server. I am concerned if a box I am given has different drive letters than the broken server.
May 1, 2009 at 4:10 am
first of all if its a DR box for a particular server insist on the same drive configuration and install SQL on it to exact same specifications as live box, then you won't get these problems.
If you are not going to be provided with the box until the disaster happens how are you going to get you database backups to it?. A cheap high availability solution for SQL 2000 is log shipping, I suggest you look into that.
If you want to get out of your current situation where drive configuration does not match then after restoring the master database, SQL will come down, restart it with flag -T3608, this won't recover msdb , model and tempdb and you should be able to run alter database to move tempdb, and use detach\attach for msdb and model. see
http://support.microsoft.com/kb/224071
then you can stop SQL, start normally and restore your msdb (sqlagent needs to be down)
after doing this you will need to update addserver\dropserver to update sysservers in master and tell the msdb its on a new server by running
update sysjobs set originating_server = 'new server name'
either that or rename the server.
As you can see this is getting complex, so avoid having to do it!
look at scripting out logins via sp_help_revlogin to avoid having to restore master.
Only other information you have to worry about that is stored in master is linked servers and possibly user defined errors added to sysmessages.
---------------------------------------------------------------------
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply