I am using sql 2005 standard edition, and i want to move my system databases
from the C drive to a new E drive I have setup on my windows 2003 server. I
have first moved the tempdb database to the E drive by using the below
method:

1 using the following code:

Use master;
Go
Alter DATABASE tempdb
MODIFY FILE (NAME = tempdev, FILENAME = 'E:\SQLData\tempdb.mdf' );
GO
ALter DATABASE tempdb
MODIFY FILE (NAME = templog, FILENAME = 'E:\SQLLog\templog.ldf' );
GO

2. Then stopped and start the sql server
3 Verify the file change, by doing a select command from the
sys.master_files
4. Then delete the tempdb files from the original location.

This above method worked fine in moving the tempdb database, but I now want
to move the other system databases. I started with the model database. I
used the above the method, but when i stopped the sql server, I moved the
model databases files to the new location mentioned in the code, but when I
now start the sql service it does not start. The error message I get is :

The request failed or the service did not respond in a timely fashion. In
the event log the error reads Operating system error 2 (error not found)
occured while creating or opening file 'E:\sql\data\modellog.mdf'. Diagnose
and correct the operating system error and retry the operation.


Is there any reason why I would get this error message, I moved the model
files back but still it will not start.

But more importantly I need to know the best way to move the system
databases like model, master, msdb to a new location. I have checked some
microsoft articles but they dont seem to work.. Has anyone moved their
system databases on sql 2005 to a different location and worked. I should
just need a list of steps.
Can anyone advise on the error above and a list of steps in moving the
system databases.

guv (guv@) writes:
Error 2 is (NET HELPMSG 2)

The system cannot find the file specified.

I note that above you have E:\SQLData, the error message says
E:\SQL\Data.

Have you search in Books Online on "Moving system databases"? There is a
good topic for this.


--
Erland Sommarskog, SQL Server MVP, esquel (AT) sommarskog (DOT) se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinf...ons/books.mspx

Thanks for the books online, but are you aware of the steps on how to move,
as I followed some online articles before and they have not worked
"Erland Sommarskog" <esquel (AT) sommarskog (DOT) se> wrote

The steps in the Books Online topic
(http://msdn.microsoft.com/en-us/libr...8(SQL.90).aspx ) are
correct. As Erland pointed out, the folder name that you specified in the
code and the folder name listed in the error message are different. That
is, you show E:\SQLData\... in your ALTER DATABASE statement for the
tempdb database but the error message indicates SQL Server expects to find
the model.mdf file in 'E:\sql\data\... You must have modified the ALTER
DATABASE statement

You should be able to resolve this by doing the following:
1. Create the folder E:\sql\data and move your model.mdf file there.
2. If you made the same mistake in the log file specification, you will also
need to create a E:\sql\log folder and put the model.log file there.
3. Start SQL Server service.
4. Assuming that E:\SQLData and E:\SQLLog are the folders that you actually
want to use, follow the instructions for "Planned Relocation and Scheduled
Disk Maintenance Procedure " in the BOL topic. These instructions will
work for all system databases in 2005 except the master and resource
databases. There are separate instructions for moving these databases in
the topic.

--
Gail Erickson [MS]
SQL Server Documentation Team
This posting is provided "AS IS" with no warranties, and confers no rights
Download the latest version of SQL Server 2005 Books Online from
http://technet.microsoft.com/en-us/s.../bb428874.aspx
Download the latest version of SQL Server 2008 Books Online from
http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
"guv" <guv@> wrote