I’m sure of all of us have had to move our databases around from time to time. I just came into a situation where we are doing a POC test for DR mirroring software called Double Take. I had to uninstall and reinstall my SQL destination (or failover machine) because the first install was not done properly. For example the System databases needed to mimic the source server. So I started along my install and I specified the path for the databases.
After the install was done I checked and I forgot that the install will create an MSSQL directory on top of whatever path you specify in the install. UGH!!!
For example I wanted my system and user databases to go Z:\SQLServer\MSSQL5\DATA and that is what I told the install so the install put the databased here Z:\SQLServer\MSSQL5\DATA \MSSQL1\DATA. What an annoyance!!!!
Rather then redoing the install I thought I will just move the system databases. Well I haven’t done this task in SQL 2005 yet and I thought ok I will go and just modify the Startup parameters as I would in SQL 2000. Not the CASE.
Here is what I need to do.
For Non Master databases
ALTER DATABASE model MODIFY FILE ( NAME = modeldev, FILENAME = Z:\SQLServer\MSSQL5\DATA \model.mdf’)
ALTER DATABASE model MODIFY FILE ( NAME = modellog, FILENAME = Z:\SQLServer\MSSQL5\DATA \ modellog.ldf’)
Repeat for tempdb and msdb if needed
Stop SQL Server
Move the files
Start SQL Server and the databases will be in the new location and you can delete the old files.
Moving Master is a little trickier
Perform the same ALTER database statement executed above for master
ALTER DATABASE master MODIFY FILE ( NAME = master, FILENAME = x:\newlocation\master.mdf’)
ALTER DATABASE master MODIFY FILE ( NAME = mastlog, FILENAME = x:\newlocation\mastlog.ldf’)
Next you need to change the startup parameter in the REGISTRY. (You can search the registery for ERRORLOG or master.mdf to find the proper key for your server) Edit the values for the new location
Stop the SQL Server Service
Move the Files
Start the SQL Server Service in minimal config mode -f and trace flag -T3608 (prevents auto recovery)
From the command prompt run the following. You need to be in the location of the sqlservr.exe.
net start MSSQLServer /f/T3608
net start MSSQL$InstanceA /f/T3608
Connect VIA Query Analyzer and Alter mssqlsystemresource
ALTER DATABASE mssqlsystemresource MODIFY FILE ( NAME = data, FILENAME = x:\newlocation\mssqlsystemresource.mdf’)
ALTER DATABASE mssqlsystemresource MODIFY FILE ( NAME = log, FILENAME = x:\newlocation\mssqlsystemresource.ldf’)
Move the mssqlsystemresource files.
Set mssqlsystemresource database to read only. ALTER DATABASE mssqlsystemresource SET READ_ONLY
Stop and Start the SQL Server Service in normal mode.
