3. April 2008 06:26
Often you need to relocate your MOSS database files to a new partition, drive, or SAN, while leaving them attached to the same SQL Database Instance.
If you are trying to figure out how to move the content database to a different SQL instance or server then you are looking for this article.
Today my colleague Paul Curtis sent me the steps he uses to perform this task in a rather efficient way.
1. Prepare SQL Script
Alter the database file names using the scripts listed down below. Modify the scripts as appropriate for each database.
If the database has dashes in the name you have to put quotes around it.
2. Run the Script
Run the script in SQL Management Studio, or Management Studio Express. You should get a result indicating that the paths have been altered for the databases.
3. Stop SharePoint and SQL Server
Stop IIS, or the web applications your SharePoint site. Stop MSSQL Server through Management Studio or via the Windows Service.
4. Move the databases to the new location
Copy the mdf and log files to the new location (new drive, partition, etc).
Please copy and then delete, as opposed to trusting a cut+paste operation.
5. Start the SQL services
Start MSSQL either through Management Studio or via the Windows Service.
6. Verify access to each database
Verify the databases are working properly, you can just pull up the properties to the database in Management Studio.
7. Start SharePoint services
Start IIS, web apps, or whatever you disabled in step 3.
Go verify your SharePoint site is working.
8. You are Done.
Go get coffee, pat yourself on the back, etc.
The SQL Script:
ALTER DATABASE SP_WSS_SEARCH_DB
MODIFY FILE (NAME = SP_WSS_SEARCH_DB_log ,FILENAME = 'D:\ Data\SP_WSS_SEARCH_DB_log.LDF')
GO
ALTER DATABASE SharePoint_Configuration_DB
MODIFY FILE (NAME = SharePoint_Configuration_DB_log ,FILENAME = 'D: \Data\SharePoint_Configuration_DB_log.LDF')
GO
ALTER DATABASE "SharePoint_AdminContent_ce805cd0-0b6d-4ea1-8eaa-469b1ea0296a"
MODIFY FILE (NAME = 'SharePoint_AdminContent_ce805cd0-0b6d-4ea1-8eaa-469b1ea0296a_log' ,FILENAME = 'D: Data\SharePoint_AdminContent_ce805cd0-0b6d-4ea1-8eaa-469b1ea0296a_log.LDF')
GO
Keep in mind that moving the database may cause SharePoint to perform a full crawl in place of a regularly scheduled incremental crawl at the next crawl interval.
Go here to get more information from MSDN.