model database won't restore

  • This is going to be a /facepalm moment I just know it as I'm sure I'm making a schoolboy error (repeatedly).

    Fortunately I'm not in a full DR situation, but was training one of my juniors through the process in case I ever have to talk him through it remotely, and I have hit a problem I have never encountered before.

    My setup is SQL Server 2008 SP2 running on a Windows 2008 server.

    Now my DR documentation and processes have always demanded that the Network team rebuild me a server with the same drive configuration as the server that has curled up it's toes - mainly because it makes my life so much easier. However for this training session they set the server up for me but I forgot to send them the drive configuration, and as a result got back different drive letters. I know I can easily get them changed, but though this would be a good example of what happens in this situation; it wasn't documented in our procedures, it had never happened to us before, but it could.

    The build went okay, I trained through moving the system databases, altering the startup parameters, keeping a copy of the system databases in case something went wrong and we had to reset and start again, etc. No problems so far, and then things went pear shaped.

    We restored the master database from another server with no issues, started the server up with the T3608 switch so we could alter the tempdb location and those of the model and msdb databases as the new master had them on drive letters that didn't exist.

    We hit the usual catch-22 situation that it couldn't start the tempdb because the model database was missing, and wouldn't allow the model to be restored without the tempdb (being created by the model, which didn't exist, etc.) T3609 to allow it to use the existing tempdb data files and we got over that one.

    So now we have the newly restored master, the original model, msdb and tempdb files, a server that would start fine, and we needed to restore the model and msdb

    This is the command I issued:

    RESTORE DATABASE model FROM DISK='?:?????????.bak' WITH REPLACE

    We were immediately greeted with the error that it couldn't restore the mdf or ldf files as they were pointing to the old locations of the old server. Although the model database was in it's new location and the master knew this (SQL Server would start and run normally even without the user databases present), the restore was trying to restore the databases to their original location from the old server. So I went for the obvious:

    RESTORE DATABASE model FROM DISK='?:?????????.bak' WITH FILE=1, MOVE 'modeldev' TO '?:?????????\model.mdf', MOVE 'modellog' TO '?:??????\modellog.ldf', NORECOVERY, NOUNLOAD, STATS=10

    The message now is that I can't move a system database with RESTORE.

    I've tried Google, MSDN and TechNet and can't find anything to help, so I must be doing something really basically wrong, but I've gotten myself into so much of a rut with it I need someone just to slap me in the face and show me what I'm doing wrong. :hehe:


    ---------------------------------------
    It is by caffeine alone I set my mind in motion.
    It is by the Beans of Java that thoughts acquire speed,
    the hands acquire shaking, the shaking becomes a warning.
    It is by caffeine alone I set my mind in motion.

  • Have you tried restarting SQL with two trace flags as below.

    SQLServr.exe –T3608 –T3609

    Also here is an artcle from Gail Shaw which explains this in a really good way.

    https://www.simple-talk.com/sql/backup-and-recovery/the-sql-server-instance-that-will-not-start/

  • Yes, we'd already ploughed through that articel and were using both trace flags. But the message we keep getting in "The system database cannot be moved by RESTORE"


    ---------------------------------------
    It is by caffeine alone I set my mind in motion.
    It is by the Beans of Java that thoughts acquire speed,
    the hands acquire shaking, the shaking becomes a warning.
    It is by caffeine alone I set my mind in motion.

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply