December 16, 2005 at 4:59 pm
I upgraded a SQL2000 Developer installation to SQL2005 Developer. SQL2005 decided to put the data on the same drive as the programs, instead of where the SQL2000 installation had them (SQL2000 programs were on D, data was on O). There are MSSQL.1 and MSSQL.2 directories on the same level as the 90 program directory; MSSQL.1 appears to contain the master/model data, etc., and the MSSQL.2 appears to be the OLAP data (I'm not sure what the latter is yet).
1. Is it possible to move the location of the system data files (i.e. everything underneath MSSQL.1 except the binn directory) on an existing installation?
2. If not (I assume I have to uninstall and reinstall), where is the data location specified in the install? On SQL2000, where to put the data was a separate directory option at install time. On SQL2005, I didn't see an option for the Data directory, only for a few program options.
3. Why doesn't SQL2005 respect the data location of SQL2000? (A moot point now, but curious nonetheless).
Thanks,
Vince
December 19, 2005 at 11:42 am
MS KBs shows you how to move system databases in SQL 2000. For SQL 2005 there must be a way but I haven't seen one.
To move user databases you can specify the default location: right click on server name in SQL Server Management Studio, Properties, Database Settings. At the bottom there are 2 boxes for the location of data files and log files.
December 21, 2005 at 4:49 pm
I finally found the information on another MS forum. As I mentioned in the original message, I wasn't worried about user databases, but system ones (master, model, msdb, tempdb). I use detach/attach for user databases.
Master is moved by changing SQL2005's startup parameters. These are configurable using SQL Server Configuration Manager, on the Advanced tab. Steps are:
1. Change the startup parameters to the new location.
2. Stop the server.
3. Copy/move the files to the new location.
4. Start the server.
5. Confirm the changes with SELECT * FROM sys.master_files.
The other system databases can be moved with ALTER DATABASE, but the server has to be started in recovery mode (or at least the instructions I found said it did).
1. Stop the SQL service from Configuration manager.
2. Start the service manually from a command line with \binn\sqlservr -c -f -T3608 (or add the trace flag to the startup parameters and re-start through ConfigMgr).
3. For each database you want to move, do an ALTER DATABASE MODIFY FILE (NAME=, FILENAME= '\'),
where is the database name, is the name column from sys.master_files, is the new directory, and is the filename portion of the physical_name column from sys.master_files. Don't forget to do the ALTER for both the data and the log.
4. Stop the server (with Ctrl-C if you started it from the command line, by stopping the service if you changed the startup parameters).
5. Copy/move the files to the new directory.
6. Re-start the server.
That should do it. There are still registry entries for the default data location and a few others that I haven't been able to find in the GUI anywhere, but I changed them as well.
Vince
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply