April 13, 2006 at 4:06 am
Morning,
I wrote recently about moving Dbases between servers and promised to keep the group up to date.
Progress has been made but I am having problem moving the Model Dbase. I am using Msoft article 304692 as a template and see the following paragraph (foot of page 2). " You need to detach the model database because you cannot directly overwrite it by using the RESTORE statement. You have to remove the system table references for this system database before the database is restored. In addition you cannot drop the database for the purpose of removing those system entries."
I have detached the Model Dbase from the server, but have no idea how to remove the system table references as mentioned in the above article.
Help much appreciated.
Colin
April 13, 2006 at 4:44 am
try the following
In SQL Server Enterprise Manager, right-click the server name, and then click Properties.
2. On the General tab, click Startup Parameters.
3. Add a new parameter as "-T3608" (without the quotation marks).
After you add trace flag 3608, following these steps: 1. Stop, and then restart SQL Server.
2. Detach the model database as follows:use master
go
sp_detach_db 'model'
go
3. Move the Model.mdf and Modellog.ldf files from D:\Mssql7\Data to E:\Sqldata.
4. Reattach the model database as follows:use master
go
sp_attach_db 'model','E:\Sqldata\model.mdf','E:\Sqldata\modellog.ldf'
go
5. Remove the -T3608 trace flag from the startup parameters box in the Enterprise Manager.
6. Stop and restart SQL Server. You can verify the change in file locations using sp_helpfile:use model
go
sp_helpfile
go
this should help
MVDBA
April 13, 2006 at 5:03 am
Mike,
Thanks for the quick response. I am moving databses between servers (A to B) and want to restore the model backup from a .bak file taken on serverA. Server A is still in commission and so I cannot move its tables. This is all a "test run" pending D Day! I am not sure if the Model on both servers are the same and so I am playing safe by using back up files to restore "like for like".
Colin
April 13, 2006 at 5:52 am
could i ask why you're moving the model? it's only the template for new databases.
it might be easier to just to a DTS transfer of all the "additional" objects you have created inside model from server A to server B
MVDBA
April 13, 2006 at 6:32 am
Mike,
Simply following the procedure in the KB article. I assumed (wrongly?) that as MS mentioned the move that I had to do it! As far as I know the ModelDb has never been changed on Server A. So, it looks like I should just reattach the model Dbase using the standard SP and then move on to restoring the msdb.
I want to restore the msdb as there are many DTS packaes and I want to retain the graphical interface, which is most useful. I believe that it wil be lost if I use other methods such as save to file and then move the file.
Thanks
Colin
April 13, 2006 at 7:05 am
if the servers are on the same service pack version then there will be no difference between them.
even if they're on different versions then i don't think there will be a difference - i can't see any thing in the service pack change lsist that indicates a change to model
which pretty much means you don't need to move your model DB - like i said - model is the template used when creating a new db - if you have no user objects in model then you don't need to move it at all
MVDBA
April 13, 2006 at 10:00 am
I have my Master Dbase restored and all the user DBs are marked as suspect because I have yet to restore their .BAK files. Happy with that - at present.
Am trying to restore the msdb as it has all my DTS packages. Have tried restoring but I keep getting told that Exclusive access could not be obtained because the database is in use. I have tried by right clicking the Dbase and selecting restore and also using code in QA. Both with the same result. No other users will be using the servver, as I am creating it from scratch. If I try single user mode I am told that only master can be restored whilst in single user mode.
Thanks for any enlightenmnet
Colin
April 13, 2006 at 10:10 am
2 things you can do
1)stop sql agent -it uses msdb so you won't be able to restore
2) if that fails right click ont eh database and choose properties and set it to single user mode. although you'll only be able to have one conenction open to it - so close qa and sql agent and use EM to to the restore.
you'll also need to do an sp_change_users_login against MSDB
MVDBA
April 13, 2006 at 10:21 am
Mike,
Thanks for all your help, will proceed on Tuesday morning.
Could you very kindly expand on your comment about sp_change_users_login?
Colin
April 13, 2006 at 12:42 pm
Mike, one quick question here:
Sometimes you get the below error message while you try to detach the system databases like sp_detach_db 'model'..
ERROR 7940 System databases master, model, msdb, and tempdb cannot be detached. ?
Any idea how to go abt it?
I checked @ http://www.sqlservercentral.com/columnists/ckempster/deattachandreattachdatabases.asp but no success.
Let me know if you can help with this query.
Thanks !
September 17, 2008 at 3:03 am
Hi,
A bit of an old thread to bring back but I am having problems moving my 'model' database. I have added the startup parameters -c -m -T3608, stopped and started the service and ran the detach and it just comes up with:
Msg 7940, Level 16, State 1, Line 1
System databases master, model, msdb, and tempdb cannot be detached.
September 17, 2008 at 3:16 am
This should prove helpful
September 17, 2008 at 3:22 am
Colin Betteley (9/17/2008)
http://support.microsoft.com/kb/224071%5B/quote%5D
Thats the article I have been working off and the MSDN one, maybe I am missing something I will give it another go.
September 17, 2008 at 4:47 pm
Make sure by inspecting the sql server logs thattrace flag 3608 is properly specified and sql server has started in single user mode(message in sql server error log). Sometimes specifying the trace incorrectly make you assume its in single user mode and starting with trace flag but actually it isn't(check sql server logs thoroughly).
HTH,
MJ
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply