Friday, January 25, 2008

SQL Server 2005 Moving system databases

Moving the Master and Resource Database

Some minor things to note, are that the database must be in single user mode, you may find that sometimes the database keeps reconnecting and doesn't allow connections. In this case, use the TCP/IP configuration under the SQL Server Client library and limit the connect to a specific port and IP Address (such as localhost).

Moving the tempdb, msdb and model database

The following example moves the tempdb data and log files to a new location as part of a planned relocation. (This is from MSDN http://msdn2.microsoft.com/en-us/library/ms345408.aspx)

USE master;
GO
ALTER DATABASE tempdb
MODIFY FILE (NAME = tempdev, FILENAME = 'E:\SQLData\tempdb.mdf');
GO
ALTER DATABASE tempdb
MODIFY FILE (NAME = templog, FILENAME = 'F:\SQLLog\templog.ldf');
GO


USE master;
GO
ALTER DATABASE model
MODIFY FILE (NAME = modeldev, FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\Model.mdf');
GO
ALTER DATABASE model
MODIFY FILE (NAME = modellog, FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\Modellog.ldf');
GO

USE master;
GO
ALTER DATABASE msdb
MODIFY FILE (NAME = MSDBData, FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\msdbdata.mdf');
GO
ALTER DATABASE msdb
MODIFY FILE (NAME = MSDBLog, FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\msdblog.ldf');
GO


Stop and restart the instance of SQL Server.
Verify the file change.

SELECT name, physical_name AS CurrentLocation, state_desc
FROM sys.master_files
WHERE database_id = DB_ID(N'tempdb');

distmdl.mdf

I have read that this database is used for replication

Moving User Databases

The following article shows how you can move user databases.

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

If you don't like using scripts, you can always detach and then reattach the database :p


Some other useful posts on this subject

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

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=126761&SiteID=1

SQL Server Agent won't start

If you are having trouble with the SQL Server Agent not starting, most likely it is because the log path needs to be moved as well (if you deleted the directory where everything was moved from).

Change the following registry key to reflect where the SQL Server agent log file should go and then restart the SQL Server Agent service.

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.1\SQLServerAgent

ErrorLogFile = C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\SQLAGENT.OUT

Miscellaneous Items in the Registry

If you have removed the default install directory, view all of the keys for the instance install and update all of the proper paths so that the defaults work properly. Otherwise for example with the back directory, you will get an error everytime you try to do a backup or restore.

This page has a good article.

http://www.novicksoftware.com/coding-in-sql/Vol3/cis-v3-N2-sql-server-agent-errorlog.htm

No comments: