June 21, 2008 at 12:20 pm
hi,
Iam trying to move Model database from the C drive to D drive.
I created a DATA n Log folder in D drive n I executed alter database commands. Then Stopped the sql server n Restarted but sql server is not starting. I found the following error in event viewer
FCB::Open failed: Could not open file D:\Data\model.mdf for file number 1. OS error: 2(The system cannot find the file specified.)
FileMgr::StartLogFiles: Operating system error 2(The system cannot find the file specified.) occurred while creating or opening file 'D:\Log\modellog.ldf'. Diagnose and correct the operating system error, and retry the operation.
How can I start the sql server now?
June 21, 2008 at 2:07 pm
Since you didn't mention it in the original post...did you physically move the actual files to the new directory?
Joshua Jones
Director, Global Database Services
PGi
June 21, 2008 at 2:16 pm
Did you follow instructions here? http://support.microsoft.com/kb/224071
June 21, 2008 at 5:25 pm
It worked after moving the data files to new location.
moving the physical files mean copying the model n modellog from default location to new location? is that right?
I did not moved physical data flles for tempdb,But its worked fine. So there is no need move physical files(i.e copying tempdb n templog from default location to new location) for tempdb?
June 21, 2008 at 10:18 pm
Hi Madhu,
For tempdb you don't need to move the physical files since tempdb will be created each time when SQL services gets restarted hence execute ALTER DATABASE command for tempdb.
Model and Msdb databases physical files should be moved to the new location, then start sql service in single user mode, detach model & msdb then attach both the databases pointing to the new location.
For master database just stop sql service, move physical files, update startup parameters and start sql service.
For more refer the link below
Regards..Vidhya Sagar
SQL-Articles
August 26, 2008 at 2:27 pm
Just a note of encouragement:
Recently moved model according to http://support.microsoft.com/kb/224071.
Problem: Kept getting "Cannot detach an opened database when the server is in minimally configured mode" when i tried sp_detach_db 'model'
Solution: after setting the startup parameters but before cycling the server: close all queries in SMS, open a new query and set your default db connection (Query|Options) to "Master", then close it and close the object browser. Stop Reporting services (maybe Analysis and Integration too). Then cycle the SQL service and you should be able to sp_detach_db 'model'.
August 26, 2008 at 2:35 pm
SQL Server 2005 and SQL Server 2000
In SQL Server 2005 and in SQL Server 2000, you cannot detach system databases by using the sp_detach_db stored procedure. When you try to run the sp_detach_db 'model' statement, you receive the following error message:
Server: Msg 7940, Level 16, State 1, Line 1
System databases master, model, msdb, and tempdb cannot be detached.
To move the model database, you must start SQL Server together with the -c option, the -m option, and trace flag 3608. Trace flag 3608 prevents SQL Server from recovering any database except the master database.
Note You will not be able to access any user databases after you do this. You must not perform any operations, other than the following steps, while you use this trace flag. To add trace flag 3608 as a SQL Server startup parameter, follow these steps:
1.In SQL Server Enterprise Manager, right-click the server name, and then click Properties.
2.On the General tab, click Startup Parameters.
3.Add the following new parameter:
-c -m -T3608
If you are using SQL Server 2005, you can use SQL Server Configuration Manager to change the startup parameters of the SQL Server service. For more information about how to change the startup parameters, visit the following Microsoft Developer Network (MSDN) Web site:
http://msdn2.microsoft.com/en-us/library/ms190737.aspx (http://msdn2.microsoft.com/en-us/library/ms190737.aspx)
After you add the -c option, the -m option, and trace flag 3608, follow these steps:
1.Stop and then restart SQL Server.
2.Detach the model database by using the following commands:
use master
go
sp_detach_db 'model'
go
3.Move the Model.mdf and Modellog.ldf files from the D:\Mssql7\Data folder to the E:\Sqldata folder.
4.Reattach the model database by using the following commands:
use master
go
sp_attach_db 'model','E:\Sqldata\model.mdf','E:\Sqldata\modellog.ldf'
go
5.Remove -c -m -T3608 from the startup parameters in SQL Server Enterprise Manager or in SQL Server Configuration Manager.
6.Stop and then restart SQL Server. You can verify the change in file locations by using the sp_helpfile stored procedure. For example, use the following command:
use model
go
sp_helpfile
go
This is from this link http://support.microsoft.com/kb/224071
September 6, 2009 at 10:32 pm
Just had a very similar issue this morning with 2008 Ent on Win2008Ent..
My problem was that the SQLServer service account did not have permissions on the file in the new location. The Account should have full access to each folder and each file. Under Win2008Ent assigning the permissions after the files were copied DID NOT automatically assign the permissions to existing files - these needed to be done manually.
Once done, worked as expected.
One other note I'd add: flags "-f -T3608" did not allow the service to start, and gave me errors about the master database log file. Nor could I start it with any other flags.
S.
September 8, 2009 at 7:31 am
This thread shows the problems that can happen when you want to do something that is totally unnecessary.
There are no performance or data integrity advantages to moving model database. There are problems you can encounter when moving it.
Many of the most experienced people (and me) advise that you leave model where it gets installed and use your time to do something that will give you a benefit.
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
September 9, 2009 at 3:14 pm
Hi All,
I've also used Microsoft Article http://support.microsoft.com/kb/224071 many times.
One think I found which isnt mentioned in the Article and could be the same issue that "Old Hand" experienced is that in SQL 2005 (and probably 2008) the SQL Configuration Manager startup parameters (-c -m -T3608) need to be seperated by a semi colon. ;
For example...
-m;-t3608;-dD:\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\master.mdf;-eD:\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\ERRORLOG;-lD:\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\mastlog.ldf
I also found that you can ommit the -c from the parameters, it doesnt really serve a purpose apart from shortening the SQL startup time.
Without the switch you are still able to move the model and msdb databases.
Now get a move on. 😉
Ant
October 15, 2010 at 7:16 am
My problem in SQL 2005 is similar to this. I used the Alter Database command to move the model database (it worked on the tempdb), stopped SQL, moved the files from the original C drive to the E drive, and started SQL back up. Now I am not able to get SQL to start again. The error message I receive is: The request failed or the service did not respond in a timely fashion. Consult the event log or other applicable error logs for details.
When I add -m;-T3608; to the properties, I am able to start SQL back up, but when I go to log into SQL Server Management Studio I get the error message: Login failed for user 'XXX'. Reason: Server is in single user mode. Only one administrator can connect at this time. (Microsoft SQL Server, Error: 18461). I had already turned off the SQL Server Agent as per http://msdn.microsoft.com/en-us/library/ms188236(SQL.90).aspx but there must be something else I need to turn off in order to get back into SQL.
I am remotely logging into a server to perform these actions if that makes a difference. Any help would be greatly appreciated!
October 15, 2010 at 7:25 am
use notepad to view the errorlog, any errors in there?
---------------------------------------------------------------------
October 15, 2010 at 7:39 am
Yes, here is the text as to where the errors begin:
Recovery is complete. This is an informational message only. No user action is required.
Login succeeded for user 'NT AUTHORITY\SYSTEM'. Connection: trusted. [CLIENT: ***.***.***.**]
Starting up database 'ReportServer'.
Error: 18461, Severity: 14, State: 1.
Login failed for user 'DATABASETEST\myuseraccount'. Reason: Server is in single user mode. Only one administrator can connect at this time. [CLIENT: ***.***.***.**]
Error: 18461, Severity: 14, State: 1.
Login failed for user 'NT AUTHORITY\SYSTEM'. Reason: Server is in single user mode. Only one administrator can connect at this time. [CLIENT: ***.***.***.**]
Error: 18461, Severity: 14, State: 1.
Login failed for user 'DATABASETEST\IUSR_DATABASETEST'. Reason: Server is in single user mode. Only one administrator can connect at this time. [CLIENT: ***.***.***.**]
and the other error log shows:
Starting up database 'model'.
Server name is 'DATABASETEST'. This is an informational message only. No user action is required.
Error: 17207, Severity: 16, State: 1.
FileMgr::StartLogFiles: Operating system error 2(The system cannot find the file specified.) occurred while creating or opening file 'E:\SQLData\model.ldf'. Diagnose and correct the operating system error, and retry the operation.
File activation failure. The physical file name "E:\SQLData\model.ldf" may be incorrect.
The log cannot be rebuilt because the database was not cleanly shut down.
Error: 945, Severity: 14, State: 2.
Database 'model' cannot be opened due to inaccessible files or insufficient memory or disk space. See the SQL Server errorlog for details.
October 15, 2010 at 7:53 am
'E:\SQLData\model.ldf' - so is that file there? are you sure its not called modellog.ldf?
---------------------------------------------------------------------
October 15, 2010 at 8:01 am
I have model.mdf and modellog.ldf in E:\SQLData. So I just need to rename the log file?
I just tried that and it worked! It seems so obvious now but thank you so much for your help.
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply