December 18, 2002 at 3:34 pm
After I restored Maser DB, it is failure to restart SQL Server. The following is part of errorlog.
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
spid1 Starting up database 'master'.
spid1 Opening file C:\MSSQL7\data\master.mdf.
spid1 Opening file C:\MSSQL7\data\mastlog.ldf.
spid1 Loading SQL Server's Unicode collation.
spid1 Loading SQL Server's non-Unicode sort order and character set.
spid1 Starting up database 'model'.
spid1 Opening file e:\MSSQL7\DATA\model.mdf.
kernel FCB::Open failed: Could not open device e:\MSSQL7\DATA\model.mdf for virtual device number (VDN) 1.
spid1 Device activation error. The physical file name 'e:\MSSQL7\DATA\model.mdf' may be incorrect.
spid1 Database 'model' cannot be opened because some of the files could not be activated.
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Obviously, the wrong path is the reason. Check sysdatabases of Master, the filename for Model is 'e:\MSSQL7\DATA\model.mdf'. However, Model DB is under C:\MSSQL7\data.
I intended to change the value of the filename for Model DB into correct one after restored Master, but it seems impossible. The error message is ¡°Ad hoc updates to system catalogs are not allowed. The system administrator must reconfigure SQL Server to allow this¡±.
What shoule the next step(s) be? I am looking for suggestions.
Thanks
December 18, 2002 at 4:25 pm
I think you have to put the file in the expected location for now. You can use command line switches to set location of both master and the folder containing the error logs, no way that I know of to set the location of model.
Andy
December 18, 2002 at 4:49 pm
Should be able to sp_configure 'allow updates to system tables', and then reconfigure and then change the values manually.
Steve Jones
http://qa.sqlservercentral.com/columnists/sjones
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
December 18, 2002 at 8:14 pm
This should do it
quote:
Moving the Model DatabaseTo move the model database, SQL Server must be started with trace flag 3608 so that it does not recover any database except the master.
NOTE: You will not be able to access any user databases at this time. You should not perform any operations other than the steps below while using this trace flag. To add trace flag 3608 as a SQL Server startup parameter: After adding trace flag 3608, perform the following steps:
Stop and restart SQL Server.
Detach the model database as follows:
use master
go
sp_detach_db 'model'
go
Move the Model.mdf and Modellog.ldf files from D:\Mssql7\Data to E:\Sqldata.
Reattach the model database as follows:
use master
go
sp_attach_db 'model','E:\Sqldata\model.mdf','E:\Sqldata\modellog.ldf'
go
Remove the -T3608 trace flag from the startup parameters box in the Enterprise Manager.
Stop and restart SQL Server. You can verify the change in file locations using sp_helpfile:
use model
go
sp_helpfile
go
December 18, 2002 at 8:39 pm
Good answer Antares!
Andy
December 19, 2002 at 9:07 am
I tried to update sysdatabases for model.The following is my script:
USE master
GO
RESTORE DATABASE master
FROM DISK = 'C:\backup\master\master.bak'
WITH REPLACE
EXEC sp_configure 'allow updates', '1'
RECONFIGURE WITH OVERRIDE
UPDATE sysdatabases
SET filename = 'C:\MSSQL7\DATA\model.mdf'
WHERE name = 'model'
I think you can guess the result. This way doesn't work, because SQL Server terminates the process right after restoring master.
Antares686 is right. I am not able to access any user databases at this time. But I like to share this experience with SQL Server guys/girls.
Moving the Model DB sounds good. I am going to try. But another problem is coming out. Currently, the E: drive on my server is a CD drive.
Well, really appreciate every suggestion I have got so far. I'll let you know the final result.
Happy holidays.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply