Moving System Databases is a delicate process, get rid of everyone around you, specially your manager. :)
The Basics
1. First verify where are the files, and to where you got to move them.
2. Make sure to create on the destination the proper folders for sql server data.
3. Ensure that the service account has ntfs permissions over those folders. Permissions are always a problem, if you forget it, sql server cannot put the databases online once it has no access to the files… remember that.
First the easy part
Moving the tempdb, Model, and Msdb
--use master
--Select name, filename from sysaltfiles where name not like 'mast%' -- sysaltfiles shows all the data files for all the databases… very handy
USE MASTER
ALTER DATABASE tempdb modify file (NAME='tempdev', FILENAME='newlocation\Data\tempdb.mdf')
ALTER DATABASE tempdb modify file (NAME='templog', FILENAME='newlocation\Data\templog.ldf')
ALTER DATABASE model modify file (NAME='modeldev', FILENAME='newlocation\data\model.mdf')
ALTER DATABASE model modify file (NAME='modellog', FILENAME='newlocation\data\modellog.ldf')
ALTER DATABASE MSDB modify file (NAME='MSDBData', FILENAME='newlocation\data\msdbdata.mdf')
ALTER DATABASE MSDB modify file(NAME='MSDBLog', FILENAME='newlocation\data\msdblog.ldf')
Stop the sql server
Move the actual datafiles to the new locations, they won´t move for themselves.
Say your prayers and start the Sql server
--- Uhhhh, SQL Server started, and all databases are online…good for you. now comes the funny part. Move the master
-- Move the master, by changing the master location on configuration manager, if in cluster do that on both nodes
look for service properties, advanced tab, startup parameters property
-dnewlocation\Data\master.mdf;-enewlocation\log\ERRORLOG;-lnewlocation\Data\mastlog.ldf
the –d parameter is the location of master.mdf file
the –l parameter is the location of master log file
the –e parameter is the location of the errorlog (very very important)
Newlocation is the PATH where the files will be located after the moving, please don´t write newlocation it won´t work… you got to write the full path where the file will be, and for god sake, make sure that the path exists and is spelled correctly
Remember that all parameters are on the same line and the parameter and the value are all toghether (no blanks). if you write it wrong sql server won´t come online. Hopefully you can change it after
If you are working in clustered environment, you got to change the startup parameter on all nodes where the instance belongs to.
Now is time to change the resource database of location, only applicable on SQL Server 2005. On SQL 2008 the system resrouce database is located on sqlroot\binn and you cannot change it.
What should i do them?? Nothing, leave it on the installation drive…
1. start-up the instance in master recovery mode, on command prompt fun the following
net start MSSQLSERVER /f /T3608
or
net start MSSQL$INSTANCENAME /f /T3608
And then connect using the sqlcmd.exe, and run the following to move the system resource db
ALTER DATABASE mssqlsystemresource
MODIFY FILE (NAME=data, FILENAME= 'V:\MSSQL2000\MSSQL$SQLC\Data\mssqlsystemresource.mdf');
GO
ALTER DATABASE mssqlsystemresource
MODIFY FILE (NAME=log, FILENAME= 'V:\MSSQL2000\MSSQL$SQLC\Data\mssqlsystemresource.ldf');
GO
-- Stop the instance, say your prayers and start it again.
Sometimes, depending on the operation you gonna do you still have to change the path where sql server agent put its logs
USE msdb
GO
EXEC msdb.dbo.sp_set_sqlagent_properties @errorlog_file=N'V:\MSSQL2000\MSSQL$SQLC\LOG\SQLAGENT.OUT' -- the new path obviously
GO
Now, you test if everything is running fine and you finished this delicate operation. Remember to failover the cluster group where sql server is running in order to make sure that everything is fine ON BOTH NODES. Collect some test evidence that you tested to make that everything was running ok, and you have to prove it.
IF YOU ARE NOT SURE OF THESE PROCEDURES. DON´T EXECUTE THEM IN PRODUCTION, create a test environment, simple sql server install on a VM or even in your own computer, and test the procedures.
Check the MSDN article with the procecedures above. http://msdn.microsoft.com/en-us/library/ms345408(SQL.90).aspx
This documentation is provided as is, and you know what it means. :)
Have fun!!!
