December 6, 2011 at 9:43 pm
Hello all,
I recently installed a sql server 2008 dev edition. I planned to move my system databases to new location. I was able to move temp db using 'alter database tempdb modify file (name = tempdev, filename = 'location.mdf') and same alter command for .ldf. then I restarted the sql server and it works well.
then i tried moving model database in the same way and it said successfully done. but when I restart the sql server, it doesnt start and gives me the following error.
1.error from eventviewer log: could not open file "path\modeldb.mdf" for the number 1 OS error:2(the system cannot find this file specified.)
2. same error for .ldf file
I am assuming the sql server was not able to detect the model database due to the change of the location. i am not sure on how to proceed on this issue.
thanks in advance.
December 6, 2011 at 10:24 pm
Another DB you must be concerned about is:
http://msdn.microsoft.com/en-us/library/ms190940.aspx
Now put the Model DB back in its original location ....
December 7, 2011 at 6:48 am
when you moved the model database, did you actually move the database files?
temp db is created upon the startup of the instance of SQL so will work by just doing alter file, then restart SQL
for model / msdb you need to alter the files using the T-SQL script you have, stop SQL, move the files to the new path, restart SQL, and all should be good
for master you need to use the SQL Server Config Manager and change the location of the -d and -l parameters in the startup properties of the engine.
December 7, 2011 at 6:49 am
the .mdf and .ldf are still in same old location, but still the server is not startingup.....
I want to know the process to move the system databases to a different location and the precaution or prereqs that we need to take care before doing this next time.
thanks in advance.
December 7, 2011 at 6:56 am
striker-baba (12/7/2011)
the .mdf and .ldf are still in same old location, but still the server is not startingup.....
You need to move/copy (preferably copy) those files to the new location. SQL won't do that automatically for you?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
December 7, 2011 at 7:01 am
so you issued for example
alter database model alter file (name='modeldev', filename='e:\model.mdf')
after which you restarted SQL, but the server would not start
this is due to the model.mdf being in C:\Program File\Microsoft SQL Server\........., if you copy from this location to e:\ (as in my example).
with moving the system databases, I have always found that you need to assign the SQL server engine account full control over the disks which you are moving the databases too.
e.g
I have server1 with drives C, D, E, F, G, H, I, J, K
I install SQL on the C drive, I tell SQL that the User database MDF's go on the G drive, User LDF's on the H drive, TempDB MDF on the I drive, TempDB LDF on the J drive and backups on the K drive.
As you cant specify a location for the system DBs (master/model/msdb) they are placed on the C drive with the SQL install files.
I then need to either grant administrator access to the service account, or grant full control to the disks E and F as this is where I am moving the MDF and LDF to.
I then open up SQL, issue my alter database commands, stop the SQL service, move the model and msdb files to drives E and F, open up Config Manager, change the master database location, and start SQL and everything is ok.
December 7, 2011 at 4:43 pm
Hello Anthony, thanks for that suggestion. I added my service account to admin group and it worked like a charm. your replies were very helpfull.
December 8, 2011 at 1:39 am
Not to worry happy to help
The best solution would be to give the account full control on the disks as this would then follow the best practice of lowest access for service accounts. Giving admin access is the quick, dirty, easy win and gives you a bigger attack surface.
December 19, 2011 at 8:12 am
When you get everything working again, it is time to think about what your objectives were in doing this work.
There are no integrity or performance advantages in moving master, model and the Resource db from their default locations. All that you do is to add risk to your SQL Server installation, as shown by the fact that you had the problems you did.
There is a long history of SPs and CUs not working if master or the Resource DB has been moved, so coupled with the fact you gain nothing then it is best by far to leave these databases where they are put. I do not know of upgrade issues affecting model, but you have far higher priority issues than moving a 2MB database just for the fun of moving it.
Some people say that there is no point in moving the msdb files, while others say these should be treated the asme as any user database. In the past I have favoured moving msdb, but I am less certain now that it is worth the time to do it.
tempdb is the only system database that should be moved. Ther aer a lot of articles about the best configuration for tempdb and it is best to do a search for these befoer deciding what you should do for your installation.
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
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply