BPH


I am in the process of moving my system databases to another volume. I have accommplished the first section pertaining to master database.

I have reached step 3 in the Resource database move section down below.

It states to change the FILENAME path to match the new location of the master database. Do not change the name of the database or the file names.

ALTER DATABASE mssqlsystemresource

MODIFY FILE (NAME=data, FILENAME= 'new_path_of_master\mssqlsystemresource.mdf');

I changed the query to point ot the new location of the master, (E:\MSSQL\Data)

ALTER DATABASE mssqlsystemresource

MODIFY FILE (NAME=data, FILENAME= E:\MSSQL\Data\mssqlsystemresource.mdf');

I get the following error when I run the query:

Could not locate entry in sysdatabases for database mssqlsystemresource. No entry found with that name. Make sure that the name is entered correctly.

What am I doing wrong My syntax must be incorrect. But I can't figure it. Anybody done this before.

These are the steps per msdn.

****************************************************************************************************************

To move the master database, follow these steps.

1. From the Start menu, point to All Programs, point to Microsoft SQL Server 2005, point to Configuration Tools, and then click SQL Server Configuration Manager.

2. In the SQL Server 2005 Services node, right-click the SQL Server (MSSQLSERVER) service and choose Properties.

3. In the SQL Server (MSSQLSERVER) Properties dialog box, click the Advanced tab.

4. Edit the startup parameters values to point to the planned location for the master database data and log files and click Apply. Moving the error log file is optional.

The parameter value for the data file must follow the -d parameter and the value for the log file must follow the -l parameter. The following example shows the parameter values for the default location of the master data and log files.

-dC:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\master.mdf;-eC:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\ERRORLOG;-lC:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\mastlog.ldf

If the planned relocation for the master data file is E:\SQLData and the planned relocation for the log file is F:\SQLLog, the parameter values would be changed as follows:

-dE:\SQLData\master.mdf;-eC:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\ERRORLOG;-lF:\SQLLog\mastlog.ldf

5. Stop the MSSQLSERVER service.

6. Physically move the files to the new location.

7. Restart the MSSQLSERVER service.

8. Verify the file change.

SELECT name, physical_name, state_desc

FROM sys.master_files

WHERE database_id = DB_ID('master');
********************************************************************************************

To move the Resource database, follow these steps.

1. Stop the MSSQLSERVER service if it is started.
2. Start the service in minimal mode. To do this, at the command prompt, enter <SQLPath>\binn\sqlservr -c -f -T3608 where <SQLPath> is the path for the instance of ssNoVersion. For example, C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL. This will start the instance of ssNoVersion for master-only recovery.

3. Run these queries. Change the FILENAME path to match the new location of the master database. Do not change the name of the database or the file names.

ALTER DATABASE mssqlsystemresource MODIFY FILE (NAME=data, FILENAME= 'new_path_of_master\mssqlsystemresource.mdf');

ALTER DATABASE mssqlsystemresource MODIFY FILE (NAME=log, FILENAME= 'new_path_of_master\mssqlsystemresource.ldf');

4. Make sure the Resource database is set to read-only by running this query:
ALTER DATABASE mssqlsystemresource SET READ_ONLY;
5. Physically move the files to the new location.

6. Restart the MSSQLSERVER service.




Re: Moving System databases SQL 2005

Peter Byrne MSFT


Step 2 of the "move the resource database" section should really be to pass the parameters to the service, not start it in a console. So if you have the default instance

net start mssqlserver /f /T3608

Do not use -c when starting as a service. After you are done with step 4, you should "NET STOP MSSQLSERVER" prior to moving the files.

Can you please comment whatever article you are getting these steps from accordingly







Re: Moving System databases SQL 2005

BPH

Peter,

Here's the link to the information that I have followed.

http://msdn2.microsoft.com/en-us/library/ms345408.aspx

In this article, I am on step 8, using sqlcmd.........

Everything up to here has worked fine to the letter.

I just think I haven't quite changed the syntax properly to match my environment.

The new location of the master database is

E:\mssql\data for the master data file

and

E:\mssql\tlogs for the master log file

Thanks






Re: Moving System databases SQL 2005

Peter Byrne MSFT

Step 8 validation will not work for the resource DB as it is hidden without the /f /T3608 flags.






Re: Moving System databases SQL 2005

BPH

It works. I just figured out the problem.

Over night , windows auto updates threw sql sp2 on the box without my knowledge.

I noticed this difference, rebooted the box and went through the steps to start service /f /T3608.

And now the statements work as indicated in step 8.

That's a good doc which I'm sure many will find useful as more sql2k5 installs take place.

Thanks for your help! bph